DATEDIF Practice: 5 Problems on the Function Excel Refuses to Autocomplete

Five problems on DATEDIF — including the one unit Microsoft's own documentation warns about. Each group opens with the cause, then gives you a case to judge.

DATEDIF is unusual: it works in every current version of Excel, but the editor will not autocomplete it, will not show you its arguments, and Microsoft's documentation carries a warning about one of its units. That combination — widely used, barely supported — is what makes it worth practising rather than looking up.

Three things account for almost every DATEDIF problem. The unit codes split into three plain ones and three remainders, and mixing up "m" with "ym" gives a number that is wrong by years. Passing the later date first returns #NUM! instead of a negative number. And the "md" unit has a documented defect that can produce a negative result, because it discards the month before comparing days and has nothing left to borrow from.

Everything below behaves the same in Microsoft 365, Excel 2021 and recent versions. Work each problem before opening the answer.

Three causes, five problems

The six unit codes, and which two are safe

2 problems

DATEDIF takes (start, end, unit) and the unit is a quoted text code. Three are plain: "y" gives complete years, "m" complete months, "d" whole days. Three are remainders and exist so you can build phrases like '3 years, 2 months': "ym" is months after subtracting whole years, "yd" is days after subtracting whole years, and "md" is days after subtracting whole years and months. The remainder codes are where the trouble lives — Microsoft's own documentation carries a warning that "md" can return a negative number — so treat "y", "m" and "ym" as the dependable set and reach for the rest only when you have checked the specific dates.

How it shows up: "DATEDIF y m d" · "How do I get years and months between two dates?"

Q1

You want someone's age in whole years from their date of birth in A2 to today. Which formula is right?

=DATEDIF(A2, TODAY(), "y")

=DATEDIF(TODAY(), A2, "y")
  1. AThe second — the larger date goes first
  2. BThe first — the earlier date must be the first argument, or DATEDIF returns #NUM!
  3. CBoth work; DATEDIF sorts the dates for you
  4. DNeither; age needs YEARFRAC
▶Show answer & explanation

Answer: B. The first — the earlier date must be the first argument, or DATEDIF returns #NUM!

🐱 DATEDIF does not sort its arguments. If the start date is later than the end date it returns #NUM! rather than a negative number, which at least makes the mistake visible. This is the single most common DATEDIF error and it usually appears after someone rearranges columns and updates the formula by eye. Note also that "y" counts complete years, so it matches how people actually state an age — someone one day short of a birthday is still the younger number, which is what you want.

Q2

You want to display a duration as "3 years, 2 months". Which pair of unit codes gives those two numbers?

=DATEDIF(A2, B2, "y") & " years, " & DATEDIF(A2, B2, ???) & " months"
  1. A"m" — it gives the months part
  2. B"ym" — months remaining after the whole years have been taken out
  3. C"md" — the remainder code for months
  4. D"yd" — years and days combined
▶Show answer & explanation

Answer: B. "ym" — months remaining after the whole years have been taken out

🐱 "m" returns the total months across the whole span, so a three-year gap would print '3 years, 38 months'. The remainder you want is "ym", which subtracts the complete years first. This is exactly why the remainder codes exist, and it is worth noticing the naming logic: the code reads as 'what is left after taking out the y' — "ym" is months after years, "yd" is days after years, "md" is days after months. Once the naming clicks, the six codes stop needing to be memorised.

The "md" unit can return a negative number

1 problem

"md" is the only unit with a documented defect: Microsoft's own reference warns that it may produce a negative number, a zero, or an inaccurate result. The cause is that it discards both the year and the month before comparing day numbers, so when the end day-of-month is smaller than the start day-of-month the subtraction goes below zero with no month to borrow from. This is not an edge case you can avoid by being careful — it is a property of the unit. If you need a day remainder that behaves, compute it by subtracting a constructed date instead, which keeps the borrow intact.

How it shows up: "DATEDIF returns a negative number" · "DATEDIF md wrong"

Q3

Start is 31 January 2026 and end is 1 March 2026. What does =DATEDIF(A2, B2, "md") return?

  1. A1 — one day past the end of February
  2. BA negative number, because the day-of-month drops from 31 to 1 with no month left to borrow from
  3. C29 — the length of February
  4. D#NUM!
▶Show answer & explanation

Answer: B. A negative number, because the day-of-month drops from 31 to 1 with no month left to borrow from

🐱 "md" strips the years and months first and then subtracts day 31 from day 1, and because the month has already been discarded there is nothing to borrow from — the result goes negative. Microsoft documents this behaviour as a known limitation rather than treating it as a bug to fix, so the practical advice is to avoid "md" in anything that runs unattended. A dependable alternative for a day remainder is =B2 - EDATE(A2, DATEDIF(A2,B2,"m")), which advances the start date by the whole months first and then subtracts real dates, keeping the borrow intact.

Why Excel does not autocomplete DATEDIF

1 problem

DATEDIF is present in every modern version of Excel but deliberately not surfaced: it does not appear in the formula autocomplete dropdown, and it gives no argument tooltip while you type. It survives from Lotus 1-2-3 compatibility and Microsoft's documentation keeps it mainly for that reason. The practical consequences are that you must type the whole name and all arguments unaided, that a typo in the unit code fails as #NUM! rather than as a helpful message, and that a colleague reviewing your file may reasonably believe you invented the function.

How it shows up: "DATEDIF not showing up" · "Is DATEDIF still supported?"

Q4

A colleague says DATEDIF must be deprecated because it never appears when they start typing =DAT. What is actually true?

  1. AIt was removed in Microsoft 365 and only works in older files
  2. BIt works normally but is hidden from autocomplete and tooltips, a deliberate legacy-compatibility choice
  3. CIt only exists if an add-in is installed
  4. DIt requires the Analysis ToolPak to be enabled
▶Show answer & explanation

Answer: B. It works normally but is hidden from autocomplete and tooltips, a deliberate legacy-compatibility choice

🐱 The function calculates correctly in current versions; it is simply not advertised by the editor. Nothing needs to be installed or enabled. The reason to know this is practical rather than trivial: because there is no tooltip, an incorrect unit code produces #NUM! with no hint about which argument was wrong, so when a DATEDIF fails the first thing to check is the spelling and quoting of the unit — "y" not y, and not "Y " with a stray space.

Keep going

SUMIF, COUNTIF and COUNTIFS practice (8 problems)Includes counting between two dates — two conditions on the same column.Excel formulas practice (8 problems)VLOOKUP failures, dollar signs when dragging, and reading error values.Excel drop-down list practice (6 problems)Why validation disappears after a paste, and how to make a list that grows.

DATEDIF — FAQ

Why does DATEDIF not appear when I type it?

It is hidden from autocomplete and gives no argument tooltip, a deliberate choice dating back to Lotus 1-2-3 compatibility. The function itself works normally in current versions — you simply have to type the name and all arguments unaided, and a mistyped unit code fails as #NUM! with no hint about which argument was wrong.

Why does DATEDIF return #NUM!?

Most often because the start date is later than the end date — DATEDIF does not sort its arguments and refuses rather than returning a negative. The other cause is a malformed unit code: it must be quoted text such as "y", not a bare y, and a stray space inside the quotes will also fail.

What is the difference between "m" and "ym"?

"m" returns the total complete months across the whole span; "ym" returns the months left after the complete years have been subtracted. For a phrase like '3 years, 2 months' you want "y" and "ym" — using "m" would print the total, for example 38 months.

Is the "md" unit safe to use?

No. Microsoft's own reference warns that "md" may return a negative number, a zero, or an inaccurate result, because it discards both the year and the month before comparing day numbers and so has nothing to borrow from. For a day remainder that behaves, use =B2 - EDATE(A2, DATEDIF(A2,B2,"m")) instead.

How do I calculate age in years with DATEDIF?

=DATEDIF(A2, TODAY(), "y") with the date of birth in A2. The "y" unit counts complete years, which matches how age is normally stated — someone a day short of their birthday still shows the lower number.