SUM Not Working with Time Values in Excel: 5 Methods

Method 1 – Get the SUM of Time Value Inserting Individual Cells

Step 1:

• Go to Cell E5.
• Find the sum of the Cells C5 and D5 using the following formula –

Step 2:

• Press Enter.

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

Step 3:

• Pull the Fill Handle icon.

Get the sum of time. Add more than two values in the same way.

Method 2 – Apply the SUM Function to Find the Time Value

HerUse the SUM function to sum time values.

Step 1:

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

Step 2:

• Press Enter.

Step 3:

• Pull down the Fill Handle icon to the last cell.

Get the sum of two weeks based on each day.

Method 3 – Sum Time Applying the AutoSum Tool

Apply the AutoSum tool here.

Step 1:

• Select the cell where we need to apply the AutoSum tool.
• Select Cell E5.
• Go to the Home tab.
• From Editing choose the AutoSum tool.

Step 2:

• Click AutoSum, a formula will appear on that cell.
• Edit the formula argument if needed.

Step 3:

• Press Enter.

Step 4:

• Apply AutoSum in the rest of the cells.

Auto sum makes it simple to sum the time values.

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

Step 1:

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

Step 2:

• Press Enter.

The sum exceeds 24 hours it only shows the reminder value.

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.
• Select [h]:mm:ss type.

Step 4:

• Press OK.

Get the exact sum of time.

Step 5:

• Get the sum at Cell D10.

Get the solution of the sum of time.

Method 5 – Perform Multiplication Operation When SUM of Time Not Working

Step 1:

• Apply the following SUM formula at cell C10 –

Step 2:

• Press Enter.

This result does not meet our needs. To get the exact time value, we multiply this value by 24, which will be the value-based hour unit.

Step 3:

• Modify the formula in the following way –

Step 4:

• Press Enter.

Step 5:

• Drag the Fill Handle icon towards the right side.

This value is based on the hour unit.

Download Practice Workbook

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

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