Excel is one of the most powerful and versatile office automation tools. From its earliest versions, it has preserved certain historic formulas that are still useful today. These include the DATEDIF function, a discreet but formidably effective tool for calculating the difference between two dates.
Although no longer officially documented in Excel, DATEDIF remains perfectly functional. It allows you to quickly determine the number of years, months or days separating two dates. In this article, we’ll look at how to use it, why it still exists and what modern alternatives there are, such as FRACTION.YEAR.
Why use DATEDIF in Excel?
DATEDIF is one of the oldest functions in the software. It originally came from the Lotus 1-2-3 spreadsheet program, and Microsoft decided to retain it to ensure compatibility with older files.
This means that even an Excel file from the ’80s can still open and run without a hitch on current versions. A major advantage, especially for companies working with archives.
If you’re growing your data skills, you may also find these useful:
How does DATEDIF work?
The DATEDIF syntax is simple:
Where unit can take different values:
-
"y"→ difference in years -
“
m"→ difference in months -
“
d"→ difference in days
Example of concrete calculations
| Type of calculation | Formula | Result |
|---|---|---|
| Years between two dates | =DATEDIF(A1;B1; “y”) | 33 |
| Months between two dates | =DATEDIF(A2;B2; “m”) | 399 |
| Days between two dates | =DATEDIF(A3;B3; “d”) | 12164 |
⚠️ Warning: certain arguments such as "MD" can generate inconsistent or even negative results. It is therefore advisable to stick to the main arguments "y", "m" and "d".
DATEDIF’s limitations
-
Not documented: Excel offers no context-sensitive help for this function.
-
Error-prone: some unfamiliar arguments return incorrect results.
-
Less flexible than modern formulas.
This is why, for more precise and intuitive calculations, it may be worth turning to FRACTION.YEAR.
FRACTION.YEAR: the modern alternative to DATEDIF
Unlike DATEDIF, the FRACTION.YEAR function is highlighted in Excel and benefits from built-in help. It calculates the fraction of a year between two dates, with or without decimals.
Examples of use :
-
Number of years with decimals :
-
Number of years rounded :
-
Number of months :
-
Number of days :
To discover another essential formula, take a look at our tutorial on the SEARCHV function, simply explained.
DATEDIF vs FRACTION.YEAR comparison
| Function | Strong points | Weak points |
|---|---|---|
| DATEDIF | Simple, backward compatible, fast | Not documented, errors possible |
| FRACTION.YEAR | More precise, decimals possible, guided by Excel | More complex to write |
Conclusion
The DATEDIF formula remains a fast and efficient solution for calculating date differences in Excel. Its main advantage lies in its simplicity and compatibility with older files.
However, for more precise calculations, FRACTION.YEAR is the recommended option, especially when you want to obtain detailed results with or without rounding.
Keep building your Excel toolkit with these related guides: