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? ›
- 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”
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).
- 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.