Where data is an Excel Table in the range B5:E16. In this example, the goal is to sum total hours in cell H5 and calculate total hours per person in the range H8:H10. All data is in an Excel Table named data in the range B5:E16. The table is used for convenience only, and is not required to solve the problem. The main challenge in this example is to correctly display time as a duration instead of time of day.
How Excel handles times
In Excel, dates are serial numbers and times are fractional parts of 1 day. This means the date and time values are just regular numbers and can be summed, added, and subtracted like other numbers. The screen below shows what the dates in column D and the times in column E look like with the General number format applied:
As you can see, dates are large serial numbers. The times in column E are just fractional values of one day, expressed as decimal values. This means you can use standard functions like SUM and SUMIF, etc. to sum time in various ways. But you have to be careful about how the result is displayed.
Excel times over 24 hours
What causes a time to look like a time in Excel is a number format. A simple number format for time might look like this: The main thing to understand is that a standard time format is meant to display time like a clock, which resets every 24 hours. This works fine when the goal is to display a time of day, or when total hours are less than 24. But in cases where time is meant to show a duration (i.e. elapsed time), the problem is that Excel will not display more than 24 hours by default. For example, if total time is 23 hours, the time format above will display “23:00”, but if total time is 31.5 hours, the time format above will display “7:30”:
The time format causes hours to reset at midnight, and the extra 7.5 hours roll over into the next day. The formula is actually working fine, but the display makes it seem like hours are being undercounted or “lost” in the calculation.
Custom time format
To display 25 hours like “25:00”, we need to use a custom time format like this: The square brackets around the “h” tell Excel to display hours as a duration, not a time of day. You can see how this works in the screen below. Cell D3 uses the time format “h:mm” and cell D4 uses the time format “[h]:mm”. Both cells contain the same formula:
Apply custom time format
To apply a custom time format, first select the cells you want to format and use Control + 1 to open the Format Cells window. Next, navigate to the Number tab, select Custom in the list to the left, and enter “[h]:mm” in the Type input area:
You will see a sample of the result displayed in the “Sample” area above Type. Video: How to create a custom time format
Total time
With the above in mind, the formula to calculate total time in cell H5 is: With the following custom time format above applied: The number returned by the SUM function is 3.1875 (3.19 days), which displays as 76:30 with the above time format applied.
Time per person
To calculate time logged per person, we use the SUMIF function. The formula in cell H8, copied down, is: The range is the “Name” column of the table, the criteria is the value from G8 (“Jane”), and the sum_range is the “Hours” column. As the formula is copied down, SUMIF returns total hours per person. The range H8:H10 has the custom time format “[h]:mm” applied. For more information on number formats, see Excel Custom Number formats.
Decimal time
Another solution for working with time values over 24 hours is to convert the time to a decimal number. For example, instead of using native time values like 4:30, 7:00, and 8:30, you convert these times to decimal hours like 4.5, 7.0, and 8.5. Once you have time in this format, you can calculate total time any way you like. This formula example explains the details.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
