converting Minutes to Seconds

R

Roger

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.
 
P

Paul Lautman

Roger said:
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.
Click to expand...

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
 
D

daddylonglegs

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

Roger

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
Click to expand...


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.
 
R

Roger

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


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
 
F

Fred Smith

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
 
R

Roger

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
Click to expand...


Thanks, Fred.
 
V

vane0326

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

=A1*144
 
P

Pete_UK

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
 
D

daddylonglegs

Roger said:
Although 1:10 is, in fact hours and minutes you can still display a 70
by custom formatting as [m]
Click to expand...


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
Click to expand...

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