DATEDIF: Hidden Date Calculator (2025)
Quick Answer: DATEDIF calculates date differences: =DATEDIF(start, end, 'Y') for years, 'M' for months, 'D' for days. Age: =DATEDIF(birthdate, TODAY(), 'Y'). Full age: Y years, YM months, MD days. YM = months ignoring years. MD = days ignoring months. Hidden function - must type manually, not in list.
What is DATEDIF?
DATEDIF is Excel's hidden function that calculates the difference between two dates in years, months, or days. Despite being undocumented, it's reliable and widely used for age calculations, employment duration, and date analysis.
🔒 Hidden Function
Not in Formula Builder: DATEDIF doesn't appear in Excel's function list or autocomplete. You must type it manually.
Why hidden: Included for Lotus 1-2-3 compatibility. Microsoft doesn't officially document it but it works in all Excel versions.
Alternative: YEARFRAC for decimal years, DAYS for day differences, or date arithmetic (end-start).
Quick Age Example:
Birthdate: January 15, 1990
Today: December 3, 2025
=DATEDIF("1/15/1990", TODAY(), "Y")Result: 35 years
Common Use Cases:
✓ Age Calculations
Calculate exact age in years, months, days
✓ Employment Duration
Years of service, tenure analysis
✓ Project Timelines
Days, months, years between milestones
✓ Warranty/Contract Periods
Time remaining, expiration tracking
Six Units Explained:
"Y" - Years
Complete years between dates
"M" - Months
Complete months between dates
"D" - Days
Total days between dates
"YM" - Months ignoring years
Months portion only (used with Y for full age)
"MD" - Days ignoring months
Days portion only (used with Y and YM)
"YD" - Days ignoring years
Days as if dates in same year
💡 Pro Tip
Full age breakdown: Combine Y, YM, and MD units to get complete age: "35 years, 10 months, 18 days"
Must type manually: DATEDIF won't appear in autocomplete - memorize the syntax!
DATEDIF Syntax
=DATEDIF(start_date, end_date, unit)
Arguments
start_date (required)
Beginning date
end_date (required)
Ending date (must be after start_date)
unit (required)
Text code for unit type (must be in quotes)
"Y" - Complete years
"M" - Complete months
"D" - Days
"YM" - Months excluding years
"MD" - Days excluding months
"YD" - Days excluding years
Unit Examples
Start: 1/15/1990, End: 12/3/2025
=DATEDIF(start, end, "Y")
Result: 35 (complete years)
=DATEDIF(start, end, "M")
Result: 430 (total months)
=DATEDIF(start, end, "D")
Result: 13,107 (total days)
=DATEDIF(start, end, "YM")
Result: 10 (months after removing 35 years)
=DATEDIF(start, end, "MD")
Result: 18 (days after removing years and months)
=DATEDIF(start, end, "YD")
Result: 322 (days as if in same year)
Building Full Age String
Complete Age Formula:
=DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days"
Result: "35 years, 10 months, 18 days"
Explanation:
- • Y gives complete years (35)
- • YM gives remaining months after years (10)
- • MD gives remaining days after months (18)
- • Concatenate with & operator for readable output
⚠️ Important Notes
- • Hidden function: Not in Excel's function list - must type manually
- • Start before end: start_date must be earlier than end_date
- • Quotes required: Unit must be in quotes ("Y", not Y)
- • Case insensitive: "Y", "y", "YM", "ym" all work
- • #NUM! error: If start_date > end_date
- • MD unit caution: May give unexpected results with month-end dates
- • Works in all versions: Despite being undocumented
- • TODAY() updates: Age formulas recalculate daily
10+ DATEDIF Examples
Age Calculations
1. Simple Age in Years
=DATEDIF(A2, TODAY(), "Y")
A2 = birthdate, returns complete years old
2. Complete Age (Years, Months, Days)
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"
Full age: "35 years, 10 months, 18 days"
3. Age in Months
=DATEDIF(A2, TODAY(), "M")
Total months old (useful for infant age)
Employment Duration
4. Years of Service
=DATEDIF(A2, TODAY(), "Y")
A2 = hire date, calculates tenure
5. Employment Duration Full
=DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months"
"12 years 5 months" of service
Project & Timeline Calculations
6. Project Duration in Days
=DATEDIF(A2, B2, "D")
A2 = start date, B2 = end date
7. Contract Length in Months
=DATEDIF(A2, B2, "M")
Complete months between dates
8. Time Until Deadline
=DATEDIF(TODAY(), B2, "D")&" days remaining"
Days until deadline in B2
Special Use Cases
9. Warranty Time Remaining
=IF(TODAY()>B2,"Expired",DATEDIF(TODAY(),B2,"M")&" months left")
B2 = warranty end date
10. Birthday Countdown
=DATEDIF(TODAY(), DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)), "D")
Days until next birthday
11. Conditional Age Format
=IF(DATEDIF(A2,TODAY(),"Y")<1,DATEDIF(A2,TODAY(),"M")&" months",DATEDIF(A2,TODAY(),"Y")&" years")
Shows months if under 1 year, years otherwise
🎯 Pro Tips
- • TODAY() auto-updates: Age formulas recalculate daily automatically
- • Combine units: Y + YM + MD for complete duration breakdown
- • Error handling: Use IF to check end > start before DATEDIF
- • Text formatting: Concatenate with & for readable output
- • Must type manually: DATEDIF won't appear in autocomplete
- • MD caution: May have issues with month-end dates
- • Alternative: YEARFRAC for decimal years, DAYS for day count
Frequently Asked Questions
Why is DATEDIF hidden and not in the function list?
DATEDIF was included for Lotus 1-2-3 compatibility when Excel was competing with Lotus. Microsoft chose not to officially document it but kept it working for backward compatibility. It doesn't appear in Excel's function list, autocomplete, or help files. Despite being 'hidden', it works reliably in all Excel versions (Windows, Mac, Online). Alternative: Use YEARFRAC for years (decimal), DAYS for day count, or date arithmetic (end-start).
What's the difference between DATEDIF 'M' and 'YM' units?
'M' returns TOTAL months between dates. 'YM' returns months AFTER removing years. Example: 1/15/1990 to 12/3/2025. DATEDIF(...,'M') = 430 total months. DATEDIF(...,'YM') = 10 months (after removing 35 years). Use Y for years, YM for remaining months, MD for remaining days to build complete duration: '35 years, 10 months, 18 days'.
Why does DATEDIF with 'MD' give strange results?
MD unit has known issues with month-end dates. Example: 1/31 to 3/1 may give unexpected day count because months have different lengths. Microsoft acknowledges this bug but won't fix it (undocumented function). Solutions: (1) Use DAYS function instead for accurate day count, (2) Calculate manually with DAY function, (3) Accept MD quirks for most use cases. For critical calculations, avoid MD or verify results.
Can I use DATEDIF with future dates?
Yes, end_date can be future. Example: =DATEDIF(TODAY(), '12/31/2025', 'D') returns days until year end. Useful for: countdown timers, deadline tracking, warranty expiration, contract end dates. Important: start_date must be before end_date or #NUM! error. Use IF to validate: =IF(A2>B2,'Invalid',DATEDIF(A2,B2,'D')).
How do I calculate age as of a specific date (not today)?
Replace TODAY() with specific date: =DATEDIF(birthdate, DATE(2025,1,1), 'Y') for age on Jan 1, 2025. Or use cell reference: =DATEDIF(A2, B2, 'Y') where B2 is the 'as of' date. Useful for: historical age calculations, age on specific date, retrospective reporting. Can calculate age at hire, age when event occurred, etc.
What are alternatives to DATEDIF?
YEARFRAC: =YEARFRAC(start,end) for decimal years (5.75 years). DAYS: =DAYS(end,start) for day count. Date arithmetic: =end-start for days. YEAR/MONTH/DAY: Extract components separately. Excel 365: No direct replacement for Y/YM/MD breakdown. DATEDIF remains most convenient for age calculations despite being hidden. Consider using for simplicity vs. officially supported functions.