Excel DATEDIF Function: Difference Between Two Dates in Years, Months or Days
Returns the gap between two dates in whole years, months or days, depending on the unit you ask for.
DATEDIF answers the question subtraction cannot: how many whole months or years lie between two dates. Subtracting one date from another gives you days, which is fine for a deadline and useless for an age or a length of service. DATEDIF takes a third argument naming the unit and counts complete ones.
It is Excel's strangest function. It is inherited from Lotus 1-2-3, it does not appear in the formula autocomplete, it is missing from the function wizard, and Microsoft's own documentation warns about one of its unit codes. It nevertheless works in every version of Excel, and it is the standard answer for calculating age.
Because it is undocumented in the UI, you have to type the whole thing yourself — no dropdown will offer it. That is not a sign it is deprecated; it is a sign nobody has updated that part of Excel since 1995.
Syntax
=DATEDIF(start_date, end_date, "unit")Arguments
start_dateRequired- The earlier date. If it is later than end_date the function returns #NUM! rather than a negative number.
end_dateRequired- The later date.
unitRequired- A quoted code: "Y" whole years, "M" whole months, "D" days, "YM" months ignoring years, "MD" days ignoring months and years, "YD" days ignoring years.
The example data
Headers in row 1, data in A2:C5. Columns B and C hold real dates.
| A | B | C | |
|---|---|---|---|
| 1 | Project | Start | Due |
| 2 | Warehouse move | 2024-01-15 | 2024-04-30 |
| 3 | Site survey | 2024-02-01 | 2024-02-14 |
| 4 | Fit-out | 2024-03-11 | 2024-09-01 |
| 5 | Handover | 2024-08-19 | 2024-10-07 |
Worked examples
=DATEDIF(B2, C2, "D")Result: 106
Days between the start and due dates. The same answer as =C2-B2.
=DATEDIF(B2, C2, "M")Result: 3
Whole months only. From 15 January to 30 April is three complete months and a fortnight, and the fortnight is discarded.
=DATEDIF(B4, C4, "YM")Result: 5
Months ignoring whole years — the "and 5 months" part of a duration you are stating as years and months.
=DATEDIF(B2, TODAY(), "Y") & " years"Result: 1 years
The age pattern. Joining it to text needs care with singular and plural; wrap it in IF if the sheet will be read by anyone else.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: start_date is later than end_date. DATEDIF refuses to return a negative result.
How to fix it: Put the earlier date first, or sort it out with MIN and MAX: DATEDIF(MIN(B2,C2), MAX(B2,C2), "D").
#VALUE!
Why it happens: One of the dates is text that Excel has not recognised as a date, or the unit code is not quoted.
How to fix it: The unit must be in quotes: "M", not M. For text dates, convert with DATEVALUE first.
Excel does not suggest the function name
Why it happens: It is undocumented in the UI and absent from autocomplete.
How to fix it: That is expected. Type the whole name and arguments yourself; it will still calculate.
"MD" returns a strange number
Why it happens: Microsoft advises against MD because it can return a negative result at month boundaries.
How to fix it: Avoid "MD". If you need days-within-a-month, calculate it from a subtraction and EOMONTH instead.
Tips worth knowing
- Age in years: =DATEDIF(dob, TODAY(), "Y"). This is the reason most people ever meet the function.
- A full "X years, Y months" reads as =DATEDIF(a,b,"Y")&" years, "&DATEDIF(a,b,"YM")&" months".
- For working days rather than calendar days, NETWORKDAYS is the function you want, not DATEDIF.
- Wrap it in IFERROR when either date might be blank, since a blank start date reads as 1900 and gives an absurd answer rather than an error.
Frequently asked questions
Why does Excel not autocomplete DATEDIF?
It was inherited from Lotus 1-2-3 for compatibility and never added to the function library's UI. It is not deprecated and works in every version, but you have to type it in full because no dropdown will offer it.
How do I calculate someone's age in Excel?
=DATEDIF(birthdate, TODAY(), "Y") gives completed years, which is what "age" normally means. Note that TODAY() recalculates, so the cell updates on its own — which is right for an age and wrong for a record of an age on a particular date.
What is the difference between DATEDIF with "D" and just subtracting?
None — =DATEDIF(a, b, "D") and =b-a give the same number of days. DATEDIF earns its place for "Y" and "M", where subtraction cannot help you.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.