Time in hours and minutes between two dates

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.

This entry was posted in Excel and tagged , , , , . Bookmark the permalink.

4 Responses to Time in hours and minutes between two dates

  1. Doug Glancy says:

    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.

    Like

  2. Jeff Weir says:

    I always wondered what [h] meant. Thanks, Doug.

    Like

    • dougaj4 says:

      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.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.