How to Summarize Hours in Excel and Show the Right Result

EasyExcel_17_1_Summarize Hours in Excel

Sometimes Excel doesn’t seem to add up the hours correctly. In this table I have made a table of an employee’s working hours and used the SUM function to add them together. Look what happens:

The sum is 13:45 instead of the expected 37:45. That’s 24 hours less than we wanted!

Here’s why: The result is actually 1 day, 13 hours and 45 minutes, but when you use the SUM function, Excel assumes that you want to keep the same format as above, which only shows hours and minutes.

How to fix it:

EasyExcel_17_2_Summarize Hours in Excel

  1. Open the Format Cells window (Ctrl+1) and choose Custom.
  2. If you want to show the result in days, hours and minutes, the format should be dd:hh:mm. Result: 01:13:45
  3. If you want to show the result in hours and minutes, the format should be [h]:mm. Result: 37:45

As you see above, the square bracket tells Excel to summarize that particular time unit, so if you want to show the time in minutes, enter the format [m]. It works for seconds too: [s]

Other Useful Tricks:

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

Leave a Reply

Your email address will not be published. Required fields are marked *

− 2 = four

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>