Type =DATE in a cell and Excel offers you DATE, DATEDIFF from Power Query, DATEVALUE, and half a dozen others. What it will not offer is DATEDIF. No autocomplete. No argument tooltip. No entry in the ribbon’s function library. The function still runs — it has since the Lotus 1-2-3 days — but Microsoft treats it like a family member nobody talks about. Anyone calculating age from a birthday or tenure from a hire date hits this wall on day one and assumes the function was removed. It wasn’t. It’s just hidden.
The function Excel forgot to advertise
DATEDIF takes three arguments: a start date, an end date, and a unit code that tells it what to return. The syntax is boring; the behavior is what matters. It always rounds down to the last complete interval, so a person born on January 2, 2000 is still “24 years old” on January 1, 2025 — not 25 until the birthday itself. That’s the answer most tenure reports and age calculations actually want.
=DATEDIF(A2, TODAY(), "Y")Reads the birthday in A2, compares it to today, and returns a whole number of completed years. No leap-year math, no /365.25 approximation. It’s the single formula that replaces every “divide by 365 then round down and maybe subtract one” trick you’ve seen in old spreadsheets.
The six unit codes, side by side
Every DATEDIF question comes down to picking the right unit code. There are six, and three of them behave in ways beginners rarely predict. Keep this table in front of you the first few times you use the function — it prevents most of the confused “why is this returning 0?” moments.
| Unit | Returns | Example (2020-03-15 to 2024-08-10) |
|---|---|---|
"Y" |
Complete years | 4 |
"M" |
Complete months | 52 |
"D" |
Total days | 1609 |
"YM" |
Months after removing full years | 4 |
"YD" |
Days after removing full years | 148 |
"MD" |
Days after removing full years and months | 26 |
Lowercase works too — "y" and "Y" are the same. The “remainder” codes (YM, YD, MD) exist for one reason: composing human-readable strings like “4 years, 4 months, 26 days.” Nothing else uses them, and one of them is quietly broken. More on that in a moment.
Calculate age from a birthday
The plain age formula fits on one line. A more useful version breaks the difference into years, months, and days so the output reads the way a person would say it out loud. Both patterns rely on the same trick: reference the birthday cell three times, once per unit.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Birthday | Age |
| 2 | Rin | 1994-05-12 | =DATEDIF(B2,TODAY(),”Y”) |
| 3 | Mateo | 2001-11-30 | =DATEDIF(B3,TODAY(),”Y”) |
When the report needs the full “31 years, 3 months, 9 days” form, concatenate three DATEDIF calls with the remainder units. The formula looks long, but every piece does exactly one job:
=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days"The "Y" call returns full years. "YM" returns whatever months are left after those years. "MD" returns whatever days are left after those years and months. Wrap it in IFERROR if the birthday cell can be blank — DATEDIF returns #NUM! whenever the start date lands after the end date, and that’s exactly what a blank cell (which Excel reads as 1900-01-00) will cause on old records.
Build a tenure report for HR
Tenure is the same shape as age, with two twists. First, HR usually wants completed months rather than years — “23 months” is a real number an employee can verify against their offer letter, while “1.9 years” is a rounding argument waiting to happen. Second, milestone anniversaries drive real workflows: benefit eligibility, vesting, sabbaticals. A well-built tenure column answers both without extra columns.
=DATEDIF(HireDate, TODAY(), "M")That single value is enough to feed conditional formatting for milestones. Highlight the cell green when tenure hits 12, 36, 60, or 120 months, and the sheet doubles as an anniversary tracker. Combine it with an EOMONTH-based quarter check and you have “who reaches a milestone this quarter” without a single helper column.
TODAY(), every open of the workbook recalculates tenure automatically. Paste the report to a colleague on a different day and the numbers refresh on their machine — no formula changes needed. If you need a frozen snapshot instead, copy the tenure column and paste it back as values before sending.
Related patterns worth knowing if HR data is a recurring workload: our guide to WORKDAY and NETWORKDAYS covers business-day math for sabbaticals and PTO accruals, and the employee time-tracking template already wires DATEDIF into a working weekly report.
The “MD” trap — and the fix
Now the disclaimer Microsoft buries at the bottom of its docs: the "MD" unit is buggy. It sometimes returns a negative number. It sometimes returns zero when it shouldn’t. It sometimes just returns the wrong day count. The failure mode shows up around end-of-month dates and is easy to reproduce — try =DATEDIF("2023-01-31", "2023-03-01", "MD") and Excel hands you -2. That’s the bug.
=DATEDIF(A2,B2,"MD")Returns -2 for 2023-01-31 to 2023-03-01. Silent wrong answer in HR reports.
=B2-EDATE(A2,DATEDIF(A2,B2,"M"))Returns 1 correctly. Works for every end-of-month pair.
The fix works by asking a different question. Instead of trusting DATEDIF to strip out months and years cleanly, add the number of complete months back onto the start date with EDATE, then subtract that from the end date. The remainder is the leftover days, and it’s always right because it’s plain date arithmetic. Use this replacement anywhere a report might touch end-of-month boundaries.
DATEDIF vs YEARFRAC — when to use which
The other “days between dates” function is YEARFRAC, and the two solve different problems even though they look similar. Reach for YEARFRAC when the answer needs to be fractional (financial accrual, prorated fees, insurance premiums). Reach for DATEDIF when the answer needs to be a whole number of completed intervals (age, tenure, notice periods).
| Question | Best pick | Why |
|---|---|---|
| Whole years / months / days between dates | DATEDIF | Rounds down to complete intervals natively |
| Fractional years (e.g. 3.42 years) | YEARFRAC | Returns a decimal; supports day-count bases |
| Human-readable “X years, Y months, Z days” | DATEDIF | The remainder units exist for this exact string |
| Insurance / bond accruals with 30/360 rules | YEARFRAC | Accepts a basis argument for finance conventions |
If you already work with dynamic-array patterns, our essential Excel formulas reference puts DATEDIF, YEARFRAC, and EOMONTH side by side for quick recall — worth bookmarking for anyone rebuilding an old HR workbook.
What to reach for first
Pick the unit code by the question, not the formula. “How old are you?” is "Y". “How long have you worked here?” is "M". “How many days until the contract ends?” is a plain subtraction — no DATEDIF needed. The three remainder codes only earn their keep inside a concatenated “years, months, days” string, and among those, treat "MD" as unsafe by default and swap in the EDATE workaround anywhere end-of-month dates are possible. That handful of habits covers every real report DATEDIF will ever be asked to build.
- ✓ Use
"Y"for age,"M"for tenure — never/365.25approximations - ✓ Use
"YM"and"MD"only inside a concatenated “X years, Y months, Z days” string - ✓ Replace
"MD"with theEDATEworkaround on any sheet that touches end-of-month dates - ✓ Wrap in
IFERRORwhenever the start-date cell can be blank - ✓ Reach for
YEARFRACwhen the answer needs to be fractional, not a whole interval