Md. Shamim Reza
Dates and Times in excel are considered as integer and fractional numbers respectively. We can calculate the elapsed time between two dates in excel. There are a few ways to easily do that. This article shows how to calculate elapsed time between two dates in excel in 5 different ways. These methods may also help to calculate the time differences between two times in excel. The following figure highlights the purpose of this article.
Download Practice Workbook
You can download the practice workbook from the download button below.
5 Ways to Calculate Elapsed Time Between Two Dates
We will use two ranges of full dates and times as our dataset to illustrate the methods. So, let’s find out what those methods are and how they even work.
1. Calculate Elapsed Time Between Two Dates by Subtraction
In this method, we are going to use the subtraction operator (–). Follow the steps below to see how it works.
📌Steps
- First, enter the following formula in cell D5:
=C5-B5- Then use the fill handle tool to apply the formula to the cells below.
- The integer parts represent the number of days between the dates. And the fractions account for the time differences between the dates.
- You can change the number format to get the actual duration.
- To do so, first select the entire range of Elapsed Time.
- Then click on the small icon for Number Format as shown in the following picture. You will find it from the Home tab.
- A new dialog box will open. Choose the desired time format as follows and then hit OK.
- As a result, the elapsed time will change to the h:mm:ss format as follows.
- In the case of dates only, you will get the elapsed time in the form of the total number of days between those dates.
Read More: How to Subtract Time in Excel (7 Quick Methods)
2. Calculate Elapsed Time Between Two Dates by DAYS Function
Using the DAYS function in excel can be an alternative to the earlier method. Follow the steps below to apply it here.
📌Steps
- First, apply the following formula in cell D5:
=DAYS(C5,B5)- Then, you will find the total number of elapsed days between the dates as shown below.
- You must enter a later date in the first argument. Otherwise, you will see a negative sign before the numbers.
Read More: How to Calculate Elapsed Time in Excel (8 Ways)
3. Estimate Elapsed Time Between Two Dates by DATEDIF Function
You will not find the DATEDIF function in the list of formulas in excel. Yet, you can apply it in excel. We can easily calculate the elapsed time between two dates using this function. It also allows us to get the results in different formats. Let’s follow the steps to take advantage of this function to calculate elapsed time between dates.
📌Steps
Elapsed Days:
- Apply the following formula in cell D5:
=DATEDIF(B5,C5,"d")- You will get the number of days as in the earlier methods.
Elapsed Weeks:
- To get the number of weeks instead, change the formula in cell D5 to the following one.
=DATEDIF(B5,C5,"d")/7- Then, change the formatting to Number and copy the formula down.
Elapsed Months:
- After that, enter the following formula in cell D5 to get the elapsed months between those dates.
=DATEDIF(B5,C5,"m")
Elapsed Years:
- Next, apply the following formula in cell D5 for the elapsed years between those dates.
=DATEDIF(B5,C5,"y")
Difference in Days/Months only:
- Using the following formulas will only show the difference of days ignoring month and year, months ignoring year, and days ignoring year respectively.
=DATEDIF(B5,C5,"md")
=DATEDIF(B5,C5,"ym")
=DATEDIF(B5,C5,"yd")
Elapsed Days, Months & Years Altogether:
- First, remove the times from the dates for a concise output. Otherwise, keep it as it is.
- Now, enter the following formula in cell D5 to get the elapsed time in a more organized format to understand.
=DATEDIF(B5,C5,"y")&" years, " &DATEDIF(B5,C5,"ym")&" months, " &DATEDIF(B5,C5,"md")&" days"
Related Content: Excel Formula To Calculate Time Worked
Similar Readings:
- How to Subtract and Display Negative Time in Excel (3 Methods)
- Calculate Turnaround Time in Excel (4 Ways)
- How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- Excel Formula to Calculate Hours Worked Minus Lunch
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
4. Calculate Elapsed Time Between Two Dates by INT Function
The INT function rounds up numbers in excel. We can use this function to get the elapsed time between dates. Follow the steps below to apply this method.
📌Steps
Elapsed Days Only:
- Simply enter the following formula in cell D5:
=INT(C5-B5)- You will get the number of elapsed days between the dates rounded up as follows.
Elapsed Days & Duration:
- With the help of the TEXT function, you can change the formatting of the elapsed time.
- For that, apply the following formula instead in cell D5:
=INT(C5-B5)&" days, "&TEXT(C5-B5,"h"" hours, ""m"" minutes, ""s"" seconds""")- Then, you will get the elapsed time as shown below.
5. Estimate Total Working Days Between Two Dates in Excel
Excel has an inbuilt function called the NETWORKDAYS function to calculate the total working days between two dates. You can also do that easily by following the steps below.
📌Steps
- First, enter the following formula in cell D5:
=NETWORKDAYS(B5,C5)- This will give the total working days between those dates as follows.
- You can use custom holidays in the arguments of this function. Alternatively, you can use the NETWORKDAYS.INTL function to calculate the total working days between two dates with custom holiday parameters.
- Just apply the following formula in cell D5:
=NETWORKDAYS(B5,C5,$C$13:$D$13)- Finally, the custom holidays are no longer included in the number of total workdays.
Related Content: How to Calculate Average Handling Time in Excel (2 Easy Ways)
Things to Remember
- Entering the start date as the first argument works best in the DATEDIF function.
- Microsoft doesn’t recommend the DATEDIF “md” argument as it may give inaccurate results.
Conclusion
Now you know how to calculate the elapsed time between two dates in 5 different ways. Please use the comment section below for further queries or suggestions. You may visit our Exceldemy blog to learn more about excel and improve your performance.
