Multiplying times

  • Thread starter Thread starter dpwicz
  • Start date Start date
D

dpwicz

Hello,

I need to multiply a time written in the format (m:ss) times a number for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
 
Excel maintains times as fractions of 24 hours. 1 hour is 1/24, 1 minute is
1/(24*60) and 1 second is 1/(24*60*60). So if you enter 00:00:10 which is 10
seconds, Excel will store that as 1/(24*60*60) * 10 - i.e.
0.000115740740740741. If you display that with a format of hh:mm:ss you will
see 0:00:10 in the cell and 12:00:10 AM in the formula bar. 12 midnight is
0.0 Time is just a number so, simply multiply and display the time as
hh:mm:ss or [h]:mm:ss if the time is greater than 24 hours.
Formatting is for human consumption. So, to multiply 10 seconds by 20,
simply enter the time 0:00:10 and multiply by 20 to get 200 seconds which is
0.00231481481481482 and format that as hh:mm:ss to see 0:03:20 - 3 minutes
and 20 seconds.

Tyro
 
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin
 
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

MartinW said:
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


dpwicz said:
Hello,

I need to multiply a time written in the format (m:ss) times a number for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
 
Did you enter your time as 0:04:02? That is 0 hours, 4 minutes and 2
seconds. That time when multiplied by 20 returns 1:26:40 - ie. 4 minutes and
2 seconds multiplied by 20 is 1 hour, 26 minutes and 40 seconds. If you
enter 4:02 that is 4 hours and 2 minutes and if you multiply that by 2, you
get 8:04 - ie 8 hours and 4 minutes. If you multiply by 20 you get 80:40:00
when formatted as [h]:mm:ss - that is 80 hours and 40 minutes.

Tyro
 
Does the "other" program provide Excel times??? Formatting is for human
consumption. You have to provide numbers to Excel that represent Excel's
times - ie. fractions of 24 hours.

Tyro

dpwicz said:
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

MartinW said:
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


dpwicz said:
Hello,

I need to multiply a time written in the format (m:ss) times a number
for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
Click to expand...
 
Ok so Excel will interpret them as 4 hours and 2 minutes.
If you format B1 as [hh]:mm it will show what appears
to be the correct answer i.e. 80:40 which hopefully shouldn't
bother you, so long as you remember that what you are viewing
as minutes and seconds is in reality hours and minutes. With
everything based on 60 it should all work the same.

Or is there some other reason that you can't work with this little deception.

HTH
Martin

dpwicz said:
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

MartinW said:
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


dpwicz said:
Hello,

I need to multiply a time written in the format (m:ss) times a number for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
Click to expand...
 
unfortunately no

Tyro said:
Does the "other" program provide Excel times??? Formatting is for human
consumption. You have to provide numbers to Excel that represent Excel's
times - ie. fractions of 24 hours.

Tyro

dpwicz said:
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

MartinW said:
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


Hello,

I need to multiply a time written in the format (m:ss) times a number
for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
Click to expand...
 
No, that would work be I figured out all I had to do is devide the 4:02 by 60
and it gave me 0:04:02.

Thank you everyone for the help

MartinW said:
Ok so Excel will interpret them as 4 hours and 2 minutes.
If you format B1 as [hh]:mm it will show what appears
to be the correct answer i.e. 80:40 which hopefully shouldn't
bother you, so long as you remember that what you are viewing
as minutes and seconds is in reality hours and minutes. With
everything based on 60 it should all work the same.

Or is there some other reason that you can't work with this little deception.

HTH
Martin

dpwicz said:
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

MartinW said:
Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


Hello,

I need to multiply a time written in the format (m:ss) times a number for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
Click to expand...
 
Glad you got it sorted.

You may already know this but I'll include it just in case.
The easiest way to divide all of your data by 60 is
to put 60 in a blank cell, then copy it,
highlight all of your data to be changed,
right click on it and select Paste Special
Check divide and then OK out.

HTH
Martin

dpwicz said:
No, that would work be I figured out all I had to do is devide the 4:02 by 60
and it gave me 0:04:02.

Thank you everyone for the help

MartinW said:
Ok so Excel will interpret them as 4 hours and 2 minutes.
If you format B1 as [hh]:mm it will show what appears
to be the correct answer i.e. 80:40 which hopefully shouldn't
bother you, so long as you remember that what you are viewing
as minutes and seconds is in reality hours and minutes. With
everything based on 60 it should all work the same.

Or is there some other reason that you can't work with this little deception.

HTH
Martin

dpwicz said:
The times are being taken from anothere program and are already entered in
the 4:02 format, so I really don't want to reenter them.

:

Hi,

With your time in A1, put =A1*20 in B1
Couple of things to watch.
Format both cells to [mm]:ss (not mm:ss)
Input your time as 00:04:02
If you input it as 4:02 it will be 4 hours and 2 minutes

HTH
Martin


Hello,

I need to multiply a time written in the format (m:ss) times a number for
instance 4:02 by 20. What would be the best way of doing this?

Thank You,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top