Excel Time - Summing Time Greater than 24 Hours

Add to Favorites

I'll show you the easy and simple way to sum time past 24 hours in Excel.  All we really have to do is to change the format of the desired cell.

The quick answer is to use the number format [h]:mm:ss

Below, I will give a more in depth explanation of how this works.

Here, we have a list of start and finish work times stored in Excel.  There is also a column which calculates how many hours were worked:

What we need to do now is to find the total number of hours that were worked.  To do this, we just need a simple sum formula:

But, since the total number of hours is greater than 24, by 7.5 hours, we get the above result.

What we want is this:

All that we did here was to add the number format [h]:mm:ss to the new cell.  Nothing else was changed!

This is all you do:

  1. Sum the number of hours worked.
  2. Select the cell where you just summed the hours.
  3. Right-click it and select Format Cells
  4. Go to the Number tab of the window that opens.
  5. Select Custom from the Category section.
  6. Scroll down and select the [h]:mm:ss option or, if that doesnt exist, simply type it in under where it says Type.
  7. Hit OK and you are done.

Download the accompanying Excel workbook if you want to get this spreadsheet.


If you enjoyed the tutorial make sure to follow us on facebook and google plus!

Have a great day!

Question? Ask it in our Excel Forum

Excel Function: SUM()
Downloadable Files: Excel File