In this tutorial we are going to look at the undocumented function in Excel DATEDIF. This could be a great tool to dynamically calculate the number of years, months and days between two dates.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Excel has an undocumented function called DATEDIF. If you start to type =DATEDIF in Excel it will not present the explanation or the syntax, but this formula allows you to calculate the period between two dates in years, months or days, including or excluding various components.
The syntax for this function is:
=DATEDIF(start_date, end_date, unit)
The unit is the unit of measure that you want the results to appear in. There are six different units, here are the first three:
Y – years
M – months
D – days
These three just calculate the full periods of years, months or days between two dates. So, here are the results of the formulas using these units with the following dates:
The other three units are:
YM – full months, ignoring days and years
YD – days, ignoring years
MD – days, ignoring months and years
Here are the results of the DATEDIF function using these three units:
So, “YM” takes the months only – December (12) minus July (7) and gives the result of 5.
“YD” takes the days, ignoring the years and subtracts them. December 31 minus July 12 of the same year would be 172 days.
“MD” takes the days, ignoring the years AND months and subtracts them. The 31 minus 12 equals 19.
Now, what if you wanted to create a dynamic calculation between two dates that would be something like this:
14 years, 5 months, and 19 days
All you would need to do is concatenate three of the DATEDIF functions with some text as follows:
=DATEDIF(C1,C2,”Y”)&” years, “&DATEDIF(C1,C2,”YM”)&” months, and “&DATEDIF(C1,C2,”MD”)&” days”
You can see that we used the DATEDIF function with a “Y” for the years, with a “YM” to get the months ignoring the days and years, and finally with an “MD” to get the days, ignoring the months and years. We concatenated in between those the text to indicate years, months and days. Now when we change any of the dates, Excel will dynamically adjust the results of the period between them:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!