Excel – How to Calculate Hours Over 24

date timemicrosoft excelworksheet-function

I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in Column D. I'm trying to total the durations, but keep getting incorrect values in cases where the total duration is > 24 hours.

Consider 

     A                   B        C                D         
1  2012/05/22 01:00  |  Wed  |  Session 23341  |  9:00
2  2012/05/23 10:00  |  Wed  |  Session 23342  | 22:00
3  2012/05/25 08:00  |  Fri  |                 |  0:00

As you can see, the date differential between A2 and A3 demonstrates that the value of 22:00 in D2 represents a duration of 46 hours (05/23 10:00 + 46:00 = 05/25 08:00). However, Column D is only displaying the 22:00 in excess of a full 24 hour day.

My attempts to read D2 have included int(d2) = 0.00 / int(d2)*24 = 0.00 / hour(d2) = 22.00 / int(a3-a2)*24 =0.00 etc. and have been unsuccessful in pulling a value of "46".

How can I accurately represent D2 to achieve accurate summary data?

Best Answer

Excel is able to display times in a time stamp format, but you need to define a custom cell format for that like this [h]:mm:ss – note the brackets, which are denoting that the hours do not roll over at 24, but keep counting up. If you define this and assign it to column D, you should be able to input a duration of 46:00:00 in D2 (you can skip the seconds in your format if you don’t need them of course).

There is a gotcha: internally, Excel still stores a time counting upwards from its 0 base date time value (January 1st, 1900, 00:00 – unless you set it to use 1904 date time clues to allow negative values, in which case that would be 4 years later), so the whole time family of functions (HOUR, MINUTE, SECOND) will still return a value inside the 24 hour time frame (the modulo of dividing by 24, times 24, or 22 in your case, to be precise).

  • if you want to display the hours, minutes, or seconds of such a time stamp, you will have to apply another time format to its value (i.e. [h] for the hours, m for the minutes, s for the seconds). Note this affects display only – internally, the value is still a regular date time value as explained above;
  • you can calculate freely, as long as all operands are of date time type. If your value in A1 is assigned a “date time” format of yyyy/dd/mm hh:mm, inputting =A1+D1 into cell A2 will give you the correct new date and time.
Related Question