DATEDIF

How DATEDIF works

DATEDIF takes a start date, an end date, and a unit code in quotes. The unit decides what kind of difference to return: "Y" for completed years, "M" for completed months, "D" for days, plus three compound codes ("YM", "MD", "YD") that strip out the bigger units so you can build a "6 years, 11 months, 6 days" style readout.

Excel does NOT autocomplete the function name as you type. It still works, but you'll see no tooltip and no argument hints. You have to remember the spelling (DATEDIF, no second E) and the unit codes by hand. That undocumented status is why so many analysts have never heard of it - and why it's a quiet superpower once you know.

start_date must be earlier than or equal to end_date. If you flip them, DATEDIF returns #NUM! - it doesn't compute negative intervals. The dates can be cell references, date literals via DATE(), or strings like "2025-01-15" (locale-permitting).

= DATEDIF(start_date, end_date, unit)

Arguments

DATEDIF "Y": complete years of tenure

The "Y" unit returns the number of full years between two dates. Partial years don't count - if an employee's anniversary hasn't happened yet in the current year, they're still last year's tenure number.

Standard HR pattern: hire date in one column, today's date or termination date in another, DATEDIF gives years of service for performance reviews, vesting calculations, or seniority lists.

=DATEDIF(A2, B2, "Y")

• •A2 -> hire date - 2018-03-15
• •B2 -> today (or any end date) - 2026-05-11
• •"Y" -> complete years only - the unit code is always in quotes
• •Result 8 -> Sarah passed her 8th anniversary on 2026-03-15 but not her 9th
• •Anniversary not yet -> If end_date were 2026-03-14 instead, the result would be 7 - one day short of the anniversary still counts as the prior year

DATEDIF "M": complete months elapsed

The "M" unit counts full months between dates. Useful for project duration, subscription length, or any interval where days are too granular and years are too coarse.

Like Y, partial months don't count. A project that started on Jan 15 and ends on Feb 14 returns zero - the second 15th hasn't arrived yet.

=DATEDIF(A2, B2, "M")

• •A2 -> project start date
• •B2 -> project end date (or today for in-progress projects)
• •"M" -> unit code - complete months only
• •Anniversary day matters -> the result only advances when the day-of-month is reached or passed

DATEDIF "D": total days between dates

The "D" unit returns the raw day count between two dates. Same as plain subtraction (B2 - A2) but the function makes the intent explicit and handles oddities like text-formatted dates more gracefully.

Common pattern: account age in days, days-since-last-login, days-until-deadline.

=DATEDIF(A2, B2, "D")

• •A2 -> earlier date
• •B2 -> later date
• •"D" -> total days, no week/month/year boundaries
• •Same as B2 - A2 -> but DATEDIF accepts more date formats than raw subtraction

Compound output: "6 years, 11 months, 6 days"

For tenure reports and age displays, a single unit isn't enough - you want a human-readable breakdown. Stack three DATEDIF calls with the "Y", "YM", and "MD" units and concatenate the results.

"Y" gives whole years. "YM" gives the months that remain AFTER those years. "MD" gives the days that remain AFTER those months. Together they cover the full interval without double-counting.

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

• •DATEDIF(A2,B2,"Y") -> 6 - complete years between Jun 5, 2019 and May 11, 2026
• •DATEDIF(A2,B2,"YM") -> 11 - months remaining after those 6 years (Jun 2025 to May 2026)
• •DATEDIF(A2,B2,"MD") -> 6 - days remaining after those 11 months (May 5 to May 11)
• •& operator -> concatenates the numbers and labels into one readable string
• •Result -> "6 years, 11 months, 6 days" - exact interval, no rounding

Age calculator: DATEDIF + TODAY() for live ages

Combine DATEDIF with TODAY() and the age recalculates every time the workbook opens. A directory built this way is correct on every birthday without anyone editing the cells.

TODAY() is volatile - it updates on file open and on any recalc. The age in column C reflects today's date automatically.

=DATEDIF(B2, TODAY(), "Y")

• •B2 -> the person's date of birth
• •TODAY() -> today's date - no arguments, recalculates on open
• •"Y" -> complete years lived = current age
• •Birthday logic -> the year only increments on the day-of-month - someone born Aug 14 is still last year's age until Aug 14 comes around

Where people go wrong

1. 01
   Typing the unit without quotes or as a word

   Writing DATEDIF(A2, B2, Y) without quotes returns #NAME? because Excel interprets Y as a missing named range. Writing "Year" or "Years" returns #NUM! because Excel only accepts the single-letter codes.

   Fix: Always wrap the unit in quotes: "Y", "M", "D", "YM", "MD", "YD". Six valid codes, no synonyms, no fuzzy matching.
2. 02
   Flipping start_date and end_date

   DATEDIF returns #NUM! if start_date > end_date. It does NOT return a negative number like raw subtraction would. People often hit this when comparing termination vs. hire date and pass them in the wrong order.

   Fix: Always pass the earlier date first. If the order might flip, wrap with IF: =IF(A2 > B2, DATEDIF(B2, A2, "D"), DATEDIF(A2, B2, "D")).
3. 03
   Using the "MD" unit blindly

   "MD" has documented edge-case bugs in some Excel versions - it can return a negative number or skip a day around month boundaries. For "6 years, 11 months, 6 days" displays it usually works, but for production data validation it's unreliable.

   Fix: Build the compound display with "Y" and "YM" plus a derived day count from the modulo: "MD" is fine for human-readable HR reports, but never trust it for compliance or contractual calculations - cross-check with a direct subtraction.
4. 04
   Forgetting DATEDIF is hidden from autocomplete

   Excel does not suggest DATEDIF as you type. People searching the function library don't find it. Junior analysts often "discover" tenure formulas by writing complicated SUM(...)/365 hacks instead.

   Fix: Just type it. The function exists in every version since Excel 2000. The lack of autocomplete is a UI quirk, not a "deprecated" warning - DATEDIF is fully supported.

Notes

• DATEDIF is hidden from Excel's autocomplete and function-library UI, but it ships with every version since the 90s and is fully supported.
• Unit codes are always in quotes: "Y", "M", "D", "YM", "MD", "YD". Other values return #NUM!.
• start_date must be ≤ end_date. Flipping them returns #NUM!, not a negative number.
• TODAY() recalculates on workbook open and on any sheet recalc - perfect for live age and tenure displays.
• For compound output ("X years, Y months, Z days"), use Y + YM + MD and concatenate with & operators.
• The "MD" unit has documented edge-case bugs - reliable enough for HR reports, unreliable for legal or compliance calculations.
• Days result is the same as raw subtraction (B2 - A2) - DATEDIF is just more readable and tolerant of date formats.
• Wrap with IFERROR for production reports - a single bad date in the source data turns the whole report into a column of red errors otherwise.

What to learn next

Now prove it