QUESTION :
I’m trying to display times over 24 hours in Excel by using =TIME() but when the values get over 24 hours they display incorrectly. For example I have =TIME(27;42,81;49) which should be displaying 24:59. Instead it displays as 03:42. I have already changed the format to [hh:mm]. This works fine when doing an =SUM() for the 3 separate values.
ANSWER :
Official article on TIME() suggests it’s only meant to display 24 hours. As such the display of 03:42 would be correct. If you want to work with arbitrary numbers of hours, minutes and seconds you will likely not be able to use that function but instead for instance the cell format.
The TIME() function is working as specified. From the on-line manual, the first parameter is defined:-
Hour Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the
remainder will be treated as the hour value. For example, TIME(27,0,0)
= TIME(3,0,0) = .125 or 3:00 AM.
If you want to show hours above 23, you will need to build and format your own values, eg if the hours, minutes and seconds are in A1:C1, set D1 to:
=A1/24+B1/1440+C1/86400
You now simply format the cell as [hh]:mm:ss, as proposed in Aganju’s answer.
[I leave below my previous proposal for output formatting, as the techniques it uses may be useful in instances not handled by Aganju’s format.]
Alternatively, the following will generate a cell formatted as you want:
=TEXT(ROUNDDOWN(D1*24),"##")&":"&TEXT(ROUNDDOWN(MOD(D1*1440,60)),"##")&":"&TEXT(ROUNDDOWN(MOD(D1*86400,60)),"##")
Strictly, the seconds field should use =ROUND() instead of =ROUNDDOWN(), but this will cause problems with seconds values of 59.5 and above.
If nearest rounding is important, then D1 must itself be rounded to the nearest second (1/86400) first, eg by setting E1 to:
=ROUND(D1*86400)/86400
The formatted cell will then be:
=TEXT(ROUNDDOWN(E1*24),"##")&":"&TEXT(ROUNDDOWN(MOD(E1*1440,60)),"00")&":"&TEXT(ROUNDDOWN(MOD(E1*86400,60)),"00")
In either case, these formulae could be combined so as to eliminate the work cells, but the resultant expressions will be very complex and difficult to understand and support.
Normal days have only 24 hours, so Excel starts counting from 0 again – like you would on a clock. This is the format code “hh:mm”, which is the default for Time
If you want to see hours accumulating over 24, you need to use the respective format code: "[hh]:mm" – the [ and ] around the hours make them count unlimited.
Note that this works for minutes too – you could use "[mm]:ss" as a format code that will count minutes forever.