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.

**Table of Contents** hide

Download Practice Workbook

7 Ways to Calculate the Duration of Time in Excel

1. Calculate Time Duration with Simple Formula

2. Calculate Time Duration with the TEXT Function

3. Measure Elapsed Time in Hours

4. Get the Duration of the Time in Minutes

5. Calculate Time Duration from a Start Time to Now

6. If Function to Get Duration From an Earlier Time to a Former Time

7. Measure Duration of Different Units of Time

Conclusion

Related Articles

## Download Practice Workbook

**Calculate the Duration of Time.xlsx**

## 7 Ways to Calculate the Duration of Time in Excel

### 1. Calculate Time Duration with Simple Formula

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.

**Read More: How to Calculate Time in Excel (16 Possible Ways)**

### 2. Calculate Time Duration with the TEXT Function

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.

**Related Content: How to Calculate Turnaround Time in Excel (4 Ways)**

### 3. Measure 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 Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)**

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

**Read More:** How to Add Minutes to Time in Excel (5 Easy Ways)

**Similar Readings**

- Excel Formula to Calculate Hours Worked Minus Lunch
- Formula for Overtime over 40 Hours [with Free Template]
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
- Excel Timesheet Formula with Lunch Break (3 Examples)
- How to Calculate Cycle Time in Excel (7 Examples)

### 5. Calculate Time Duration from a 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.

**Read More: How to Add Minutes to Time in Excel (3 Quick Methods)**

### 6. If Function to Get Duration From an Earlier Time to a 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.

**Read More: How to Subtract Time in Excel (7 Quick Methods)**

### 7. Measure Duration of Different Units of Time

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,

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

- Timesheet Formula in Excel (5 Examples)
- How to Calculate Time Difference in Excel (13 Ways)
- Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
- Calculate Hours Between Two Times in Excel (6 Methods)
- How to Calculate Time Difference Between AM and PM in Excel

## FAQs

### How do I calculate time DURATION in Excel? ›

**How to Calculate Time in Excel**

- Enter the start date and time in cell A2 and hit Enter. ...
- Enter the end time in cell B2 and hit Enter.
- Enter the formula =B2-A2 in cell C2 and hit Enter. ...
- Right-click on C2 and select Format Cells. ...
- Choose the Custom category and type “h:mm”

**What is the formula for calculating time DURATION? ›**

We can calculate the duration of an activity if we know the starting and finishing time. For example, if the morning assembly in a school begins at 8:00 a.m. and finishes at 8:25 a.m. the duration of assembly is the **difference of finishing time and starting time**. 0825 - 800 = 25 minutes.

**How do I calculate DURATION from start and end date in Excel? ›**

Calculate the difference in days

Select cell D2, which is the first blank cell in the Duration column. **Type =C2-B2, and then press RETURN** . Excel displays the result as the number of days between the two dates (104).

**How do you manually calculate time duration? ›**

**Calculating Time Difference Manually**

- Convert the time to a 24-hour format. ...
- Convert all end times from hours to minutes. ...
- Subtract the start time minutes from the end minutes.
- Perform the same subtraction operation for the hours.
- Finally, combine the hours and minutes results to get the answer.