Microsoft Excel is one of the most popular applications used in big corporations and business centers. It contains lots of built-in functions that help us to perform different tasks needed for our day-to-day work. One of them is to find the sum of time values in Excel. In this article, we will discuss the solutions when SUM is not working with time values properly in Excel.

**Table of Contents**Expand

**SUM Not Working with Time Values in Excel: 5 Solutions**

We have taken the data set of one employee for two weeks. Now, we will sum the time value and show solutions to the problem.

We can sum time in two ways in Excel. One problem occurs when we sum time. That is, we get the remainder of the time instead of getting the actual sum of time. If the sum of time is less than 24 hours no problems at all. But when the sum exceeds 24 hours, we get the reminder value only. So, the solution of the mentioned problem will be shown with the sum of time in the following methods.

**1. Get the SUM of Time Value Inserting Individual Cells**

Here, we will sum the time values by inserting the cell reference individually. For this, we add a column named **Result**.

**Step 1:**

- Now, go to
**Cell E5**. - Find the sum of the
**Cells C5**and**D5**using the following formula –

`=C5+D5`

**Step 2:**

- Press the
**Enter.**

We get the sum of the working time on **Monday** of **Week 1** and **Week 2**.

**Step 3:**

- Now, pull the
**Fill Handle**icon.

Here, we get the sum of time. We can add more than two values in the same way.

**2. Apply the SUM Function to Find the Time Value**

Here, we will use **the SUM function** to sum time values.

**Step 1:**

- Go to
**Cell E5**. - Write the following formula consisting of the
**SUM**function –

`=SUM(C5:D5)`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Pull down the
**Fill Handle**icon to the last cell.

Here, we get the sum of two weeks based on each day.

**Read More: **How to Sum Time in Excel

**3. Sum Time Applying the AutoSum Tool**

We will apply the **AutoSum **tool here.

**Step 1:**

- First, need to select the cell where we need to apply the
**AutoSum**tool. - Here we select
**Cell E5**. - Now, go to the
**Home**tab. - From
**Editing**choose the**AutoSum**tool.

**Step 2:**

- When we click
**AutoSum,**a formula will appear on that cell. - We can edit the formula argument if needed.

**Step 3:**

- Then press
**Enter**.

**Step 4:**

- In the same way, apply
**AutoSum**in the rest of the cells.

Here, auto sum makes it simple to sum the time values.

**4. Change the Cell format to Solve Not Working Issue with SUM the Time Value**

In this section, we will show how we get the exact sum time when the sum is more than 24 hours.

To show the sum of each week we add a row below the existing data set named **Result**.

**Step 1:**

- Go to
**Cell C10.** - Apply the
**SUM**function as shown before.

`=SUM(C5:C9)`

**Step 2:**

- Then press
**Enter**.

Here, we did not the exact sum value. As the sum exceeds 24 hours it only shows the reminder value. Now, we will show how to solve this.

**Step 3:**

- Select
**Cell****C10**cell and press**Ctrl+1**. - We get a window named
**Format Cells**. - Select the
**Custom**category in the**Number**section. - Now, select
**[h]:mm:ss**type.

**Step 4:**

- Finally, press
**OK**.

Here, we get the exact sum of time.

**Step 5:**

- In the same way, get the sum at
**Cell D10**.

This is how to get the solution of the sum of time.

**5. Perform Multiplication Operation When SUM of Time Not Working**

Here, we will multiply the sum of time by 24 to get the exact sum.

**Step 1:**

- First, apply the following
**SUM**formula at**cell C10**–

`=SUM(C5:C9)`

**Step 2:**

- Then press
**Enter**.

This result is not according to our needs. To get the exact time value we multiply this value by 24 and that will be the value-based hour unit.

**Step 3:**

- Modify the formula in the following way –

`=SUM(C5:C9)*24`

**Step 4:**

- Then press
**Enter**.

**Step 5:**

- Drag the
**Fill Handle**icon towards the right side.

This is our required value. This value is based on the hour unit.

**Download Practice Workbook**

Download the following workbook to exercise while you are reading this article.

**Conclusion**

In this article, we described the sum of time in Excel and showed the solution when the sum of time is not working. I hope this will satisfy your needs. Please give your suggestions in the comment box.

**Related Articles**

- How to Calculate Total Hours in Excel
- How to Calculate Total Time in Excel
- How to Calculate the Duration of Time in Excel
- How to Calculate Lag Time in Excel
- How to Calculate Lead Time in Excel
- How to Calculate Median Follow-up Time in Excel
- How to Calculate Percentage of Time in Excel

**<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel**

Hi Alok,

Thank you so much, your thorough explanation really helped me!

Shimrit

Hi Shimrit, you are most welcome! We are glad to hear that it worked for you.

Regards

-ExcelDemy Team

4. Change the Cell format to Solve Not Working Issue with SUM the Time Value

This was reeeeally helpful, I know the sum was not working right thanks for sharing!

Hello

Jessica H,You are most welcome.

Regards

ExcelDemy