If you want to know how to calculate the duration of time in Excel, you will find this article helpful. In this article, I’ve discussed 7 different ways to calculate time.

Suppose in your dataset, starting times and ending times are given. Now, I’ll show you how you can measure time durations from the starting times to the ending times.

## 1. Applying Excel Formula to Calculate Time Duration

You can calculate time duration in Excel with a simple subtraction **formula**. First,

➤ Type the following formula in cell **D5**,

`= C5-B5`

The formula will subtract the time of cell **B5 **from the time of cell **C5**. As a result, you will get the duration in cell **D5**.

➤ Press **ENTER**.

You’ll see the output will be displayed in a time format, which is inappropriate in this case. You want to know the duration that means the difference between the two times. In time duration **A.M./P.M. **doesn’t make any sense. So, you have to change the time format.

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

It will open the** Number **tab of the **Format Cells **box.

➤ Select a suitable time format from the **Type **box and click on **OK**.

In this demonstration, I’ve selected** the h:mm** format.

After that, 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.

As a result, you will get all the time durations.

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. So, the duration should be 8 hours 20 minutes.

It is happening because the time in cell **B9 **has a second value that is not being displayed.

If you select the cell, you will see in the formula bar that there is a value in the **“second” **argument of **the TIME function**.

You can display these second values.

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

As a result, you will see the second value is displaying in all the time inputs.

## 2. Inserting Excel TEXT Function to Calculate Duration of Time

With **the TEXT function**, you can calculate time duration in your desired format. First,

➤ Type the following formula in cell **D5**,

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

The formula will first calculate the time duration by subtracting the time of cell **B5 **from the time of cell **C5** and then it will show the duration in **hh:mm:ss **format.

➤ Press **ENTER**.

As a result, you will get the time duration in cell **D5**.

➤ Drag cell **D5 **to the end of your data set.

As a result, you will get all the time durations in **hh:mm:ss **format.

**Read More: **How to Calculate Total Hours in Excel

## 3. Calculating Elapsed Time in Hours

Excel assigns the number 1 to the 24 hours of a day. So 00:00 AM/ 12:00 PM has a value of 0 and 11:59 PM has a value of 0.999. Any time between these gets a fraction of 1. So, when you multiply the time difference by 24, you will get the time duration in hours.

In this method, I’ll show you how you can calculate the duration of time in Hours. First,

➤ Type the following formula in cell **D5**,

`= (C5-B5)*24`

The formula will subtract the time of cell **B5 **from the time of cell **C5** and multiply the value by 24. As a result, you will get the duration in cell **D5**.

➤ Press **ENTER**.

You will get the result in time format, so you have to change the format to **Number **to get the time duration.

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

As a result, you will get the time duration in hours.

Now,

➤ Drag the cell **D5**.

As a result, you will get all the time durations.

**Read More: **How to Sum Time in Excel

## 4. Getting the Duration of the Time in Minutes

You can also get the time duration in minutes. First,

➤ Type the following formula in cell **D5**,

`= (C5-B5)*24*60`

The formula will subtract the time of cell **B5 **from the time of cell **C5** and multiply the value by 24 and 60. As a result, you will get the duration in cell **D5**.

➤ Press **ENTER**.

You will get the result in time format, so you have to change the format to **Number **to get the time duration

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

As a result, you will get the time duration in minutes.

Now,

➤ Drag the cell **D5**.

As a result, you will get all the time durations.

## 5. Calculating Time Duration from Start Time to Now

Time duration from a past time to now can be determined by Excel by using **the NOW function**.

Suppose, you have some past time in your dataset and you want to find out time duration from that time to the present moment. To do that

➤ Type the following formula,

`= NOW () - B5`

The **NOW** function will give the present time and the formula will give the time duration from the time in cell **B5 **to the present time.

After that,

➤ Press **ENTER**.

You will get an output in time format with **AM/PM**. So, you need to convert the output to a suitable format.

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

It will open the** Number **tab of the **Format Cells **box.

➤ Select a suitable time format from the **Type **box and click on **OK**.

In this example, I’ve selected** the h:mm:ss** format.

As a result, you will get the time duration from the past time to present moment.

Now,

➤ Drag cell **C5 **to the end of your dataset.

So, you will get time durations for all the times of your dataset.

## 6. Using Excel IF Function to Calculate Duration From Earlier to Former Time

In all the previous methods we have subtracted an earlier time from a former time. When we subtract a former time from an earlier time, Excel gives an irrelevant value, because Excel considers both the time of the same date. We can solve the problem by using **the IF function** in our formula.

Suppose, we have the following dataset, where some of the activities have ended in the next day of starting day.

To find out the time duration for this dataset,

➤ Insert the following formula in cell **D5**,

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

The formula will subtract **B5** from **C5 **if **C5>B5**. Otherwise, it will subtract **C5** from **B5**. So, we will get the time duration irrespective of which one is earlier or former.

➤ Press **ENTER**.

As a result, you will get the output in number format. So, you need to change it to a suitable time format.

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

It will open the** Number **tab of the **Format Cells **box.

➤ Select a suitable time format from the **Type **box and click on **OK**.

In this example, I’ve selected** the h:mm:ss** format.

As a result, you will get the time duration for the time of cells **B5 **and **C5 **in cell **D5**.

After that,

➤ Drag cell **D5 **to the end.

So, you will get the durations for all of the time intervals.

## 7. Calculating Duration of Different Units of Time in Excel

You can also measure the time duration of different units from two different time inputs.

Let’s say, you have the following dataset where you have starting time and ending tie of some activities. Now, you want to know the time duration of hours, minutes and seconds of those activities.

You can find out the hour duration by using **the HOUR function**.

➤ Type the following formula in cell **D5**,

`=HOUR(C5-B5)`

The formula will give the difference of the hour units of the times in cells **C5** and **B5**. So, you will get the hour duration.

➤ Press **ENTER**.

As a result, the hour duration will appear in cell **D5**.

You can find out the minute duration by using **the MINUTE function**.

➤ Type the following formula in cell **E5**,

`=MINUTE(C5-B5)`

The formula will give the difference of the minute unit of the times in cells **C5** and **B5**. So you will get the minute duration.

➤ Press **ENTER**.

As a result, the minute duration will appear in cell **E5**.

You can find out the second duration by using **the SECOND function**.

➤ Type the following formula in cell **F5**,

`=SECOND(C5-B5)`

The formula will give the difference of the hour units of the times in cells **C5** and **B5**. So you will get the second duration.

➤ Press **ENTER**.

So, you will get the second duration of the two times of cells **B5 **and **C5** in cell **F5**.

Now,

➤ Drag cell **D5** to the end of your dataset.

As a result, you will get all the hour durations.

Similarly, you can get the minute and second durations for all of the times by dragging the cells **E5 **and **F5** to the end of your dataset respectively,

**Download Practice Workbook**

## Conclusion

In this article, I have demonstrated 7 methods to determine time duration in Excel which you can use to find the time duration in different conditions and in different units. I hope now you know how to calculate duration of time in Excel and will be able to apply the methods to your needs. If you have any confusion, please feel free to leave a comment.

**Related Articles**

- How to Calculate Lag Time in Excel
- How to Calculate Lead Time in Excel
- How to Calculate Median Follow-up Time in Excel
- How to Calculate Percentage of Time in Excel
- [Fixed!] SUM Not Working with Time Values in Excel

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