How does Excel handle the h:mm:ss format?

Question

I have the number 0.00082788917980126 which once converted to h:mm:ss format gives me 0:01:12, and I need to understand how as I need to perform similar formatting outside Excel, hence my question:

How does Excel convert a number into the h:mm:ss format?

Answer 1

Source: http://www.cpearson.com/excel/datetime.htm

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. This is called a serial date, or serial date-time.

Times
The fractional portion of the number, ttttt, represents the fractional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly, 6PM is stored at 0.75, or 75% percent of a 24 hour day.

Dates
The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1.

Example calculation in Excel

hours of a day              = 24
minutes of a day            = 60
seconds of a day            = 60        
total seconds of a day      = 24 * 60 * 60 = 86400

your time as serial date    = 0.00082788917980126       
your seconds since midnight = 86400 * 0.00082788917980126 = 71.5296251348289

hours      =ROUNDDOWN( 71.5296251348289 / 3600, 0 )    = 0      
minutes    =ROUNDDOWN( 71.5296251348289 / 60, 0 )      = 1      
seconds    =ROUND( MOD( 71.5296251348289, 60 ), 0 )    = 12     
your formatted time                                    = 00:01:12

• You can expand the calculation to milliseconds and more