My name’s Lee and I’ll be your guide in this tutorial on How to Sum Time in Excel in Excel.
We’ll also tell you how to sum time when the total exceeds 24 hours, which is a common issue you may encounter.
Let’s get started!
How to Sum Time in Excel Video
How to Sum Time in Excel
In the previous tutorial, you could learn how to calculate hours worked.
Now, we’ll go through how to sum hours worked.
Have a look at the table.
We want to sum all the hours spent at work throughout the week, from Monday to Friday.
To do this, we’ll use the SUM function.
Remember that before you start calculating time, it’s important to format all the cells containing time data correctly.
To learn how, watch the Sheet Leveller tutorial on ‘How to insert and format time in Excel‘.
Now, we can move on to the calculation itself.
We’ll type the formula in the cell where we want the result to appear.
Start with an equal sign, followed by the SUM function, and select all the cells with the time values.
How to Sum Time in Excel When Its Total Exceeds 24-hours
If the total time does not exceed 24 hours, the sum is correct.
But, in this case it’s clear that 43 minutes is not the accurate sum.
This is a common error, so we’ll learn how to fix it.
Right-click on the cell and go to ‘Format Cells‘.
In the tab ‘Number‘, select ‘Custom‘, and select the formatting option with the letter ‘h‘ in square brackets.
Delete ‘ss‘ from the bar above to display only hours and minutes.
This way, we’ll be able to calculate the sum accurately, even if it exceeds 24 hours.
Click OK, and the sum is correct!
All we had to do was adjust the formatting.
If you found this tutorial helpful, and if you want to learn Excel our complete guide section is the place to go that will help you use Excel like a boss!
Want to learn how functions work in excel then check out my excel functions page where you will learn many of the functions. Or looking for formulas? Well, we have you covered there too with our Excel Formulas page.