[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)

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.

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 –

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 –

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.

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 –

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 –

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