Summing times is just =SUM(B2:B10) — the catch is the format. Excel stores time as a fraction of a day, so a total of 34 hours 30 minutes comes out as 1.4375, and the standard time format shows only the 0.4375 part, displaying 10:30 instead of 34:30. The whole number is being read as a full day and dropped from view.
To fix it, select the total cell, press Ctrl+1 → Custom, and enter [h]:mm. The square brackets tell Excel to let hours run past 24 instead of wrapping.
Use [h]:mm:ss if you need seconds, or September:ss to see the whole total as minutes. Two related things.
To turn the total into a number for billing or payroll, multiply by 24: =SUM(B2:B10)*24 gives 34.5 hours as a decimal — format that cell as Number, not Time. And to add a fixed amount to a total, add it as a time value: =A1+TIME(0,30,0) adds thirty minutes.
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
Good morning sir I want to added 30 minutes in total 2,530 hours. Please tell me how to calculate in excel with formula
thanks it helped