| 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. In the Russian version of Excel, the function exists but may not appear in the function wizard – enter it manually.
Function syntax
=DATEDIF(start_date, end_date, "interval")- 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."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”.
Formatting
If dates are formatted as text, pre-convert them using DATEVALUE or ensure Excel recognizes them as dates.
Useful 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.
This post is also available in RU.