You probably know Excel can crunch numbers. What most people miss? It can also tell you exactly how long ago something happened — down to the day, month, or year. That's where DATEDIF comes in. It's a hidden function. Microsoft never officially documented it in modern menus, yet it works in every version from Excel 2007 right through to Microsoft 365.

Here's the kicker: most people who use Excel daily have never heard of it. They build complicated formulas with YEAR(), MONTH(), and DAY() to calculate age or tenure. Then they wonder why their results are off by a day or two near month-ends. DATEDIF solves all of that with one clean function call — and you don't even need to memorise the syntax once you've seen it twice.

This guide walks through every unit code, every quirk, and every real-world use we've seen on the Excel practice tests. You'll leave knowing how to calculate age in years and months, project tenure for HR reports, work out contract durations, and avoid the one bug that catches almost everybody the first time around. Ready? Let's open a workbook.

A quick note before we dive in. We've written this for analysts, finance staff, HR coordinators, project managers — anyone who lives in spreadsheets all day. No prior VBA or macro knowledge is needed. Every formula here works in the standard formula bar. If you can type =SUM(A1:A10), you can use DATEDIF. The only thing that matters is reading the syntax carefully and getting the unit code right. Stick with us and by the end you'll have built three working formulas you can paste straight into your own workbooks.

What DATEDIF Actually Does

Think of DATEDIF as a stopwatch that started running on one date and stopped on another. You tell it the start, the end, and what unit you want back — years, months, days — and it hands you a clean integer. No decimals. No rounding surprises. Just the number you'd write down on paper.

The syntax looks like this: =DATEDIF(start_date, end_date, "unit"). Three arguments. That third one, the unit, is the magic part. It accepts six different two-letter codes, and each one tells the function to count something different.

Here's something worth knowing right away. The start date must come before the end date. Reverse them and Excel returns a #NUM! error. Sounds obvious, but when you're pulling dates from a database with mixed formats, it bites. We've seen it on payroll reports more times than we can count.

The Six Unit Codes Explained

Each unit code is a string. Wrap it in quotes or Excel won't recognise it. Here's what each one tells DATEDIF to count.

"Y" — complete years between the two dates. Someone born on 15 March 2000 is exactly 24 on 15 March 2024, and still 24 on 14 March 2024. That's why DATEDIF beats (end-start)/365 for age calculations. It handles leap years natively.

"M" — complete months. Useful for tenure reports where HR wants "23 months" instead of "1.9 years". The function ignores partial months entirely.

"D" — complete days. Same as a simple subtraction (=end-start) but cleaner when you need it inside a larger formula.

"YM" — months remaining after counting full years. If someone has worked 3 years and 7 months, this returns 7. Combined with "Y", you get the classic "X years, Y months" format.

"YD" — days remaining after counting full years. Less common, but it appears on a few Excel test scenarios about anniversary tracking.

"MD" — days remaining after counting full months. Honestly? Microsoft warns this one has bugs around month-end transitions. Use it carefully.

Building a Full Age Formula

You probably want one cell that says "32 years, 4 months, 17 days". Here's how to build it. Open a fresh workbook, put a date of birth in cell B2 — say 23 January 1992 — then drop this into B3.

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

Press Enter. Watch it come alive. Tomorrow that result updates automatically because TODAY() recalculates on every workbook open. If you want the value frozen in time, swap TODAY() for a hard-coded date or use Paste Special > Values to lock it.

One subtle thing: the ampersand & concatenates pieces together. Each DATEDIF call sits inside the formula independently. You can chain as many of these as you want. We've seen contract templates with six nested DATEDIFs computing years, months, days, plus business days remaining. Excel doesn't care — it'll happily run them all.

The Famous "MD" Bug Everyone Hits

Microsoft's own documentation warns about this one. The "MD" unit — days remaining after counting full months — sometimes returns a negative number, or skips a day, when the start date falls on the 31st of a month and the end date is in a shorter month.

Try it. Put 31 January 2023 in A1, 1 March 2023 in B1, then =DATEDIF(A1,B1,"MD") in C1. Excel may return -1 instead of 1. Bizarre, right? The function is doing its arithmetic correctly under its old Lotus-era logic, but the modern calendar disagrees.

The workaround? Use this alternative formula when "MD" matters: =B1-EDATE(A1,DATEDIF(A1,B1,"M")). The EDATE function adds whole months reliably, and the subtraction gives you the leftover days without the bug. We use this version on every payroll template we maintain.

DATEDIF vs Other Date Functions

People ask why bother with DATEDIF when YEARFRAC, NETWORKDAYS, and plain subtraction exist. Fair question. Each tool has its place.

YEARFRAC returns a decimal — 2.5 years, for example. Useful for financial interest accrual, useless for HR reports where "2.5 years" reads worse than "2 years 6 months". DATEDIF gives you the components separately, ready to format.

NETWORKDAYS counts business days, excluding weekends and optional holidays. It's the right choice for SLA tracking or project working-day estimates, but it can't break a duration into years and months. Use them together: DATEDIF for headline totals, NETWORKDAYS for working-day buckets.

Plain subtraction (=end-start) returns raw days as a number. Quick for simple cases, but you lose the unit awareness. We've seen analysts subtract two dates, divide by 365, and quote ages that drift over time as leap years stack up. DATEDIF handles all that quietly.

If you're prepping for an Excel certification test, expect questions that test which function to use when. The pattern: granular control over units = DATEDIF, business days = NETWORKDAYS, decimal fractions = YEARFRAC.

Real Examples From Real Spreadsheets

Let's walk through three scenarios we've seen analysts wrestle with. Each uses DATEDIF differently and shows where the function shines.

Scenario 1: New hire onboarding tracker. An HR coordinator wants a column showing how many days into their probation each new starter is. Probation lasts 90 days. The formula in cell D2, where C2 is start date, is =DATEDIF(C2,TODAY(),"D")&" / 90". Result: 47 / 90. Easy. Then a conditional format highlights anyone past day 80 for a final review meeting.

Scenario 2: Loan amortisation summary. A finance team needs to know loan age in months at any reporting date. Loan started 1 April 2021, reporting date is in B1. The cell shows =DATEDIF("2021-04-01",B1,"M")&" months elapsed". Change B1 to any future date and the count updates. They paired this with INTRATE for total interest accrued — a clean two-cell summary.

Scenario 3: Contract renewal alerts. Sales ops tracks vendor contracts that auto-renew. They use =DATEDIF(TODAY(),renewal_date,"D") to count days until next renewal. Conditional formatting paints anything under 30 days red, under 14 days flashing red. The team chases contracts the moment they enter the danger zone.

Tips That Save Hours in Excel Workbooks

Once DATEDIF clicks, you'll start seeing places to use it everywhere. Here are some patterns experienced spreadsheet builders rely on.

Lock the start date with absolute references. When you drag a DATEDIF formula down a column, you usually want the end date relative (so each row uses its own value) and the start date absolute (the company's launch date, for example). Use $B$2 for the absolute one. Otherwise, dragging shifts everything and you'll wonder why row 50 shows #NUM!.

Wrap in IFERROR for client-facing reports. If your sheet pulls dates from a system that occasionally returns blanks, =IFERROR(DATEDIF(A2,B2,"Y"),"Pending") keeps the report tidy. Nobody likes seeing #VALUE! in an executive summary.

Combine with TEXT for custom output. Want "4y 2m" instead of "4 years, 2 months"? Use =DATEDIF(A2,B2,"Y")&"y "&DATEDIF(A2,B2,"YM")&"m". Short, scannable, fits in narrow columns.

Use named ranges for repeated dates. If 1 January 2024 appears in fifty DATEDIF formulas across your workbook, define it as a named range called YearStart. Then your formulas become =DATEDIF(YearStart,B2,"D") — readable and editable from one place.

Where DATEDIF Fits in Your Excel Toolkit

If you're building serious spreadsheets — the kind colleagues open every morning — DATEDIF earns its place fast. Once it's in your muscle memory, you stop reaching for ugly date subtractions. You stop dividing by 365 and shrugging at the off-by-one results. You write one clean function, hit Enter, and the answer is right.

The trick is to treat it like any other workhorse function. Memorise the six unit codes. Watch out for the MD bug. Pair it with EDATE when you need bulletproof month arithmetic. After a week of regular use, you'll wonder how you managed before.

For more on date handling, anchor functions, and the dozens of small Excel skills that turn up on certification tests, run through the Excel practice questions. We mix DATEDIF problems with VLOOKUP, INDEX-MATCH, and conditional logic scenarios — the exact blend you'll see on a real Microsoft Office Specialist exam.

Last thing on this section. If you find yourself building the same DATEDIF pattern repeatedly across workbooks, save it as a custom function in your Personal Macro Workbook. A short VBA wrapper around DATEDIF gives you a friendlier function name and lets you add validation logic. Five minutes of setup, hours saved over a career.

Going Deeper: DATEDIF and the Modern Excel Stack

Microsoft 365 introduced dynamic arrays in 2020. That changed how DATEDIF behaves in some contexts. If you wrap DATEDIF in a function like FILTER or SORT, the result spills across multiple cells automatically. Drop =DATEDIF(A2:A100,TODAY(),"Y") in one cell and watch it fill ninety-nine cells in one shot. Older versions need you to drag the formula manually — a small thing, but it adds up when you're processing thousands of records.

The same principle works with LET, the function that lets you name intermediate values inside a formula. A typical age calculation becomes: =LET(start,B2,end,TODAY(),y,DATEDIF(start,end,"Y"),m,DATEDIF(start,end,"YM"),y&" years, "&m&" months"). Reads almost like English. Easier to maintain. Easier to hand to a junior team member who needs to tweak the output later.

If you've moved your team to Excel Online or the Microsoft Teams Excel app, DATEDIF still works perfectly. The function is part of the calculation engine, not the UI layer, so it doesn't matter whether the workbook is desktop, web, or mobile.

Common Mistakes We Catch on Practice Tests

Watching candidates take Excel practice tests reveals patterns. The DATEDIF mistakes are weirdly consistent. Three show up over and over.

First, candidates omit the quotes around the unit code. They write =DATEDIF(A2,B2,Y) instead of =DATEDIF(A2,B2,"Y"). Excel reads Y as a cell reference to column Y, finds it empty, and returns either a strange number or an error. The quotes are non-negotiable.

Second, they confuse "YM" with "M". The first returns months remaining after counting full years. The second returns total months. Big difference. Someone with 38 months of tenure has "YM" = 2 (years and 2 months) but "M" = 38. Both are correct — just for different questions.

Third, they format the result cell as a date. DATEDIF returns a number. If that cell is formatted as a date, Excel converts the number to a calendar day. A result of 45 becomes 14 February 1900. Mystifying. Format the cell as General or Number and the issue vanishes.

One Last Pro Tip

If you're showing this to colleagues for the first time, don't oversell it. Drop it into one cell, show them the output, and let the question come naturally. "Wait, how did you do that?" That's the moment to teach it. People remember functions they discover, not functions they're told about. We've watched entire teams adopt DATEDIF within a week because one person quietly built it into a daily report.

Now go open a workbook and try it. Type a date in A1, today's date in B1, and =DATEDIF(A1,B1,"D") in C1. That's your first one. From there, swap the unit code to "M", then "Y", and you'll see the pattern within minutes. Welcome to the small club of people who actually know about this function.

One more practical thought before you close this tab. If you support a team that lives in Excel, build a single template workbook with a few DATEDIF formulas already wired up — age calculator, tenure counter, days-until-deadline tracker. Save it to a shared drive. People will copy it without you having to teach them anything. The function spreads through teams the same way good shortcuts do: someone notices a clean output, asks how it works, and adopts it themselves. That's how Excel skills actually propagate inside an organisation.

If you want to keep sharpening your spreadsheet skills, our Excel practice test bank covers DATEDIF alongside the rest of the date-and-time function family. The questions are written to mirror the format of Microsoft Office Specialist exams and on-the-job scenarios. Most candidates find the date functions section the trickiest, so spending an evening on it pays off. Good luck out there.