Function Overview
The DATEDIF function (Date Difference) is Excel's best-kept secret for date calculations. Originally from Lotus 1-2-3, this undocumented function has been available since Excel 2007. It calculates complete intervals between two dates in years, months, or days.
Syntax Structure
=DATEDIF(start_date, end_date, "unit")Parameter Details
- start_date: Earlier date (required)
- end_date: Later date (required)
- "unit": Measurement unit (text string in quotes)
6 Unit Codes Explained
| Unit Code | Calculation | Example |
|---|---|---|
| "Y" | Complete years | 2020-01-01 to 2023-12-31 → 3 |
| "M" | Complete months | 2023-01-15 to 2023-03-14 → 1 |
| "D" | Total days | 2023-01-01 to 2023-01-31 → 30 |
| "MD" | Days excluding months/years | 2023-01-15 to 2023-03-10 → 25 |
| "YM" | Months excluding years | 2022-11-30 to 2023-02-28 → 3 |
| "YD" | Days excluding years | 2024-02-28 to 2025-03-01 → 31 |
Practical Use Cases
Case 1: Employee Tenure Calculation
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months"Where B2 contains hire date: returns "3 years 5 months" format
Case 2: Project Timeline Tracking
=DATEDIF(start_date,end_date,"D")-NETWORKDAYS(start_date,end_date,holidays)Calculates actual working days excluding weekends and holidays
Error Troubleshooting
- #NUM! Error: Occurs when end_date < start_date → Swap dates
- #VALUE! Error: Invalid date format → Use DATE(2023,12,31) format
- Unit Not Working: Missing quotes → Use "Y" not Y
Critical Considerations
- Leap Year Handling:
2020-02-29 to 2021-02-28 → Returns 1 year with "Y" unit - Month-End Quirk:
=DATEDIF("2023-01-31","2023-03-01","MD") returns 1 day (not 0) - Date Format Best Practice:
Always use DATE function for unambiguous calculations
Real-World Application
A logistics company improved their invoice processing by 35% using DATEDIF to calculate payment terms:
- 30/60/90-day payment cycles
- Equipment lease durations
- Customs clearance deadlines
When to Use Alternatives
While powerful, consider other methods for:
- Custom workweeks → WORKDAY.INTL
- Holiday-aware calculations → NETWORKDAYS
- Time zones → Online date calculators
Pro Tips
- Combine units: =DATEDIF(A1,B1,"Y")&"y "&DATEDIF(A1,B1,"YM")&"m"
- Validate dates: =IF(ISNUMBER(start_date), DATEDIF(...), "Invalid Date")
- Document hidden functions: Add comments explaining DATEDIF usage
Last verified: Excel 365 (August 2023 release)