In Excel, if you want to sum time values, you need to use the sum function to sum the values and then you need to change the format to show the total hours and minutes. Below is a list of time values that we have sum in cell C2.
In this tutorial, we learn to write this formula and apply correct formatting to it.
Write Formula to Sum Time in Excel
- First, in cell C2, enter the sum function.
- Now, enter the starting parentheses.
- After that, refer to the range of cells where you have time values.
- In the end, enter the closing parentheses and hit enter to get the result.
This will give you a sum of values, but this format is not correct and we need to change this format using custom formatting.
- While selecting the cell, open the format option with the keyboard shortcut (Ctrl + 1).
- Now, click on the Custom option.
- After that, click on the format input bar.
- Next, enter the [h]:mm in the input bar.
- In the end, click OK to apply the formatting.
The moment you click OK, it will show you the total number of hours and minutes.
You Need to Understand This
Excel stores the date and time as a number in the backend and you have the format on the cell. So if you convert your time values into values they will look something like the below.
Time values are always in decimals.
Now when you sum these values, it returns 1.4375 in the result.
As I said, time values are always in decimals, when you apply time format (normal time format HH:MM AM/PM), Excel shows you the time according to the remaining decimal value which is 0.4375, and consider the integer which is 1 as a complete day.
To overcome this problem, you need to use a custom format that we have used in our method. When you add square brackets with the format, it shows the total hours in the result.
[h]:mm