Como o Excel lida com o formato h: mm: ss?

Question

Eu tenho o número 0.00082788917980126 que, uma vez convertido para h:mm:ss , me oferece 0:01:12 , e preciso entender como preciso executar uma formatação semelhante fora do Excel, daí minha pergunta:

Como o Excel converte um número no formato h: mm: ss?

    

Answer 1

Fonte: link

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.

Exemplo de cálculo no 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

• Você pode expandir o cálculo para milissegundos e mais