DATEDIF gives the gap between two dates, counted in whole years, months or days depending on the unit you ask for.
Syntax
DATEDIF(start_date, end_date, unit)
When to use it
Age from a date of birth, years of service from a start date, days remaining until a deadline.
What trips people up
DATEDIF is a remaining from an older spreadsheet and Excel will not offer it as you type. It still works in every version; you simply have to type the whole name yourself. The earlier date goes first, or the answer comes back as an error.
It is a leftover kept for compatibility with Lotus 1-2-3, so Excel does not suggest it as you type. It still works when typed out in full.
Which units can DATEDIF use?
"Y" for whole years, "M" for whole months, "D" for days, "YM" for months after the whole years, "MD" for days after the whole months, and "YD" for days ignoring years. Microsoft warns that "MD" can give wrong results.
Why does DATEDIF return #NUM!?
The start date is later than the end date. DATEDIF only counts forwards, so swap the two.
Related functions
Date subtraction — Excel stores every date as a number, so subtracting one date from another gives the number of days between them.
EDATE — EDATE moves a date forward or back by a whole number of months, and lands on a real date every time.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
