DATEDIF Function in Excel: Ultimate Guide

DATEDIF measures the interval between two dates. Unlike a simple subtraction (=B2-A2), which returns the raw day count, DATEDIF can return the interval expressed in complete years, complete months, or days — and it can return the remaining months or days after whole years have been removed. That last part is why it exists.

The function dates back to Lotus 1-2-3. Microsoft kept it in Excel for spreadsheet compatibility but never documented it in the user interface. That is why you cannot find it by typing =DATE and looking through the dropdown — you have to know the name and type it in full.

A simple age, drawn out

Take the formula =DATEDIF(A2, TODAY(), "Y"). Excel reads it in three steps: check the start date, check the end date, then count how many complete years sit between them.

All three arguments are required. There is no optional third argument, and there is no fourth argument for time.

=DATEDIF(start_date, end_date, unit)

The unit code is what makes DATEDIF flexible. Each code tells the function how to slice the interval between the two dates.

Worked example — all six at once

Start date 1990-05-15, end date 2025-03-10. Here is what every unit returns:

These are the patterns that appear in real workbooks. Each example includes the spreadsheet layout and the exact formula for each cell.

Example 1 — Age in years, months, and days

The classic use: calculate an age today from a birthdate, and format it as “X years, Y months, Z days”.

' Complete years
=DATEDIF(B2, TODAY(), "Y")

' Remaining months after whole years
=DATEDIF(B2, TODAY(), "YM")

' Remaining days (fragile — see note)
=DATEDIF(B2, TODAY(), "MD")

' All three, concatenated into one string
=DATEDIF(B2,TODAY(),"Y") & " years, "
& DATEDIF(B2,TODAY(),"YM") & " months, "
& DATEDIF(B2,TODAY(),"MD") & " days"

Example 2 — Employee tenure report

HR wants tenure broken into years and months, with a “milestone” flag for anyone past 5 years.

Example 3 — Project duration tracker

Project managers want to know how long each project ran, in total days and in months.

Example 4 — Warranty countdown

Retail wants to know how many days of warranty remain on each product sold.

Example 5 — Subscription billing cycles

SaaS finance teams count how many complete billing cycles have elapsed since a customer signed up.

DATEDIF is not the only way to measure a date interval. Four other functions cover overlapping ground, and one of them may be a better fit for your case.

' Total days — use DAYS instead of DATEDIF "D"
=DAYS(B2, A2)

' Total months — no equivalent, keep DATEDIF "M"
=DATEDIF(A2, B2, "M")

' Complete years — no equivalent, keep DATEDIF "Y"
=DATEDIF(A2, B2, "Y")

' Fractional years — use YEARFRAC for decimals
=YEARFRAC(A2, B2)

' Business days only — use NETWORKDAYS
=NETWORKDAYS(A2, B2)

Type your answer in the box and click Check. Answers are case-insensitive and ignore extra spaces.

1. Q1. What does =DATEDIF("1990-05-15","2025-03-10","Y") return?
   Check
2. Q2. What does =DATEDIF("1990-05-15","2025-03-10","M") return?
   Check
3. Q3. What does =DATEDIF("1990-05-15","2025-03-10","YM") return?
   Check
4. Q4. What error does =DATEDIF("2025-03-10","1990-05-15","Y") return? (Include the error symbol.)
   Check
5. Q5. Which unit code returns the number of days between two dates ignoring years? (Answer with the two-letter code.)
   Check

DATEDIF is easy to use badly. These habits keep your date formulas correct and readable.

• DATEDIF returns the interval between two dates in complete units.
• The syntax is =DATEDIF(start_date, end_date, unit) — all three arguments are required.
• Six units: "Y", "M", "D", "YM", "MD", "YD".
• It is a hidden function — no autocomplete, no tooltip, no wizard entry.
• Reversed dates return #NUM!; guard with IF.
• The "MD" unit is documented as unreliable — avoid it for anything important.
• Use DAYS instead of "D" for a plain total day count.
• Use YEARFRAC instead of DATEDIF when you need fractional years.
• Dates must be real date values, not text — otherwise #VALUE!.
• For live age reports, TODAY() as the end date recalculates on every open.
• Chain "Y", "YM", and "MD" to build an “X years, Y months, Z days” string.
• Wrap DATEDIF in IFERROR when working with imported or user-entered dates.

DATEDIF is one member of Excel’s date and logical family. The natural next steps are the functions that pair with it in real workbooks.

Start with the companion date functions: yearfrac-function, days-function, and networkdays-function.

Then connect them to the logical tools: if-function, iferror-function, and ifs-function.