Hi,

How do I convert 1:10 in one cell to 70 seconds or just 70 in another
cell.

Or better yet; is there a way to enter 1:10 into a cell and have it
change to 70 automatically? Can this be done without a macro?

Thanks.

Roger wrote:
Hi,

How do I convert 1:10 in one cell to 70 seconds or just 70 in another
cell.

Or better yet; is there a way to enter 1:10 into a cell and have it
change to 70 automatically? Can this be done without a macro?

Thanks.

1:10 is 1 hour and 10 minutes
00:01:10 is 1 minute and 10 seconds

To convert the former to seconds ,multiply it by 86400

Although 1:10 is, in fact hours and minutes you can still display as 70
by custom formatting as [m]


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=524578

On Mon, 20 Mar 2006 22:43:03 -0000, "Paul Lautman"
wrote:

Roger wrote:
Hi,

How do I convert 1:10 in one cell to 70 seconds or just 70 in another
cell.

Or better yet; is there a way to enter 1:10 into a cell and have it
change to 70 automatically? Can this be done without a macro?

Thanks.

1:10 is 1 hour and 10 minutes
00:01:10 is 1 minute and 10 seconds

To convert the former to seconds ,multiply it by 86400



Thanks I made a mistake. What I want to do is enter one minute and
ten seconds into a cell without having to enter (00:1:10:0) and have
it come out as 70 seconds. I want to enter either 110 or 1:10.

Can that be done?

Thanks.

On Mon, 20 Mar 2006 17:13:04 -0600, daddylonglegs
wrote:


Although 1:10 is, in fact hours and minutes you can still display as 70
by custom formatting as [m]


I appreciate the help guys, but I still can't get it.

I've fiddled with all the custom layouts, but nothing is working.

I'm open to any and all suggestions.

Thanks

You can do either.

1. To enter 1:10 in one cell (say A1), and have it convert to 70 (seconds) in
another use:

=a1*60*24

2. To enter 110 in A1, and have it convert to 70, use:

=int(a1/100)*60+mod(a1,100)

In both cases, format the cell as a number

--
Regards,
Fred


"Roger" wrote in message
...
On Mon, 20 Mar 2006 22:43:03 -0000, "Paul Lautman"
wrote:

Roger wrote:
Hi,

How do I convert 1:10 in one cell to 70 seconds or just 70 in another
cell.

Or better yet; is there a way to enter 1:10 into a cell and have it
change to 70 automatically? Can this be done without a macro?

Thanks.

1:10 is 1 hour and 10 minutes
00:01:10 is 1 minute and 10 seconds

To convert the former to seconds ,multiply it by 86400



Thanks I made a mistake. What I want to do is enter one minute and
ten seconds into a cell without having to enter (00:1:10:0) and have
it come out as 70 seconds. I want to enter either 110 or 1:10.

Can that be done?

Thanks.

On Mon, 20 Mar 2006 19:01:23 -0600, "Fred Smith"
wrote:

You can do either.

1. To enter 1:10 in one cell (say A1), and have it convert to 70 (seconds) in
another use:

=a1*60*24

2. To enter 110 in A1, and have it convert to 70, use:

=int(a1/100)*60+mod(a1,100)

In both cases, format the cell as a number

--
Regards,
Fred


Thanks, Fred.

Say that 1:10 is in cell A1 then input this formula in cell B1 BUT
format cell b1 as general.

=A1*1440


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=524578

If I have a lot of times to enter in m:ss format, I find it more
convenient to enter them as m.ss and then have a formula to convert
this format. If you entered 1.1 in cell A1 (meaning 1:10 - you don't
need to enter the zero), then this formula in B1 would give you 70
seconds:

=INT(A1)*60 + MOD(A1,1)*100

The formula can be copied down column B and then the times can be
entered in decimal in column A. You can pre-format column A to 2 dp so
the times look similar.

Hope this helps.

Pete

Roger Wrote:
On Mon, 20 Mar 2006 17:13:04 -0600, daddylonglegs
wrote:


Although 1:10 is, in fact hours and minutes you can still display as
70
by custom formatting as [m]


I appreciate the help guys, but I still can't get it.

I've fiddled with all the custom layouts, but nothing is working.

I'm open to any and all suggestions.

Thanks

use Format Cells Custom and type [m] in the box. Now if you enter
1:10 the cell will display 70 - no formula required! Although this is
technically minutes, not seconds, it works fine for display and if you
need to use the value for calculations any adjustment can take place in
the formula for that.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=524578