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.
- Questions
- 4 questions
- Groups
- 3 groups
- Access
- free, no sign-up
- Explanations
- answers and explanations for each question, available to expand
Three causes, five problems
The six unit codes, and which two are safe
2 problemsDATEDIF 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")
- AThe second — the larger date goes first
- BThe first — the earlier date must be the first argument, or DATEDIF returns #NUM!
- CBoth work; DATEDIF sorts the dates for you
- 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"
- A"m" — it gives the months part
- B"ym" — months remaining after the whole years have been taken out
- C"md" — the remainder code for months
- 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?
- A1 — one day past the end of February
- BA negative number, because the day-of-month drops from 31 to 1 with no month left to borrow from
- C29 — the length of February
- 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 problemDATEDIF 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?
- AIt was removed in Microsoft 365 and only works in older files
- BIt works normally but is hidden from autocomplete and tooltips, a deliberate legacy-compatibility choice
- CIt only exists if an add-in is installed
- 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.