How to Use the Excel DATEDIF Function? 3 Easy Examples
The Excel DATEDIF formula calculates the gap between two date ranges in the format of years, months, or days.
It was originally introduced in a very old Excel version and documented only in Excel 2000. But, it can still be used in the latest versions of Excel including Office 365.
In this tutorial, I’ll show you how to use Excel DATEDIF with the help of some examples.
You’ll learn:
Related:
How to Use the NPER Excel Formula: 2 Easy Examples
The Excel SMALL Function – 3 Best Examples
The Excel CHOOSE Function – 4 Best Uses
Excel DATEDIF Syntax
=SYNTAX(start_date,end_date,Unit)Where start_date is the beginning date of the time period
end_date is the ending date of the time period and
Unit is the type of difference you are looking for ( days, months, or years)
You can use the following time unit options inside the DATEDIF formula:
‘y’ – to find the difference in terms of complete years.
‘m’ – to find the difference in terms of complete months.
‘d’ – to find the difference in terms of complete days.
‘ym’ – to find the difference in months, ignoring months.
‘md’ – to find the difference in days, ignoring months
‘yd’ – to find the difference in days, ignoring years.
Note: Excel will not display or suggest the syntax for DATEDIF as it does for other normal functions.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
How to Use the Excel DATEDIF Function?
The following examples will help you appreciate the formula’s usage better. I recommend you open a rough spreadsheet and follow along to gain a better understanding.
Example 1: Common Uses
In this example, column D contains the date March 1, 2019 and column E contains the date April 5, 2021. In column F, we can use the following variations of the DATEDIF function to calculate the difference in years, months and days respectively.
F5=DATEDIF(D5,E5,”y”) // returns 2
F6=DATEDIF(D6,E6,”m”) // returns 25
F7=DATEDIF(D7,E7,”d”) // returns 766
Example 2: Calculate the Difference in Days Using DATEDIF
In this example, we use the DATEDIF formula to calculate the difference in days between dates. Please note that you can use three different time units for days inside the DATEDIF function: (1) total days, (2) days ignoring years, and (3) days ignoring months and years.
=DATEDIF(D11,E11,”d”) // returns the difference in total days i.e 766 days
=DATEDIF(D12,E12,”yd”) // returns the difference in days ignoring years i.e 35 days
=DATEDIF(D13,E13,”md”) // returns the difference in days ignoring months and years i.e 4 days
Example 3: Calculate the Difference in Months Using DATEDIF
Use the DATEDIF formula to calculate the difference in months between dates. You can use two different time units for months inside the DATEDIF formula: (1) complete months and (2) in terms of months but ignoring years
=DATEDIF(D17,E17,”m”) // result: difference in complete months
=DATEDIF(D18,E18,”ym”) // result: difference in terms of months but ignoring years
Example 4: Calculate the Difference in Years Using DATEDIF
Use the DATEDIF formula to calculate the difference in years between two dates.
=DATEDIF(D21,E21,”y”) // difference in complete years
A small caveat: Since, DATEDIF rounds off the difference to the next lowest value, it is advisable to use YEARFRAC to find a more accurate difference in years.
Important Note:
The DATEDIF function returns the number of complete days, months, or years by rounding them off to the next smallest value. This means that it may give incorrect results when calculating the date difference in some cases. It is advisable to be careful while using DATEDIF and double-check the results before proceeding further.
The #NUM! error usually arises when the start_date is greater than the end_date.
The #VALUE! Error usually occurs when invalid arguments are used inside the formula.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
Closing Thoughts
In this short guide, I have shown how to properly use the DATEDIF function in Excel. I hope you find this very useful in your spreadsheet calculations. If you have any questions about this or any other Excel feature, feel free to let us know in the comments. We’re always happy to help.
Visit our free Excel Resources Centre for more high-quality Excel guides.
Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules.
You can train your entire team in Excel and other business software, for a single low monthly fee by clicking here.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
