How to Calculate the Duration of Time in Excel (7 Methods)

We have a dataset with starting times and ending times. We’ll measure time durations from the starting times to the ending times.

Method 1 – Applying an Excel Formula to Calculate Time Duration

Use the following formula in cell D5:

= C5-B5

The formula will subtract the time of cell B5 from the time of cell C5.

Hit Enter.

Select More Numbers Formats in the box in the Number ribbon of the Home tab.

This will open the Number tab of the Format Cells box.
Select a suitable time format from the Type box and click on OK. We’ve selected the h:mm format.

You will find the duration between the times of cell B5 and C5 in cell D5.

Drag cell D5 to the end of your data set.

If you observe carefully, you will see the duration in cell D9 showing 8 hours and 19 minutes. But the starting time and the Ending time are 10:00 AM and 6:20 PM. This is because the time in cell B9 has a seconds value that is not being displayed.

Select Time in the box of the Number ribbon of the Home tab.

You will see the seconds value is displayed in all the time inputs.

Method 2 – Inserting the TEXT Function to Calculate Duration

Use the following formula in cell D5:

=TEXT(C5-B5,"hh:mm:ss")

Hit Enter.

Drag cell D5 to the end of your data set.

Method 3 – Calculating Elapsed Time in Hours

Use the following formula in cell D5:

= (C5-B5)*24

Hit Enter.

Select Number in the box in the Number ribbon of the Home tab.

You will get the time duration in hours.

Now,

Drag the cell D5 to AutoFill.

Read More: How to Sum Time in Excel

Method 4 – Getting the Duration in Minutes

Use the following formula in cell D5:

= (C5-B5)*24*60

Hit Enter.

Select Number in the box in the Number ribbon of the Home tab.

You will get the time duration in minutes.

Drag the cell D5 down to AutoFill the column.

Method 5 – Calculating Duration from Start Time to Now

We have some past times in the dataset (in the B column) and want to find out time duration from that time to the present moment.

Insert the following formula,

= NOW () - B5

Hit Enter.

Select More Numbers Formats in the box on the Number ribbon of the Home tab.

This will open the Number tab of the Format Cells box.
Select a suitable time format from the Type box and click on OK. We’ve selected the h:mm:ss format.

You will get the time duration from the past time to now.

Drag cell C5 to the end of the dataset.

Method 6 – Using the IF Function to Calculate Time Between Two Consecutive Days

In all the previous methods, the ending time was later in the day. We have the following dataset, where some of the activities have ended in the next day.

Insert the following formula in cell D5:

=IF(C5>B5, C5-B5, B5-C5)

The formula will subtract B5 from C5 if C5>B5, which happens if we didn’t pass through midnight. Otherwise, it will subtract C5 from B5.

Press Enter.
Select More Numbers Formats in the box on the Number ribbon of the Home tab.

This will open the Number tab of the Format Cells box.
Select a suitable time format from the Type box and click on OK. We’ve selected the h:mm:ss format.