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