Dates and times in Excel are stored as a date number, which is the number of days since 1 January 1900, with the value after the decimal point representing the time of day.
To calculate the number of hours between two dates we can simply subtract the two values and multiply by 24. This returns a decimal value though. If we want the time in hours and minutes (or hours minutes and seconds), things get a bit more complicated:
The screen-shot shows various alternatives for displaying the number of hours between the date/time in cell B3 and the start of the date in B2.
The first row shows the result of multiplying the date difference by 24. This shows the correct result if formatted as a decimal, but if is formatted as time it shows the wrong value.
Microsoft recommends use of the Text function, with the format string “h:mm”. Note that the format string overrides the cell format, so the returned text displays the same regardless of the cell number format. This will work correctly for time differences up to 24 hours, but the display resets to zero after each 24hours, so the 36 hour time difference above displays as 12 hours. This can be changed to display days, hours and minutes with the format string “d:h:mm”.
Another approach is to leave the time difference as a date number and format to display as hours. The default time format has the same problem as the “h:mm” format string, so 36 hours displays as 12, but it is possible to display the correct number of hours:
- On the Home tab, select the Number Format dialogue.
- Select Custom, then h:mm.
- Edit in the Type box by putting square brackets around the h: “[h]:mm”.
- Time differences greater than 24 hours should now display correctly in hours and minutes (see cell F8 below).
- It is possible to use the same approach with the Text function (Row 9 below). Note that the time difference now displays in hours and minutes, regardless of the cell format.
Update 1 Oct 18: As noted by Doug Glancy in the comments, the [h]:mm:ss format is available from the format dialog. In my version (Office 365) it is at the end of the time formats, near the bottom of the custom format list.
Newton Excel Bach, not (just) an Excel Blog 
I had never noticed until reading one of Debra’s posts a while back that the [h]:mm:ss version of this is actually a built-in choice in the Time section of the Format Cells dialog.
LikeLike
Yes, I noticed that as well after posting :). I’ll add a note.
LikeLike
I always wondered what [h] meant. Thanks, Doug.
LikeLike
I’d never noticed it until recently, hence the post. It does seem it’s not very well known, which is strange since the problem of displaying hours time difference > 24 is well known.
LikeLike
This was extremely helpful, but I do have a question… How do you convert [h] into a number that can be used in another formula?
LikeLike
Sorry for the slow reply.
If you use the custom format to display hours, the actual number stored by Excel is in days, so to convert to hours just multiply by 24 and make sure it is formatted as a number.
LikeLike
THANK YOU. Finally, the solution clearly laid out.
LikeLike