1

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.

Improve this question

3 Answers 3

Reset to default
4

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.

Improve this answer
2
  • As mentioned just the cell format as it also supports bigger numbers. It's unclear why you're using TIME() in the first place.Your example has all the base information you'd need to just write 24:59:00 into a cell.
    – Seth
    Commented Oct 24, 2018 at 11:04
  • Well it comes from a piece of code that converts an amount of seconds in hours, minutes and second. By typing out what I did I figured it would be easier to just print the values directly without using Excel formulae. Thanks for the help.
    – Rodin10
    Commented Oct 24, 2018 at 11:37
3

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.

Improve this answer
1
  • I have just seen Aganju's answer, and using [hh]:mm:ss as the cell format is a much neater solution than building the output string manually.
    – AFH
    Commented Oct 29, 2018 at 12:31
1

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.

Improve this answer
1
  • I've not come across that before: well worth knowing, and a much easier answer than mine to the formatting side of the question. Thanks. I would like to up-vote, but you haven't considered the input side, so your answer is only partial.
    – AFH
    Commented Oct 29, 2018 at 12:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .