How to Sum Time in Excel

I’ll guide you through this tutorial which will show you how to sum time in Excel. And that’s not where we’ll wrap it up! As an extra piece of advice, we’ll tell you how to sum time when its total exceeds twenty-four hours, which is the most common issue you can potentially bump into when you try to add up time.

Let’s start!

See the video tutorial and transcription below: 

See this video on YouTube:
https://www.youtube.com/watch?v=RMd-zZLqUfM

In the previous tutorial, you could watch how to calculate hours worked.

In this tutorial, we’ll go through how to sum hours worked. Have a look at the table. We’re gonna sum all the hours spent at work throughout the week, from Monday to Friday. So here, as in any case when calculating a total, we’ll use the SUM function.

Remember that before you start inserting formulas and calculating time, it’s necessary to format all the cells that contain time data correctly.

To see how to format the cells, watch the EasyClick Academy video tutorial on how to insert and format time in Excel.

Alright now! We can move on right to the calculation itself and sum all the hours in the column ‘Hours Worked’. Every formula must begin with the equal sign. We’ll type it in the cell where we want the result to appear.

Then we’ll carry on by typing ‘SUM’, which means we want to use the function to calculate a total. Excel will look the function up and we’ll confirm by double-clicking on the selected option.

Now we select all the cells containing the time we want to sum and press ‘Enter’.

Here’s an important thing – if the total time does not exceed twenty-four hours, the sum calculated this way is correct.

How to Sum Time in Excel When Its Total Exceeds Twenty-Four Hours

However, in this case it’s quite clear that forty-three minutes is not an accurate sum.

This is a common error, so now we’ll have a look at how to fix it. It’s actually very easy. The catch here is that to add up correctly time if its total exceeds twenty-four hours, we need to adjust formatting. So, right-click on the cell and choose ‘Format Cells’.

In the tab ‘Number’, select ‘Custom’.

Scroll through the options under the section ‘Type’ and select the formatting option where the letter ‘h’ is in square brackets.

In this case, we don’t want seconds to show, so we’ll just delete them from the bar above, which leaves us only with hours and minutes.

Now, we’ll be able to calculate the sum accurately, even though it exceeds twenty-four hours.

Click on OK, and we’re done!

The sum that’s been calculated is correct. The only thing that needed to be changed was formatting.

If you’re interested in other time calculations, watch the whole series of video tutorials that focus on working with time in Excel.

• How to Insert and Format Time in Excel
• How to Calculate Hours Worked in Excel
• How to Calculate Hours Worked in Excel (Midnight Span)

If you’ve found this tutorial helpful, like us and subscribe to receive more videos from EasyClick Academy. Look at more tutorials that help you use Excel quick and easy!

See you in the next tutorial!