How Changing Formats Will Result In Different Sums of Time In Excel

In this tutorial we will review how different time formats will change the view of the results when you are adding a list of cells with time, and could give you what appears to be incorrect answers.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

In Excel, when you are adding up blocks of time, be it hours, minutes, or seconds, how you format the cell with the SUM function will determine what is being displayed. For example, here are two different “results” when adding up hours:

Even though columns D & E have the same data, the results in row 7 show differently. Row 8 indicates what the formatting style is being used for each column. Notice that when I just use “h:mm”, Excel will show the “remainder” after removing the blocks of 24 hour periods. More likely, it’s displaying the time after taking 8:30 and adding those various hours and minutes to it. However, if we use the format “[h]:mm”, Excel will now give us the sum of all the hours listed.

Here are two more examples using minutes:

… and seconds:

See how the impact of different formatting has on the results displayed? Basically, by putting square brackets around the segment of the time you want to add, Excel will total up those values within the terms that you bracket rather than the end time after adding the values to the original cell.

Take a few minutes and watch the video for more details.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

3 Responses to How Changing Formats Will Result In Different Sums of Time In Excel

  1. Peter B December 13, 2017 at 6:28 pm #

    Perhaps you should make it clear that the values are identical; only the display is formatted differently.

  2. Feel good December 20, 2017 at 7:53 am #

    Hei Michael,
    Thanks a lot for sharing this. You help me a lot with this ! Respect !

Leave a Reply