Need to calculate someone's exact age in Excel? Or find out how many years and months an employee has been with your company? The DATEDIF function is Excel's dedicated tool for calculating the difference between two dates in years, months, or days — and it handles all the calendar complexity for you. Despite being one of Excel's most useful date functions, DATEDIF is hidden: it does not appear in autocomplete and has no formula wizard. This guide teaches you the syntax, all six unit codes, and six real-world examples covering age, tenure, project duration, and more.
#NUM! error.
DATEDIF Syntax
The function takes three arguments — a start date, an end date, and a unit code that controls what is counted:
| Argument | Required? | What it means |
|---|---|---|
| start_date | Required | The earlier date — a cell reference, a date in quotes, or a formula like DATE(1990,5,15). Must be earlier than end_date. |
| end_date | Required | The later date — usually a cell reference or TODAY() for calculations up to the present day. |
| unit | Required | A text code in double quotes that controls the type of interval returned. Six codes are available — see the table below. |
The Six Unit Codes
The unit argument is what makes DATEDIF powerful. The first three codes return total intervals; the last three return remaining intervals after a larger unit has been counted — useful for building combined "X years, Y months, Z days" strings.
| Unit | Returns | Typical use | Example result |
|---|---|---|---|
| "Y" | Complete years between the two dates | Age, tenure in years | DATEDIF("1-Jan-2020", "28-Mar-2026", "Y") = 6 |
| "M" | Complete months between the two dates | Subscription length, loan age | DATEDIF("1-Jan-2020", "28-Mar-2026", "M") = 74 |
| "D" | Total days between the two dates | Project duration, days overdue | DATEDIF("1-Jan-2020", "28-Mar-2026", "D") = 2278 |
| "YM" | Remaining months after complete years | Combined "Y years M months" strings | DATEDIF("1-Jan-2020", "28-Mar-2026", "YM") = 2 |
| "YD" | Remaining days after complete years | Days until next birthday/anniversary | DATEDIF("1-Jan-2020", "28-Mar-2026", "YD") = 87 |
| "MD" | Remaining days after complete months | Final days in a "Y yr M mo D days" string | DATEDIF("1-Jan-2020", "28-Mar-2026", "MD") = 27 |
Example 1: Calculate Age from Date of Birth
The most common use of DATEDIF is calculating a person's current age in complete years. Using TODAY() as the end date means the result updates automatically every day.
TODAY() with any date cell to calculate age as of that date — useful for eligibility checks, insurance reports, and historical age verification.
Example 2: Full Age in Years, Months, and Days
A single DATEDIF call returns one unit. Combine three calls — "Y", "YM", and "YD" — with the ampersand operator to build a full human-readable age string like "35 years, 10 months, 13 days".
Example 3: Calculate Employee Tenure
HR teams use DATEDIF to calculate service length for payroll banding, leave entitlement, and performance reviews. The formula works identically whether the employee is still active (use TODAY()) or has left (use their last day).
Example 4: Count Days Between Two Dates
For total days between two dates, you can simply subtract the start date from the end date — Excel stores dates as numbers, so subtraction gives days directly. DATEDIF with "D" does the same thing. Both approaches are shown here.
Ctrl+1, and choose Number format to see the day count correctly.
Example 5: Count Complete Months Between Dates
Use "M" to count total complete months — useful for subscription billing, loan tracking, and any scenario where partial months do not count. Combine with "YM" to show the remaining months after full years.
Example 6: Next Birthday and Anniversary Countdown
The "YD" unit returns days elapsed since the last anniversary of a date within the current year. Subtract this from 365 to get a countdown to the next birthday or work anniversary — useful for HR alerts and customer loyalty programmes.
DATE(YEAR+1, MONTH, DAY) formula shown above and subtract TODAY() from it.
Troubleshooting DATEDIF Errors
#NUM! error
The start_date is greater than the end_date. DATEDIF requires the earlier date first. Check that your date columns are not reversed, and add an IF guard if needed: =IF(A2>B2, "Check dates", DATEDIF(A2,B2,"Y")).
#VALUE! error
One of the date arguments is stored as text rather than a real Excel date. Press Ctrl+` to check — real dates show as 5-digit serial numbers. Convert text dates using DATEVALUE() or by using Data > Text to Columns.
DATEDIF does not appear in autocomplete
This is normal — DATEDIF is a hidden compatibility function. You must type the full formula manually. It will work correctly once entered even though Excel does not suggest it.
Result looks like a date instead of a number
Excel has auto-formatted the result cell as a date. Press Ctrl+1, select Number from the category list, and set decimal places to 0. The result will then display as a plain integer.
=B2-DATE(YEAR(B2),MONTH(B2),1) as a reliable alternative.
Frequently Asked Questions
-
What does DATEDIF do in Excel?DATEDIF calculates the difference between two dates in a unit you specify — complete years, complete months, or total days. It also supports three "remainder" units (YM, YD, MD) for building combined strings like "3 years, 4 months, 12 days". It is available in all Excel versions but does not appear in autocomplete.
-
Why is DATEDIF not showing in Excel autocomplete?DATEDIF is a hidden compatibility function inherited from Lotus 1-2-3. Microsoft intentionally omits it from the autocomplete list and function wizard. It works correctly when typed in full — simply type =DATEDIF( and fill in the three arguments manually.
-
How do I calculate age in Excel using DATEDIF?Use =DATEDIF(DOB, TODAY(), "Y") where DOB is the date of birth cell. The "Y" unit returns complete years only, correctly accounting for whether the birthday has passed this year. The result updates automatically each day.
-
What is the difference between "M" and "YM" in DATEDIF?"M" returns the total number of complete months between the two dates. "YM" returns only the remaining months after complete years have been counted. For example, between 1-Jan-2024 and 28-Mar-2026: "M" returns 26 (total months), while "YM" returns 2 (the 2 months left after 2 complete years).
-
Can DATEDIF calculate tenure automatically?Yes. Use =DATEDIF(hire_date, TODAY(), "Y") for years of service, or combine "Y" and "YM" with the ampersand operator to get a "X years, Y months" string. Using TODAY() means the tenure value updates every time the workbook opens.
-
Why should I avoid the "MD" unit in DATEDIF?Microsoft's own documentation acknowledges that the "MD" unit can produce incorrect results for certain date combinations, especially near the end of months. For the day portion of a combined date string, a safer approach is to subtract the start of the current month from the end date using =end_date - DATE(YEAR(end_date), MONTH(end_date), 1).