Converting Seconds to Hours:Miutes:Seconds and reverse

Posted by Donna 520 on July 08, 2001 9:24 AM

What if you need to convert seconds to HOURS:min:sec
how would you modifiy the equation below? And how do
you convert H:M:S using the TRUNC below back to seconds?

I was able to show A1/(24*60)/60 and have is displayed as the correct format HH:MM:SS but I can't figue out
how to do the reverse.

A2 = A1/(24*60) and format as time

If B1 contains the number 1.3 for one minute and 30 seconds then:

B2 = TRUNC(B1)*60 + (B1-TRUNC(B1))*100

Thanks,
Donna

Posted by Damon Ostrander on July 08, 2001 7:09 PM

Hi Donna,

You can actually get Excel to do most of the work for you. Since dates and times are stored internally as floating point days, all you need to do is convert your number in seconds to days by dividing by 86400 and formatting the cell to display the result as hh:mm:ss.

Damon