| Function category | Date and time functions |
| Volatility | Non-volatile |
| Similar functions | DAYS — difference in days between two dates NETWORKDAYS — difference in working days YEARFRAC — difference between dates where unit equals one year YEAR, MONTH, DAY — you can create your own calculation logic without DATEDIF |
What does the DATEDIF function do?
The DATEDIF function returns the difference between two dates in specified units: years, months, days, or their combinations. Useful for calculating age, work experience, project duration, "pure" months without years, etc. The function works in all versions of Excel, but it doesn’t appear in the function wizard or in formula AutoComplete, so type it manually.
DATEDIF function syntax
=DATEDIF(start_date, end_date, "interval")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start | End | Interval | DATEDIF |
| 2 | 03/15/2000 | 02/01/2025 | "Y" | 24=DATEDIF(A2,B2,C2) |
| 3 | 03/15/2000 | 02/01/2025 | "M" | 298=DATEDIF(A3,B3,C3) |
| 4 | 03/15/2000 | 02/01/2025 | "D" | 9089=DATEDIF(A4,B4,C4) |
| 5 | 03/15/2000 | 02/01/2025 | "YM" | 10=DATEDIF(A5,B5,C5) |
| 6 | 03/15/2000 | 02/01/2025 | "MD" | 17=DATEDIF(A6,B6,C6) |
| 7 | 03/15/2000 | 02/01/2025 | "YD" | 323=DATEDIF(A7,B7,C7) |
24 full years, 298 full months, 9089 days. "YM" and "MD" are the remainders: 10 months and 17 days. Microsoft warns that "MD" can give wrong results in some cases (for example, when the start day is the 31st); for exact day remainders, use a formula with DATE instead.
- start_date — earlier date
- end_date — later date
- "interval" — string code for measurement units (see below)
Intervals
- "Y" — complete years between dates
- "M" — complete months between dates
- "D" — days between dates
- "YM" — months without complete years
- "YD" — days without complete years
- "MD" — days without complete months and years
When to use
- Employee or customer age in years, months and days
- Work experience (complete years and months)
- Project duration in months or days
- How many complete months have passed since the start of the period (without years)
DATEDIF examples
1. Complete years between dates
=DATEDIF(A2, B2, "Y")If A2 = 03/15/2000, B2 = 02/01/2025 → result: 24
2. Complete months between dates
=DATEDIF(A2, B2, "M")3. Days between dates
=DATEDIF(A2, B2, "D")4. Age in "years + months" format
=DATEDIF(A2, B2, "Y") & " years " & DATEDIF(A2, B2, "YM") & " months"5. Age in "years, months, days" format
=DATEDIF(A2, B2, "Y") & " y. " & DATEDIF(A2, B2, "YM") & " m. " & DATEDIF(A2, B2, "MD") & " d."| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Start | End | Years | Months | Days | Years + months | Y-M-D |
| 2 | 06/12/1990 | 10/22/2026 | 36=DATEDIF(A2,B2,"Y") | 436=DATEDIF(A2,B2,"M") | 13281=DATEDIF(A2,B2,"D") | 36 years 4 months=DATEDIF(A2,B2,"Y")&" years "&DATEDIF(A2,B2,"YM")&" months" | 36 y. 4 m. 10 d.=DATEDIF(A2,B2,"Y")&" y. "&DATEDIF(A2,B2,"YM")&" m. "&DATEDIF(A2,B2,"MD")&" d." |
| 3 | 01/20/2024 | 03/10/2024 | 0=DATEDIF(A3,B3,"Y") | 1=DATEDIF(A3,B3,"M") | 50=DATEDIF(A3,B3,"D") | 0 years 1 months=DATEDIF(A3,B3,"Y")&" years "&DATEDIF(A3,B3,"YM")&" months" | 0 y. 1 m. 19 d.=DATEDIF(A3,B3,"Y")&" y. "&DATEDIF(A3,B3,"YM")&" m. "&DATEDIF(A3,B3,"MD")&" d." |
| 4 | 01/01/2026 | 01/01/2025 | #NUM!=DATEDIF(A4,B4,"Y") | #NUM!=DATEDIF(A4,B4,"M") | #NUM!=DATEDIF(A4,B4,"D") | #NUM!… | #NUM!… |
Row 2 is an age on 10/22/2026. Row 3: from Jan 20 to Mar 10 is only 1 full month — a month counts as full when the end day reaches the start day, and March 10 hasn’t reached the 20th. Row 4: the start is later than the end, so every interval returns #NUM!. Use TODAY() as the end date for the current age.
DATEDIF not working?
Unlike most Excel functions, DATEDIF doesn't appear in the autocomplete list when typing a formula and doesn't have a pop-up hint with argument descriptions. This can be misleading: it seems like the function isn't working, but in reality it's available and correctly performs calculations when entered properly.
If the start date is greater than the end date, the function returns a #NUM! error. The user might think the function isn't working, but the reason is the wrong order of dates.
Why is there no DATEDIF formula?
The DATEDIF function originally appeared in Lotus 1-2-3 - a popular spreadsheet from the 90s. Microsoft added it to Excel for backward compatibility but never officially promoted it as a full-fledged function. It was considered auxiliary, outdated, and intended for those transitioning from other systems.
Nevertheless, due to its wide popularity and usefulness (especially for calculating age, experience, etc.), it was left in Excel but hidden from the interface:
- doesn't appear in the function list;
- no argument hints;
- no F1 help;
- no description in the "Function Wizard".
DATEDIF: result and input format
If dates are formatted as text, pre-convert them using DATEVALUE or ensure Excel recognizes them as dates.
Useful DATEDIF formula combinations
- Complete months without years: "YM"
- Days ignoring years: "YD"
- Breakdown into years-months-days (see example above) - the most common case
Other date and time functions in Excel
TIMEVALUE, TIME, YEAR, DATE, DATEVALUE, EDATE, DAY, WEEKDAY, YEARFRAC, EOMONTH, MONTH, MINUTE, WEEKNUM, WORKDAY, TODAY, SECOND, NOW, HOUR, NETWORKDAYS
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.