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

Read More: How to Calculate Total Hours in Excel

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

• You will get the desired durations.

• Drag cell D5 to the end of the dataset to AutoFill.

## Method 7 – Calculating Durations in Different Units of Time in Excel

We have the following dataset with a starting time and an ending time of some activities. We’ll get the time duration in hours, minutes and seconds.

• Use the following formula in cell D5:
`=HOUR(C5-B5)`

• Hit Enter.

Insert the following formula in cell E5:

`=MINUTE(C5-B5)`

• Hit Enter.

• Insert the following formula in cell F5:
`=SECOND(C5-B5)`

• Press Enter.

• Drag cell D5 to the end of your dataset.

• Drag the cells E5 and F5 to the end of your dataset, respectively.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF