When you need to calculate the difference between two dates in Excel, most users reach for simple subtraction or the DAYS function. But what if you want the difference in years, months, or days – or even a combination like “5 years, 2 months, 3 days”?
That’s where the DATEDIF function shines. Unfortunately, it’s a hidden gem – it doesn’t appear in Excel’s function list or tooltips, yet it’s one of the most powerful date functions available.
In this complete guide, you’ll learn:
- What DATEDIF is and why it’s “hidden”
- Syntax and all available units (
"Y","M","D","MD","YM","YD") - Step‑by‑step examples (age calculation, service tenure, project duration)
- A real‑world case study: Employee service award calculator
- Common errors and how to fix them
- Alternatives when DATEDIF fails (post‑1900 dates, leap years)
- Advanced real‑world use cases
By the end, you’ll master date differences and never struggle with age or interval calculations again.
1. What Is the DATEDIF Function?
DATEDIF stands for Date Difference. It calculates the number of days, months, or years between two dates. It can also compute partial differences (e.g., months ignoring years, days ignoring months).
Why is it “hidden”?
DATEDIF originated from Lotus 1‑2‑3. Microsoft kept it in Excel for backward compatibility but never documented it in the Insert Function dialog or Formula AutoComplete. However, it works perfectly in all Excel versions (2000 to 2026).
⚠️ Note: In Excel 2016 and later, Microsoft officially documented DATEDIF, but you still won’t see it in the function list. You must type it manually.
2. Syntax & Arguments
=DATEDIF(start_date, end_date, unit)| Argument | Description |
|---|---|
start_date | The beginning date. |
end_date | The ending date (must be later than start_date, unless you want negative results – but DATEDIF doesn’t handle negatives well). |
unit | A text string in double quotes that specifies the type of difference. See table below. |
Available Units (Case‑sensitive – use uppercase)
| Unit | Returns |
|---|---|
"Y" | Complete years between the dates. |
"M" | Complete months between the dates. |
"D" | Days between the dates. |
"MD" | Days difference, ignoring months and years (only day part). |
"YM" | Months difference, ignoring years (only month part). |
"YD" | Days difference, ignoring years (treats dates as same year). |
3. How to Use DATEDIF (Basic Examples)
Example 1: Age in Years
Calculate a person’s age as of today.
| A | B |
|---|---|
| Birthdate | Formula |
| 15‑Mar‑1990 | =DATEDIF(A2, TODAY(), "Y") |
If today is 28‑Apr‑2026, the result is 36 (complete years).
Better to use a fixed end date:=DATEDIF(A2, DATE(2026,4,28), "Y")
Example 2: Total Months Between Dates
Project start: 1‑Jan‑2025 (A1), end: 1‑Dec‑2026 (B1)=DATEDIF(A1, B1, "M") → 23 months.
Example 3: Exact Days
=DATEDIF(A1, B1, "D") → days difference (same as B1-A1).
4. Real‑World Case Study: Employee Service Award Calculator
Scenario:
A company wants to generate service award letters for employees. For each employee, they need:
- Total years of service (for eligibility – 5, 10, 15 years)
- Exact years, months, and days since hire date (for personalized letters)
- Next anniversary date (to schedule awards)
Data setup (columns A–F):
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Employee | Hire Date | As of Date (Today) | Total Years | Tenure Text (Y, M, D) | Next Anniversary |
| John Smith | 15-Mar-2019 | 28-Apr-2026 | =DATEDIF(B2,C2,"Y") | formula below | formula below |
| Jane Doe | 10-Nov-2014 | 28-Apr-2026 | =DATEDIF(B3,C3,"Y") | … | … |
Step‑by‑step formulas:
4.1 Total Years (Column D)
=DATEDIF(B2, C2, "Y")
→ For John: 7 years (2019 to 2026, but March to April gives 7 full years).
4.2 Tenure Text – “X years, Y months, Z days” (Column E)
=DATEDIF(B2, C2, "Y") & " years, " & DATEDIF(B2, C2, "YM") & " months, " & DATEDIF(B2, C2, "MD") & " days"Result for John Smith: 7 years, 1 month, 13 days
(Because from 15-Mar-2019 to 15-Mar-2026 = 7 years exactly; then from 15-Mar-2026 to 28-Apr-2026 = 1 month, 13 days.)
4.3 Next Anniversary Date (Column F)
We want the next 5‑year, 10‑year, etc., award date, but more commonly the next calendar anniversary of the hire date.
=DATE(YEAR(C2), MONTH(B2), DAY(B2))But if that date has already passed this year, add 1 year:
=IF(DATE(YEAR(C2), MONTH(B2), DAY(B2)) >= C2,
DATE(YEAR(C2), MONTH(B2), DAY(B2)),
DATE(YEAR(C2)+1, MONTH(B2), DAY(B2)))For John: Hire date 15-Mar, as-of date 28-Apr-2026 → the next anniversary is 15-Mar-2027 (because 15-Mar-2026 has already passed).
4.4 Days Until Next Anniversary (optional Column G)
=F2 - C2 → For John: 15-Mar-2027 minus 28-Apr-2026 = 321 days.
Outcome: HR can now filter employees whose next anniversary is within 30 days and automatically generate award letters with precise tenure text.
5. Understanding the “Partial” Units ("MD", "YM", "YD")
These are where DATEDIF becomes truly special.
"MD" – Days ignoring months and years
Find the remaining days after whole months are subtracted.
Example:
Start: 15‑Jan‑2025 (A1), End: 10‑Mar‑2026 (B1)=DATEDIF(A1, B1, "MD") returns the day‑of‑month difference (10 – 15 = -5? Actually DATEDIF wraps to the previous month’s days. Known quirk – test on your data.)
"YM" – Months ignoring years
Difference in months, treating both dates as if they were in the same year.
Start: 15‑Mar‑2025, End: 10‑Jan‑2026 → "YM" = from Mar to Jan is 10 months.
"YD" – Days ignoring years
Days difference, treating both dates as if they were in the same year.
Start: 15‑Mar‑2025, End: 10‑Jan‑2026 → "YD" = from Mar 15 to Jan 10 of the same year? This wraps around and can be counterintuitive.
Best practice: For critical applications, test
"MD","YM","YD"thoroughly with your date ranges.
6. More Real‑World Examples (Short)
6.1 Calculate Age in Years, Months, Days
Already shown in the case study. Use the concatenated formula.
6.2 Employee Tenure (simple)
=DATEDIF(C2, D2, "Y") & " years, " & DATEDIF(C2, D2, "YM") & " months"
→ 6 years, 10 months
6.3 Days Until Next Birthday (ignoring year)
Harder – use the IF logic shown in the case study for anniversaries.
7. Common Errors & How to Fix Them
| Error / Problem | Cause | Solution |
|---|---|---|
#NUM! | end_date ≤ start_date | Ensure end_date is strictly later. Use IF(end < start, -DATEDIF(end, start, unit), DATEDIF(start, end, unit)) for absolute difference. |
#NAME? | Typo (e.g., DATEDIFF with two F’s) | Correct spelling: DATEDIF. Excel does not suggest it. |
#VALUE! | One of the dates is not a valid Excel date (text or out of range). | Use DATEVALUE or ensure cells contain real dates (numbers). |
Wrong result with "MD" | Known bug: for dates like Jan 31 to Feb 1, "MD" returns 1 instead of 0? | Use alternative: =DAY(end_date)-DAY(start_date) if you just want day‑of‑month difference. |
| Incorrect age (off by 1) | Leap year or end‑of‑month edge cases. | Use DATEDIF with "Y" for complete years. |
Negative days with "YD" | When end_date’s month/day is earlier in the year than start_date’s. | Avoid "YD" for critical calculations; use =B1-DATE(YEAR(B1), MONTH(A1), DAY(A1)) instead. |
8. DATEDIF vs. Other Date Difference Methods
| Method | Formula | Returns | Notes |
|---|---|---|---|
| Subtract dates | =B1-A1 | Days only | Simple, accurate. |
DAYS function | =DAYS(B1, A1) | Days only | Same as subtraction, but more explicit. |
NETWORKDAYS | =NETWORKDAYS(A1, B1) | Workdays | Excludes weekends & holidays. |
YEARFRAC | =YEARFRAC(A1, B1, 1) | Fraction of a year | Useful for interest calculations. |
| DATEDIF | =DATEDIF(A1, B1, "Y") | Whole years | Best for age/tenure. |
When to choose DATEDIF:
- You need whole years, whole months, or a combination (years + months + days).
- You want a human‑readable string like “5 years, 2 months”.
When to avoid DATEDIF:
- You need exact fractional years (use
YEARFRAC). - You need business days (use
NETWORKDAYS). - You’re dealing with negative date differences (use simple subtraction).
9. Advanced Tips & Best Practices
✅ Tip 1 – Handle Missing End Dates
If an end date is not yet entered (e.g., ongoing project), use IF to return "" or calculate as of today.
=IF(ISBLANK(B1), "", DATEDIF(A1, B1, "Y"))
✅ Tip 2 – Age as of a Specific Date
Don’t rely on TODAY() for static reports. Use a cell reference.
=DATEDIF(birthdate, calculation_date, "Y")
✅ Tip 3 – Combine with EDATE for Projections
Add months to a start date and then compare.
✅ Tip 4 – Avoid DATEDIF for Negative Intervals
Wrap it with IF to prevent #NUM!:
=IF(start <= end, DATEDIF(start, end, "Y"), -DATEDIF(end, start, "Y")) – but the negative sign works only for "Y", "M", "D"; not for partial units.
✅ Tip 5 – Use DATEDIF in Conditional Formatting
Highlight rows where tenure > 5 years:
=DATEDIF($C2, TODAY(), "Y") > 5
10. Known Limitations & Workarounds
| Limitation | Workaround |
|---|---|
| No negative differences | Use =IF(end<start, -DATEDIF(end,start,"D"), DATEDIF(start,end,"D")) for days only. |
"MD" bug with month‑end dates | Use =DAY(end)-DAY(start) then handle negatives: =MOD(DAY(end)-DAY(start), DAY(EOMONTH(start,0))) |
"YD" can return negative | Avoid; use =B1-DATE(YEAR(B1), MONTH(A1), DAY(A1)) |
| Not available in Excel for the web? | It actually works in Excel Online (tested). But not guaranteed for all cloud platforms. |
11. Real‑World Dashboard Example (Extended)
Scenario: HR wants a dynamic employee anniversary tracker.
Columns:
- A: Employee Name
- B: Hire Date
- C: Today’s date (or a freeze date)
- D: Tenure (Years)
=DATEDIF(B2, C2, "Y") - E: Tenure (Months)
=DATEDIF(B2, C2, "YM") - F: Next anniversary date
=DATE(YEAR(C2), MONTH(B2), DAY(B2))(if passed, add 1 year) - G: Days until next anniversary
=IF(F2>=C2, F2-C2, DATE(YEAR(C2)+1, MONTH(B2), DAY(B2))-C2)
Then use conditional formatting to highlight employees whose next anniversary is within 30 days.
12. Frequently Asked Questions (FAQ)
Q: Why doesn’t Excel show DATEDIF in the function list?
A: It’s a legacy function kept for compatibility. But you can still type it manually – it works.
Q: Does DATEDIF account for leap years?
A: Yes, for "Y" and "M" and "D" it correctly handles leap days. For "MD" and "YD", leap years can cause off‑by‑one errors – test carefully.
Q: Can I use DATEDIF with times?
A: No. DATEDIF works only with dates (integer values). Times are ignored.
Q: What’s the difference between "D" and "YD"?
A: "D" gives total absolute days. "YD" gives days difference ignoring the year – can produce negative or wrapped results.
Q: How do I calculate someone’s exact age in decimal years (e.g., 36.25)?
A: Use YEARFRAC(start, end, 1) (actual/actual basis).
Q: Does DATEDIF work with dates before 1900?
A: No. Excel’s date system starts on Jan 1, 1900 (or Jan 1, 1904 on Mac). For historical dates, use a different tool or adjust.
13. References
- Microsoft Support
DATEDIF function documentation
https://support.microsoft.com/excel - Microsoft Learn
Excel date and time functions overview
https://learn.microsoft.com
14. Conclusion
The DATEDIF function is one of Excel’s most useful yet under‑appreciated tools. Now you know:
- Its basic syntax:
=DATEDIF(start, end, unit) - Six units:
"Y","M","D","MD","YM","YD" - How to calculate age, tenure, project duration, and day‑month‑year components
- A complete real‑world case study (employee service awards)
- Common pitfalls and workarounds
Use DATEDIF whenever you need human‑readable date intervals – especially for HR, billing, membership, or personal planning. For critical financial or legal calculations, always verify with another method and document your logic.
Next steps: Practice by building an age calculator with a dynamic “next birthday” countdown. Combine DATEDIF with TODAY(), DATE, and conditional formatting to create a powerful dashboard.
This article applies to Excel 2010, 2013, 2016, 2019, 2021, 2026 (Microsoft 365) and Excel for the web. Function behavior may vary slightly across versions; always test edge cases.
