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

We’ll use a simple dataset with check in and check out times to calculate the duration.

how to calculate total time in excel dataset

The Excel formula to calculate the time duration is a simple subtraction formula. We’ll get the total duration.

calculate total time in excel duration formula

Method 1 – Using Conventional Formula

Steps:

Select cell E10.
Use the following formula in it.

=E5+E6+E7+E8+E9

Press Enter.

calculate total time in excel using conventional formula

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

Method 2 – Applying the SUM Function

Steps:

Insert the following formula in cell C10.

=SUM(E5:E9)

Press Enter.

calculate total time in excel using sum function

Method 3 – Using the AutoSum Feature

Steps:

Select the range you want to add. We used the range E5:E9.
Go to the Home tab.
Select AutoSum from the Editing group. You can directly click on AutoSum or you can click on the drop-down arrow and select Sum from the drop-down list.

calculate total time in excel using autosum feature

Excel will calculate the total time worked by putting in the SUM formula in the cell.

Method 4 – Calculate Total Time Over 24 Hours

We have a duration over 24 hours. If we used a regular sum, we’d likely get the wrong result since Excel will only display the hours.

Steps:

Calculate the total time in cell E10 by entering the following formula in the cell.

=SUM(E5:E9)

calculate total time in excel over 24 hours problem

The total time here shows 17 hours and 15 minutes which is not true.
Select cell E10 and press Ctrl+1 on your keyboard.
The Format Cells box will appear. Go to the Number tab in it.
Select Custom from the Category.
You can select the Type from the options or write down the format [h]:mm:ss in the box below it.

correcting format to calculate total time over 24 hours in excel

Click OK.

calculate total time over 24 hours in excel

Read More: How to Sum Time in Excel

Method 5 – Calculate the Total Time When the Date Changes

Let’s consider a slightly different dataset where the check out time is before the check in time, but in the next day.

Using a subtraction formula gives errors.

calculate total time in excel date change error

Steps:

Select cell E5.
Use the following formula.

=IF((D5-C5)<0,1-(D5-C5),(D5-C5))

Press Enter.

calculate total time in excel when a new date comes

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.