adding times

[rpt]

please help, i need to add times greater than 9999:59 on my laptop
running xp on a spreadsheet

 

[J.E. McGimpsey]

Times in XL are stored as Fractional days (i.e., 03:00 = 0.125). So
you can continue to add times >9999:59, but you can't display them
in time format. You can get a decimal format by multiplying by 24,
e.g.:

A1: 9999:30
A2: 1:45
A3: =(A1 + A2) * 24 ===> 10001.25

Or you could display the colon format as text (though you won't be
able to use it as a number):

A3: =INT((A1+A2)*24) & right(text(mod(A1+A2,1),"h:mm"),3)

 

[Arvi Laanemets]

Hi

You can display hours above 24, when you format the cell with custom format
"[h]", or "[h]:mm", etc.

To convert time to text in time format (it's about J.E. McGimpsey's second
formula) also exists a simpler solution
=TEXT(A1+2;"[h]:mm")

 

[J.E. McGimpsey]

Thanks for the correction - I brain-dumped that the *display* can go
above 9999:59, even though the parser doesn't recognize *entries*
above 9999:59.