Is there a way to represent a time value in Excel where hours can be 24 and above?
For example, have a time that would be 25:00:00 for 1 AM on the following day (common representation in mass transit scheduling).
It can't just be plain text, because I want to be able to perform calculations on them (averages, standard deviation for example) or chart them.
On the Home tab, in the Number group, click the Dialog Box Launcher next to Number. You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
It's already the case that if you put a date or a time into a cell, you get a datetime value.
For example, type 14:00
into A1
and A2
, and set A3
to =A1+A2
. Why does it display 04:00
, you might wonder. The answer comes if you set the cell format to something that displays date and time, eg dd/mm/yyyy hh:mm
. Do this to A1
and A2
as well and we see:
So to do what you want - to store 25 hours in a cell and have it understood by both users and Excel to mean 25 hours - just put a value of 25/24
, and a custom format including the magic format code [h]
, the []
here meaning 'allow the displayed value to go over 24'.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With