DATEDIF()
Calculates the difference between two dates expressed in complete years, months, or days.
DATEDIF(start_date, end_date, unit)DATEDIF returns the number of complete years, months, or days between two dates, depending on the unit argument. It is the standard way to calculate someone's age or the number of complete years a contract has run.
Note the argument order: start_date comes first, end_date second — the reverse of DAYS. If start_date is after end_date the function returns a #NUM! error.
DATEDIF is an undocumented function — it does not appear in Excel's function list or autocomplete, but it works in all versions. It was inherited from Lotus 1-2-3 and retained for compatibility.
DATEDIF is undocumented, has no IntelliSense, and must be typed in full — it survives for compatibility and because nothing else returns whole elapsed months or years as cleanly. Its unit codes are the useful part: Y and M for complete years and months, YM for the months remaining after whole years, and MD for the days after whole months. MD is known to return wrong results in some month-end cases and is best avoided. For tenure or age calculations, the Y plus YM pair gives the familiar "3 years 7 months" with no edge cases.
Arguments
- start_date
- The earlier date.
- end_date
- The later date.
- unit
- "Y" for complete years, "M" for complete months, "D" for days. "YM" gives remaining months after whole years; "MD" gives remaining days after whole months.
Examples
=DATEDIF(A2, TODAY(), "Y")Returns a person's age in complete years when A2 is their date of birth.
=DATEDIF(A2, B2, "M")Returns the number of complete months between two dates.
=DATEDIF(A2, B2, "Y")&"y "&DATEDIF(A2, B2, "YM")&"m"YM gives the months remaining after whole years. DATEDIF is undocumented and has no IntelliSense — it must be typed in full.
Notes and gotchas
- DATEDIF is undocumented and hidden — Excel offers no autocomplete and no argument tooltip for it. It still works, and has since Lotus 1-2-3, but you have to type it from memory.
- The unit codes are text:
"Y"complete years,"M"complete months,"D"days,"YM"months ignoring years,"MD"days ignoring months,"YD"days ignoring years. "MD"is known to give wrong results in some month-end cases and should be avoided for anything that matters.- It errors if the start date is after the end date, rather than returning a negative — the opposite of plain subtraction.
- Its real use is age and tenure in complete years, where
(end-start)/365drifts because of leap years.
Common errors
| You see | Because | Fix |
|---|---|---|
| #NUM! | The start date is later than the end date. | Order the arguments, or wrap with MIN/MAX to normalise them. |
| #VALUE! | An invalid unit code, or text dates. | Unit codes must be quoted text and are case-insensitive but must be from the list above. |



