How do I calculate number of hours > 24 in Excel?

Question

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?

Answer 1

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.

Answer 2

Note that you can multiply by 24 to get the hours as a decimal, so this formula should return 46

=(A3-A2)*24

format result cell as number

Answer 3

You can get Excel to handle very large numbers of hours and minutes, simultaneously obviating the need to use the colon : symbol each time, to seperate hours and minutes.

1. Enter hours in a column and minutes in a separate column. Enter say hours in cell A1 and minutes in Column B1

2. At the end of each of the above columns, add the numbers for example say A1 to A19 and B1 to B19 as plain numbers in Columns A20 and B20

   Put in a formula in cell A20 that reads =SUM(A1:A19) and in B20 that reads =SUM(B1:B19)

3. Put in a formula in Cell A21 that reads =A20+(B20-B21)/60 and in Cell B21 that reads =MOD(B20,60)

Voila! . The values Cells A21 and B21 give you totals in hours and minutes. strong text

PS: You can cut and paste the formulas from this page!

Answer 4

Maybe separate the date and times into different columns, so you can then use the new B column to perform a calculation in the new E column.