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

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

### Method 1 – Using Conventional Formula

**Steps:**

- Select cell
**E10**. - Use the following formula in it.

`=E5+E6+E7+E8+E9`

- Press
**Enter**.

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

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

- 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)`

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

- Click
**OK**.

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

**Steps:**

- Select cell
**E5**. - Use the following formula.

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

- Press
**Enter**.

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

### Method 6 – Calculate Total Time in Days, Hours, and Minutes

Consider the following dataset.

If we try to calculate the total time directly using the conventional subtraction formula, it gives something like this.

**Steps:**

- Find the difference by putting the following formula in cell
**D5**.

`=C5-B5`

- Press
**Enter**on your keyboard.

- Open the
**Format Cells**box by pressing**Ctrl + 1**on your keyboard. - Go to the
**Number**tab and select**Custom**as**Category**. - Under
**Type,**write down the following

`d "day", h "hours", mm "minutes"`

`.`

- Click on
**OK**and you will get the desired result for the cell.

- Select the cell again and click and drag the fill handle icon to the end of the column to replicate it.

## How to Calculate Total Hours in Excel

Let’s take the dataset from before again.

**Steps to Calculate Duration:**

- Select cell
**E5**. - Insert the following formula.

`=D5-C5`

- Press
**Enter**.

- Click and drag the fill handle icon down to replicate the formula for the rest of the cells.

**Calculate Total Hours in the Week:**

- Select cell
**E10**. - Insert the following formula in it.

`=SUM(E5:E9)`

- Press
**Enter**.

## How to Calculate Total Average Time in Excel

**Steps:**

- Select cell
**E10**. - Use the following formula in the cell.

`=AVERAGE(E5:E9)`

- Press
**Enter**.

**Download the Practice Workbook**

## 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**