The Excel DATEDIF() function can be used to calculate the difference between two dates in Excel. Knowing how to do this is an important skill, given how much data analysis involves working with dates. So many real-world tasks involve working with dates, regardless of the industry you work in. In this tutorial, you’ll learn how to calculate the difference between two dates in Excel.
By the end of this tutorial, you’ll have learned:
- How to use Excel to calculate the difference between two dates in Excel
- How to use the DATEDIF() and DAYS() functions in Excel
- How to use Excel to calculate the difference in months and years between two dates in Excel
How Excel Stores Dates
Excel has a clever way of storing date and time values. The date portion is stored as an integer value, with January 1, 1900, as the first date. Any date beyond that is incremented by 1. For example:
- January 3, 1900, would be equal to 3
- November 23, 2023, would be 45253
This is important because it allows you to understand how we can easily find the difference between two dates. If days are simply numbers in Excel, we can actually just subtract the two days from one another.
How to Get the Difference Between Two Dates in Excel
How can you get the difference between two dates in Excel?
The best way to get the difference between two dates in Excel is by using the DATEDIF() function. For example, if you have a start and end date in cells A2 and A3, you can just write = DATEDIF(A2, A3, “d”), which returns the number of days between the two dates.
Similarly, Excel provides a built-in function for calculating the number of days between two dates: the DATEDIFF() function.
If you can simply subtract two dates, why would you want to use anything else? The function gives you the extra benefit of handling errors, such as when a start date happens before an end date. In this case, the function would return a #NUM! error.
Let’s take a look at how the function can be used:
= DATEDIF(start_date, end_date, unit)The function takes three different arguments:
start_date=represents the the starting dateend_date=represents the end dateunit=represents the type of information you want to return
The DATEDIF() function accepts the following units:
| Unit | Returns |
| “Y“ | The number of whole years. |
| “M“ | The number of whole months. |
| “D“ | The number of days between the two dates. |
| “YM“ | The difference between the months in start_date and end_date. The days and years of the dates are ignored, meaning that it only calculates the difference in months. |
| “YD“ | The difference between the days of start_date and end_date. The years of the dates are ignored, meaning that it only calculates the difference in days as if the dates fell in the same year. |
Let’s take a look at an example of how you can calculate the number of days between two dates as days, using the DATEDIF() function. We’ll load two dates representing a start date and an end date into cells B1 and B2, respectively. Then, in cell D2, we’ll use the DATEDIF() function and pass in:
- The start date referencing B1,
- The end date referencing B2, and
- “d”, indicating that we want to calculate the number of days between the two dates
When we evaluate the function, we get 506 back. This indicates that there have been 506 days between the two dates.
In the following sections, you’ll learn how to calculate the number of months and years between two dates in Excel.
How to Calculate the Number of Months Between Two Dates in Excel
You can use the Excel DATEDIF() function to calculate the number of whole months between two dates. The function will always return a whole number, rather than indicating how many partial months are between two dates. (Interested in learning more about calculating the number of months between two dates – learn how to calculate partial months and whole months).
Let’s take a look at how we can calculate the number of whole months between two dates in Excel:
In the example above, we passed the start date, end date, and the unit of “m” into the DATEDIF() function. The function calculates the number of complete months between the two dates, meaning that partial months are ignored.
In the following section, you’ll learn how to calculate the number of complete years between two dates in Excel.
How to Calculate the Number of Years Between Two Dates in Excel
Similar to the two previous examples, Excel makes it easy to calculate how many whole years are between two dates. This can also be done by using the DATEDIF() function. As the unit, we’ll use the “y” string, indicating we want to want to calculate the number of complete years. Excel provides a number of different ways to calculate the number of years between two dates, including calculating partial dates.
Let’s take a look at an example of how this can be done in Excel:
From the example above, we can see that Excel returns that there is one year between the two dates. Keep in mind that the function ignores any partial years, essentially rounding down the number of precise years between the two dates.
Conclusion
In this tutorial, you learned how to use the Excel DATEDIF() function to calculate the difference between two dates. Knowing how to work with dates is an incredibly valuable skill to understand when working with Excel. So many real-world tasks involve working with dates, regardless of the industry you work in. You first learned how to use the DATEDIF() function and the different options that it provides.
From there, you learned how to use the function to calculate the number of days between two dates using the unit of “d”. From there, you learned how to customize the function using additional arguments of “m” and “y”. These return the number of whole months and years between two dates, respectively.
Additional Resources
To learn more about related topics, check out the tutorials below: