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: Dynamically calculate age
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months"Look at the example below and copy and paste the data below into the Excel file.
ID Birth Date (Column B) Age (Years & Months) (Column C) Formula Explanation
1 2010-05-15 15 years 5 months Calculates time from 2010-05-15 to today (2025-10-14)
2 2020-12-20 4 years 10 months Calculates time from 2020-12-20 to today (2025-10-14)
3 1995-03-08 30 years 7 months Calculates time from 1995-03-08 to today (2025-10-14)
4 2025-07-01 0 years 3 months Calculates time from 2025-07-01 to today (2025-10-14)
5 1988-10-14 37 years 0 months Calculates time from 1988-10-14 to today (2025-10-14) – Birthday of the current year
Case 2: Non-working Days
=DATEDIF(start_date,end_date,"D")-NETWORKDAYS(start_date,end_date,holidays)This Excel formula is used to calculate the number of non-working days between two dates (including weekends and specified holidays).
Formula principle:
- DATEDIF(start_date, end_date, "D"): Calculates the total number of days between two dates (including weekends and holidays).
- NETWORKDAYS(start_date, end_date, holidays): Calculates the number of working days between two dates (excluding weekends and specified holidays).
- Subtracting the two results gives the number of non-working days (weekends + holidays).
The following is a test table that calculates three indicators: total number of days (DATEDIF), number of working days (NETWORKDAYS), and number of non-working days (difference).
Project Name Start Date End Date Total Days (Formula 1) Working Days (Formula 2) Non-working Days (Formula 3)
Project A 2023-10-09 2023-10-15 =DATEDIF(B2,C2,"D") =NETWORKDAYS(B2,C2) =D2-E2
Project B 2023-12-20 2023-12-26 =DATEDIF(B3,C3,"D") =NETWORKDAYS(B3,C3) =D3-E3
Project C 2024-01-08 2024-01-14 =DATEDIF(B4,C4,"D") =NETWORKDAYS(B4,C4) =D4-E4
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)