Convert Hours to Seconds

[bhenlee]

Hi Experts,

How do i convert time in hours (a1) to seconds (b1) if the A1 is formatted as 00\:00\:00?

A1 is 01:30:05 = seconds (?)


Cell is custom formatted to 00\:00\:00

Thank you in advance

[6StringJazzer]

Use this formula in B1:

=A1*24*60*60

or just

=A1*86400

The formatting of A1 doesn't matter as long as it is a time value. B1 should be formatted as General (not a time format).

[bhenlee]

thank you Jeff. Unfortunately its not working..

A1 is formatted to 00\:00\:00 so i could just enter 13005 and it will show as 01:30:05 in A1.

If I use the formula you provided the answer is not 5405.

[Roel Jongman]

that is because you are not entering times but do a formating trick on a regular number..

with numbers like that try this formula

Please Login or Register  to view this content.

[bhenlee]

Thank you Roel. It does the trick; however, it does not work if i only input minutes or just seconds..

need to the formula to calculate if only minutes were entered or even seconds.

[6StringJazzer]

What is the need you are meeting by entering a string of digits into A1 instead of an actual time?

[bhenlee]

for convenience Jeff. the cell is formatted to 00\:00\:00 so i could just enter 13005 and it will show 01:30:05 (1 hour, 30 minutes and 5 seconds)

i have this formula =INT(A1/100)+(A1-100*INT(A1/100))/60)/60/24)*86400) which does the trick if the format is limited to just 00\:00 but need to extend it to 00\:00\:00 to accommodate seconds if any.

[Roel Jongman]

ok moving forward on your formula above.
try this

Please Login or Register  to view this content.

you might need to change the semi colons in the formmula with comma depending on regional setting for list separator

[Root_]

Just use

=TEXT(A1,"00\:00\:00")*86400

[sandy666]

ignore this post. I just notice you've 2007 which doesn't support PowerQuery

one way, use PowerQuery

Please Login or Register  to view this content.

[bhenlee]

Thank you Roel. Works like a charm!!!