Eric Campbell 6 min read Excel

How to Use DATEDIF in Excel

DATEDIF is Excel's worst-kept secret. Despite being one of the most useful date functions available, Microsoft doesn't include it in the function autocomplete list or official documentation. Yet it's...

Key Insights

  • DATEDIF is Excel’s undocumented function for calculating precise date differences using six unit types (Y, M, D, MD, YM, YD), making it essential for age calculations and duration tracking despite being hidden from the function list.
  • The MD unit has known calculation bugs with certain date combinations—always validate results when using partial date units, especially for month-end dates.
  • For financial calculations requiring decimal precision, use YEARFRAC instead of DATEDIF; reserve DATEDIF for scenarios where you need whole units like “2 years, 3 months, 15 days.”

Introduction to DATEDIF Function

DATEDIF is Excel’s worst-kept secret. Despite being one of the most useful date functions available, Microsoft doesn’t include it in the function autocomplete list or official documentation. Yet it’s been working reliably since Excel 2000, and millions of spreadsheets depend on it daily.

The function calculates the difference between two dates in various units—years, months, or days. This makes it invaluable for HR departments calculating employee tenure, finance teams tracking contract durations, and anyone who needs to determine someone’s exact age. Unlike simple subtraction that gives you total days, DATEDIF understands calendar logic: it knows that January to March is 2 months, not 59-62 days depending on leap years.

The reason DATEDIF remains hidden is historical. Microsoft inherited it from Lotus 1-2-3 for compatibility but never fully adopted it into their official function library. Despite this orphan status, it’s not going anywhere—too many legacy spreadsheets would break.

DATEDIF Syntax and Parameters

The DATEDIF function follows a straightforward structure:

=DATEDIF(start_date, end_date, unit)

All three parameters are required:

  • start_date: The beginning date of the period you’re measuring
  • end_date: The ending date of the period
  • unit: A text string specifying what to calculate (must be in quotes)

The unit parameter accepts six different values, each returning different aspects of the date difference:

  • “Y”: Complete years between dates
  • “M”: Complete months between dates
  • “D”: Total days between dates
  • “MD”: Days remaining after subtracting complete months
  • “YM”: Months remaining after subtracting complete years
  • “YD”: Days remaining after subtracting complete years

Here’s a basic example using sample dates:

// If A1 contains 1/15/2020 and B1 contains 1/15/2024
=DATEDIF(A1, B1, "Y")  // Returns 4 (complete years)
=DATEDIF(A1, B1, "M")  // Returns 48 (complete months)
=DATEDIF(A1, B1, "D")  // Returns 1461 (total days, including leap year)

Calculating Years, Months, and Days

The three primary units—Y, M, and D—handle the most common date difference calculations.

Complete Years (“Y”) counts only full years that have elapsed. This is perfect for age calculations:

// Calculate age from birthdate in A2
=DATEDIF(A2, TODAY(), "Y")

// Example: If A2 is 3/15/1990 and today is 2/10/2024
// Returns 33 (because 34th birthday hasn't occurred yet)

Complete Months (“M”) counts total months between dates, regardless of years:

// Calculate project duration in months
// Start date in C2: 6/1/2023
// End date in D2: 11/15/2023
=DATEDIF(C2, D2, "M")  // Returns 5 complete months

Total Days (“D”) gives you the absolute day count, accounting for varying month lengths and leap years:

// Days until contract expiration
// Today's date: 1/15/2024
// Contract end in E2: 4/30/2024
=DATEDIF(TODAY(), E2, "D")  // Returns 106 days

The D unit is particularly useful because it handles all the calendar complexity for you—no need to worry about whether February has 28 or 29 days.

Working with Partial Date Units

The partial units—MD, YM, and YD—let you break down date differences into composite formats. These are crucial when you need to display something like “3 years, 2 months, and 14 days.”

“MD” (Month-Day) returns remaining days after removing complete months. Use this for the “days” portion of a multi-part duration:

// Days component of employment duration
=DATEDIF(hire_date, TODAY(), "MD")

“YM” (Year-Month) returns remaining months after removing complete years:

// Months component after accounting for years
=DATEDIF(hire_date, TODAY(), "YM")

“YD” (Year-Day) returns remaining days after removing complete years, useful for “days into current year” calculations.

Here’s a complete example that combines multiple DATEDIF calls to create a formatted duration string:

// Hire date in A2: 3/15/2019
// Calculate complete tenure breakdown
=DATEDIF(A2, TODAY(), "Y") & " years, " & 
 DATEDIF(A2, TODAY(), "YM") & " months, " & 
 DATEDIF(A2, TODAY(), "MD") & " days"

// If today is 1/28/2024, returns: "4 years, 10 months, 13 days"

Important caveat: The MD unit has a known bug. When the start day is greater than the end day (e.g., starting on the 31st and ending on the 15th of a later month), it can return incorrect results. Always validate MD calculations with test data.

Practical Applications and Real-World Examples

HR Dashboard - Employee Tenure Tracking:

// Column A: Employee Name
// Column B: Hire Date
// Column C: Years of Service
=DATEDIF(B2, TODAY(), "Y")

// Column D: Full Tenure Description
=DATEDIF(B2, TODAY(), "Y") & "y " & 
 DATEDIF(B2, TODAY(), "YM") & "m " & 
 DATEDIF(B2, TODAY(), "MD") & "d"

Age Verification for Forms:

// Check if person is 18 or older
// Birthdate in cell B5
=IF(DATEDIF(B5, TODAY(), "Y") >= 18, "Eligible", "Not Eligible")

Loan Term Calculator:

// Loan start date: A10
// Current date: TODAY()
// Loan term: 360 months (30 years)

// Months remaining on loan
=360 - DATEDIF(A10, TODAY(), "M")

// Percentage of loan term completed
=DATEDIF(A10, TODAY(), "M") / 360

Subscription Tracking with Renewal Alert:

// Subscription start: C15
// Subscription term: 12 months
// Days until renewal

=DATEDIF(TODAY(), DATE(YEAR(C15)+1, MONTH(C15), DAY(C15)), "D")

// Alert if renewal within 30 days
=IF(DATEDIF(TODAY(), DATE(YEAR(C15)+1, MONTH(C15), DAY(C15)), "D") <= 30, 
    "RENEWAL SOON", "Active")

Common Errors and Troubleshooting

#NUM! Error: This occurs when your start_date is later than your end_date. DATEDIF cannot calculate backward in time:

// This produces #NUM! error
=DATEDIF("1/15/2024", "1/15/2023", "Y")

// Fix by ensuring proper date order
=IF(A2 > B2, "Invalid date range", DATEDIF(A2, B2, "Y"))

#VALUE! Error: You’ll see this when Excel doesn’t recognize your dates as valid date values:

// Text that looks like a date causes #VALUE!
=DATEDIF("January 15, 2023", TODAY(), "Y")  // May fail

// Use proper date formatting or DATE function
=DATEDIF(DATE(2023, 1, 15), TODAY(), "Y")  // Works reliably

MD Calculation Quirks: As mentioned, MD has bugs with certain date combinations. Here’s a defensive approach:

// Safer MD calculation with validation
=IFERROR(
    DATEDIF(A2, B2, "MD"),
    DAY(B2) - DAY(A2) + IF(DAY(B2) < DAY(A2), DAY(EOMONTH(B2, 0)), 0)
)

Best Practice - Comprehensive Error Handling:

// Robust tenure calculation with error handling
=IFERROR(
    IF(A2 > TODAY(), 
        "Future date", 
        DATEDIF(A2, TODAY(), "Y") & " years, " & 
        DATEDIF(A2, TODAY(), "YM") & " months"
    ),
    "Invalid date"
)

Always validate your DATEDIF formulas with edge cases: month-end dates, leap years, and same-date inputs (which should return 0).

Alternatives and Modern Replacements

While DATEDIF excels at whole-unit calculations, other functions may be better suited for specific scenarios.

YEARFRAC for Decimal Years: When you need fractional years (common in financial calculations), YEARFRAC provides more precision:

// DATEDIF approach (whole years only)
=DATEDIF("1/1/2020", "7/1/2023", "Y")  // Returns 3

// YEARFRAC approach (decimal precision)
=YEARFRAC("1/1/2020", "7/1/2023")  // Returns 3.493150685

// Useful for prorated calculations
=salary * YEARFRAC(hire_date, TODAY())

DAYS for Simple Day Counting: For straightforward day differences without the complexity:

// DAYS function (Excel 2013+)
=DAYS(end_date, start_date)

// Equivalent to DATEDIF D unit
=DATEDIF(start_date, end_date, "D")

Side-by-Side Comparison:

// Scenario: Calculate partial year employment for bonus calculation
// Hire date: 4/15/2023
// Calculation date: 12/31/2023

// DATEDIF (whole months)
=DATEDIF("4/15/2023", "12/31/2023", "M") / 12  // Returns 0.667 (8/12)

// YEARFRAC (precise decimal)
=YEARFRAC("4/15/2023", "12/31/2023")  // Returns 0.711 (more accurate)

When to Use Each:

  • Use DATEDIF when you need whole units or composite displays (“2 years, 3 months”)
  • Use YEARFRAC for financial calculations, interest accrual, or prorated amounts
  • Use DAYS for simple countdown timers or when you only need total days
  • Use simple subtraction (end_date - start_date) when working with date serial numbers

DATEDIF remains the best choice for human-readable duration displays and scenarios where calendar logic matters more than decimal precision. Despite its undocumented status, it’s a powerful tool that deserves a permanent place in your Excel toolkit.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.