How to Sum Time in Excel

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.

How to Sum Time in Excel - hours worked

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

How to Sum Time in Excel - format cells corectlly

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.

How to Sum Time in Excel - sum function

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

How to Sum Time in Excel - all cells we want to sum

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.

How to Sum Time in Excel - sum hours worked

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’.

How to Sum Time in Excel - format cell

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

How to Sum Time in Excel - cells formated time

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

How to Sum Time in Excel - time formating

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.

How to Sum Time in Excel - time format without seconds

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

Click on OK, and we’re done!

How to Sum Time in Excel - time format used

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

How to Sum Time in Excel - result

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

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!

Comments

Add comment

Your email address will not be published.

Your personal information will only be used for the purpose of processing this comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed.