Time value is stored as a serial number in Excel and sometimes it is tricky to get the accurate result while adding time values in Excel. We need to make sure to use the right approach while making calculations to get an accurate sum of time values. In this article, we will learn how to sum time in Excel without an error or wrong calculation.
Figure 1. The Output of Custom Format Type
Using SUM Formula
Suppose we want to estimate the total time in hours and minutes while adding up the time values. We have time values for each day of the weeks in terms of hours and minutes worked during two weeks and we can easily calculate the sum of time values by entering the SUM formula in an active cell in Excel, such as;
=SUM(B2:C2)
Figure 2. Using the SUM Formula
Using The AutoSum Function
Instead of inserting the SUM formula, we can also use the AutoSum function to add up the time values in Excel. In the above example and using the AutoSum function we can learn how to sum time in Excel in the following steps:
- Select the active cell or cells where we need to sum the time, say D2:D6 in this example,
- Then on the Home tab, in the Editing section choose AutoSum.
- Press Enter to get the result
Figure 3. Using The AutoSum Function
Figure 4. The Output of the AutoSum Function
To Add Up More Than 24 Hours
While making the calculation of time values it happens that the hours add up to more than 24 hours and if we do not select the right format of time the SUM formula or AutoSum function displays the remainder of time instead of the actual sum of time values.
Let’s have a look at the total of time values of all the four weeks in column F. The sum of time values is not what it should be. Because of the sum of time values exceeds 24 hours, so the SUM formula returns the remainder of time and not the actual total of time.
Figure 5. The Output of the SUM of Time Values Exceeding 24 hours
In order to get the accurate result, we need to choose the right format type of time that will show the accurate values while performing how to sum time in Excel in the following steps;
- Select the cell or cells to show the sum as more than 24 hours
- Mouse right click and go to Format Cells
Figure 6. Applying the Time Format
- Choose Custom in the Category list.
- In the Type box, at the top of the list of formats, type [h]:mm;@ and press OK.
Figure 7. Applying the Custom Format Type
This format type will show us the accurate result of Total of the time values in Excel and will be available in the Type list for future use.
Figure 8. The Output of Custom Format Type
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment