How to Calculate Total Time in Excel (6 Suitable Examples)

Indeed “time is money”. Time management is crucial for any business or organization to improve productivity, efficiency, and overall improvement. And adding or subtracting time is an important part of any time/task tracker. In this tutorial, we are going to demonstrate different ways and scenarios and how to calculate total time from them in Excel.

How to Calculate Total Time in Excel: 6 Suitable Examples

In this section, we will go through different scenarios and examples of time differences and total times. The dataset will be more or less like the following.

The Excel formula to calculate the time duration worked here is the simple subtraction formula.

We will be going over a total of six different sections. Of them, the three are just different methods to calculate total time from column E of the Excel spreadsheet. Others include formatting cells to sum in hours and minutes, what happens when the sum time is over 24 hours on the same Excel table, and total times when different dates are involved.

Follow along to see how each of them works, or find the one you need from the table above.

1. Using Conventional Formula

To calculate the total time listed in column E, we can simply add them using the conventional addition formula of Excel. This is the formula where we start it with an equal sign (=) and the values that we will add follow with a plus sign (+) between them.

Follow these steps to see the conventional formula to calculate the total time worked in a week from the dataset in Excel.

Steps:

• First, select cell E10 (the cell we are placing the addition result).
• Then write down the following formula in it.

• After that, press Enter.

This is one way to calculate the total time in Excel.

2. Applying SUM Function

Another way to get the same outcome from the same dataset is to use the SUM function. The SUM function can take several arguments- either with a comma in between them or as an array and returns the summation of all the arguments.

Follow these steps to see how we can use this function in the formula to calculate the total time worked from this dataset in Excel.

Steps:

• First, select cell E10.
• Now insert the following formula in the cell.

• Finally, press Enter.

Thus, the SUM function will calculate the total time in Excel.

3. Using AutoSum Feature

There is yet another way to calculate total time, or any other data for that matter, in Excel. It is to use the AutoSum feature from the Excel ribbon. It is a very helpful feature not only for these additions but also for other statistical calculations like finding the average, counting, etc. Also, you can use built-in functions from this feature too.

Follow these steps for the detailed guide to calculate the total time from our dataset in Excel.

Steps:

• First, select the range you want to add. This is the range E5:E9
• Then go to the Home tab of your ribbon.
• Now select AutoSum from the Editing You can directly click on the AutoSum or you can click on the downward-facing arrow, in which case select Sum from the drop-down list.

Either way, Excel will calculate the total time worked by putting in the SUM formula in the following cell.

4. Calculate Total Time Over 24 Hours

Previously, we have selected smaller time frames for demonstration purposes. But this isn’t always the case in real-time scenarios. Let’s take a longer time frame on the same dataset.

If we sum these longer hours and minutes and the result time is over 24 hours, which obviously is this case, Excel doesn’t generally show the correct format. This happens because Excel divides the total 41 hours into 1 day and 17 hours. Because days are not formatted in most cases, the cells show the hour values only.

So what we need to do to calculate the sum time or total time in Excel that gives the result over 24 hours is to format the cell. You can do that before or after the summation.

Follow these steps for a more detailed guide.

Steps:

• First, calculate the total time in cell E10 by using the following formula in the cell.

• Then press Enter.

• As you can see, the total time here shows 17 hours and 15 minutes which obviously is not true.
• To format the cell further, select cell E10 and press Ctrl+1 on your keyboard.
• As a result, the Format Cells box will appear. Now go to the Number tab in it.
• Then select Custom from the Category.
• Next, you can select the Type from the options or write down the format [h]:mm:ss in the box below it.

• Once you are done, click OK. This will show the correct time in the desired format.

In effect, we can calculate the total time and get the correct result in the desired format in Excel.

Read More: How to Sum Time in Excel 

5. Calculate the Total Time When the Date Changes

Sometimes, a work or event duration may cross 12 midnight. In those cases, the later time (checkout time) is smaller than the entry time. So while calculating the total time in between them, it often comes down to negative values or errors which is ridiculous. What actually happens is, that as there are no dates involved in those entries, Excel just subtracts a larger value from a smaller value.

Let’s consider a slightly different dataset now.

Here, you can see some of the check-out values are lower than the check-in values. What happened here is, let’s say, the person checked in at 8 in the evening and left at 1 at night. As the clock passed 12, it technically was the next day. But when we try to calculate the total time he worked, it comes to something like this.

This was supposed to be 5 hours. But in these scenarios, where an Excel chart may contain random values like this, we can use a formula consisting of the IF function to eliminate this error.

Follow these steps to see how we can calculate the total time worked using this formula for the dataset.

Steps:

• First, select cell E5.
• Then write down the following formula.

• After that, press Enter.

• Now select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

This is how we can calculate the total time elapsed if the dataset randomly contains durations that cross 12 AM and the date changes in Excel.

The downside of using this method is, if it crosses more than 24 hours, Excel treats it as if it was under the duration. For example, an event spanning 26 hours will give 2 hours as the total time using this formula.

6. Calculate Total Time in Days, Hours, and Minutes

In this section, let’s consider a scenario like this.

A starting time and ending time may not only be on different days but also they can start or end in random time values. If we try to calculate the total time directly using the conventional subtraction formula, it gives something like this.

We clearly don’t want a value like this. If we want the sum of days, hours, minutes, or even seconds, we need to go for different approaches. Excel has different functions such as the HOUR function, the MINUTE function, etc to calculate the total sum of hours or minutes in between a range. But we are choosing the formatting method. Due to the formatting, we can have one cell containing all the values inside it. Using independent functions does not directly provide such outcomes.

Follow these steps to see how we can do that.

Steps:

• First, find the difference by putting the following formula in cell D5.

• Then press Enter on your keyboard.

• Now open up the Format Cells box by pressing Ctrl+1 on your keyboard.
• Next, go to the Number tab and select Custom as Category.
• Then under Type write down the format as the following

• Finally, click on OK and you will get the desired result for the cell.

• To do this for the rest of the cells, select the cell again and click and drag the fill handle icon to the end to replicate it.

This is how we can calculate the total sum of days, hours minutes, or even seconds in Excel within a single cell, no matter whether the sum time exceeds over 24 hours or not.

How to Calculate Total Hours in Excel?

We can use the principles we described above to calculate total hours in Excel too. For example, let’s take the dataset from before again.

To calculate the total hours worked in between them follow these steps.

Steps to Calculate Duration:

• First, select cell E5.
• Then insert the following formula.

• After that, press Enter.

• Finally, select the cell again. Then click and drag the fill handle icon down to replicate the formula for the rest of the cells.

Now we can calculate the total hours in the week using any of the formulas used above. For the demonstration, we are using the SUM function for that. Follow these steps if you need a detailed guide.

Steps to Calculate Total Hours in Week:

• First, select cell E10.
• Then insert the following formula in it.

• Finally, press Enter.

How to Calculate Total Average Time in Excel?

Like calculating the sum of different formats of time like days, hours, minutes, and seconds in Excel, we can calculate the average of them by many methods. We will use the AVERAGE function to find the average time for the same dataset as above.

This function takes several arguments in different forms and returns the average of them as output.

Follow these steps to calculate the average time duration for each day in Excel.

Steps:

• First, select cell E10.
• Then write down the following formula in the cell.

• After that, press Enter.

That’s it. This will calculate the total average daily time in Excel.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.

Conclusion

These were different methods and scenarios to calculate the total time in Excel and express them in different formats. Hopefully, you have grasped the idea and can apply these principles to your scenarios. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

How to Calculate Total Time: Knowledge Hub

• [Fixed!] SUM Not Working with Time Values in Excel
• How to Calculate Years of Service in Excel
• How to Calculate Travel Time Between Two Cities in Excel
• Excel Formula To Calculate Time Worked

<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel