Date & Time

DATEDIF Function in Excel

Calculates the number of days, months, or years between two dates.

Syntax

  • =DATEDIF(start_date, end_date, unit)

Arguments

  • start_date (required): The start date
  • end_date (required): The end date
  • unit (required): "Y"=years, "M"=months, "D"=days, "MD"=days ignoring months/years

Examples

  • =DATEDIF(A1, B1, "Y") - Years between two dates - Result: 5
  • =DATEDIF("1/1/2020", TODAY(), "M") - Months since Jan 1, 2020 - Result: 72

DATEDIF best practices

  • Use DATEDIF for age, tenure, and duration calculations where complete years, months, or days matter.
  • Make sure the start date is before the end date to avoid #NUM! errors.

Excel DATEDIF function documentation

  • DATEDIF calculates the difference between two dates in days, months, or years based on unit code.
  • Syntax: =DATEDIF(start_date, end_date, unit). Legacy function — not in Insert Function dialog but still supported.
  • Common units: "Y" years, "M" months, "D" days, "YM" months ignoring years, "MD" days ignoring months/years.
  • Related: [TODAY](/functions/today/), [DATE](/functions/date/), [NETWORKDAYS](/functions/networkdays/).

DATEDIF unit codes explained

  • "Y" — complete years between dates (age in years).
  • "M" — complete months between dates.
  • "D" — total days between dates (same as end-start for simple day count).
  • "YM" — months after subtracting whole years (birthday month offset).
  • "MD" — days after subtracting whole months — can behave oddly at month-end; test edge cases.
  • start_date must be ≤ end_date or DATEDIF returns #NUM!.

Step-by-step: calculate age in years and months

  • Step 1 — Birth date in A2.
  • Step 2 — Years: =DATEDIF(A2, TODAY(), "Y").
  • Step 3 — Extra months: =DATEDIF(A2, TODAY(), "YM").
  • Step 4 — Display: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months".
  • Step 5 — Verify against known birthdates including leap years.

DATEDIF vs simple subtraction vs YEARFRAC

  • end_date - start_date — total days; does not give years/months directly.
  • DATEDIF — structured units for HR age and tenure reporting.
  • YEARFRAC — fractional years for finance (bond accrual); different purpose.
  • Hidden from wizard — type name manually; document in model for auditors.

Worked examples to copy

  • Tenure years: =DATEDIF(C2, TODAY(), "Y").
  • Days open ticket: =DATEDIF(A2, TODAY(), "D").
  • Months in role: =DATEDIF(start, end, "M").
  • Contract remainder months: =DATEDIF(TODAY(), end_date, "M").

People also ask

  • Why DATEDIF not in function list? — Legacy compatibility function; still works when typed.
  • DATEDIF #NUM!? — start_date later than end_date.
  • DATEDIF for age? — =DATEDIF(birth, TODAY(), "Y") is standard pattern.
  • DATEDIF vs DAYS? — DAYS returns day count only; DATEDIF supports Y/M/D units.

Common errors

  • #NUM! if start_date > end_date
  • Not in function wizard (legacy function)

Use cases

  • Age calculations
  • Tenure tracking
  • Contract durations

Frequently asked questions

  • What is the DATEDIF function in Excel? DATEDIF calculates the difference between two dates in years, months, or days. The syntax is =DATEDIF(start_date, end_date, unit). Units include: "Y" for years, "M" for months, "D" for days, "YM" for months ignoring years, "MD" for days ignoring months. It's a legacy function not shown in Excel's function wizard but still works.
  • How do I calculate exact age with DATEDIF? For age in years: =DATEDIF(birthdate, TODAY(), "Y"). For complete age: =DATEDIF(A1, TODAY(), "Y")&" years, "&DATEDIF(A1, TODAY(), "YM")&" months, "&DATEDIF(A1, TODAY(), "MD")&" days". This gives exact age like "25 years, 3 months, 15 days". DATEDIF handles leap years correctly.
  • Why is DATEDIF not showing in Excel's function list? DATEDIF is a legacy function from Lotus 1-2-3 that Microsoft kept for compatibility but never officially documented. It doesn't appear in the function wizard or autocomplete, but it works perfectly. You must type it manually. Despite being "hidden," it's reliable and widely used for date calculations.
  • What do the DATEDIF unit codes mean? DATEDIF units: "Y" = complete years; "M" = complete months; "D" = total days; "YM" = months remaining after subtracting years; "MD" = days remaining after subtracting months; "YD" = days remaining after subtracting years. Use "Y", "YM", and "MD" together for full age/duration breakdowns.
  • Why is DATEDIF returning #NUM! error? DATEDIF returns #NUM! when start_date is after end_date (negative duration). Unlike other date functions, DATEDIF requires start_date < end_date. Solutions: 1) Swap the dates if needed; 2) Use ABS() with regular date subtraction; 3) Use IF to check date order first: =IF(A1<B1, DATEDIF(A1,B1,"D"), DATEDIF(B1,A1,"D")).

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use DATEDIF

  • Age calculations — common Date & Time scenario for DATEDIF.
  • Tenure tracking — common Date & Time scenario for DATEDIF.
  • Contract durations — common Date & Time scenario for DATEDIF.

DATEDIF in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • DATEDIF syntax: =DATEDIF(start_date, end_date, unit)
  • start_date (required): The start date
  • end_date (required): The end date
  • unit (required): "Y"=years, "M"=months, "D"=days, "MD"=days ignoring months/years
  • Confirm DATEDIF arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm DATEDIF arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • DAYS (/functions/days/): Returns the number of days between two dates.
  • NETWORKDAYS (/functions/networkdays/): Returns the number of whole working days between two dates, excluding weekends and holidays.
  • YEARFRAC (/functions/yearfrac/): Returns the fraction of the year represented by the number of days between two dates.
  • EDATE (/functions/edate/): Returns a date that is a specified number of months before or after a given date.

Errors to watch for

  • #NUM! if start_date > end_date — review causes on linked error pages in the directory.
  • Not in function wizard (legacy function) — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter DATEDIF on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

DATEDIF worked examples to copy

  • =DATEDIF(A1, B1, "Y") — Years between two dates. Expected result: 5.
  • =DATEDIF("1/1/2020", TODAY(), "M") — Months since Jan 1, 2020. Expected result: 72.

DATEDIF reference summary for crawlers and offline review

  • DATEDIF belongs to the Date & Time category in Excel. Calculates the number of days, months, or years between two dates.
  • Full syntax: =DATEDIF(start_date, end_date, unit). Open /functions/datedif/ for parameters, FAQs, and related pages.
  • Common mistakes: #NUM! if start_date > end_date; Not in function wizard (legacy function)
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.