Date Difference Generator
Calculate the gap between two dates in years, months, or days using Excel's DATEDIF function.
DATEDIF
Calculate the difference between two dates in days, months, or years.
The earlier date cell or value.
The later date cell or value.
"Y" for full years, "M" for months, "D" for days.
=DATEDIF(A2, B2, "Y")Checks Before You Paste
- •The start date must be on or before the end date.
- •Use "Y" for whole years, "M" for whole months, "D" for days.
- •For plain day counts you can also just subtract: =B2-A2.
How DATEDIF works
Availability: Present in every version of Excel since 5.0, and in Google Sheets and LibreOffice Calc. Excel does not list it in Insert Function, does not autocomplete it and shows no argument tooltip - it is kept for Lotus 1-2-3 compatibility, so you have to type the whole name from memory.
DATEDIF returns the number of complete units between two dates. Complete is the important word: it counts whole years, whole months or whole days that have finished, and discards the remainder rather than rounding it. A person born on 14 July is 27 on 13 July and 28 the following day, which is exactly the behaviour an age or a tenure needs and exactly what (end - start)/365 gets wrong every leap year.
The third argument is a quoted unit code, and there are six. "Y" gives complete years, "M" complete months and "D" plain days - the same answer as subtracting the two cells. The other three are remainders, meant to be combined with the first three to phrase a duration in words. "YM" is the months left over after the whole years, "MD" is the days left over after the whole months, and "YD" is the days left over after the whole years. So "3 years, 7 months" is a "Y" and a "YM" side by side.
Microsoft's own documentation carries a warning about "MD": it can return a negative number, a zero, or a plainly inaccurate result, because of how it borrows across months of unequal length. If you need days-and-months phrasing that has to be right in every case, compute it from EDATE instead - =B2-EDATE(A2, DATEDIF(A2,B2,"M")) gives the leftover days without the borrow. "YM" and "YD" behave predictably by comparison.
DATEDIF is directional and will not go backwards. If the start date is later than the end date it returns #NUM! rather than a negative number, so a duration column where somebody has typed the dates the wrong way round shows an error rather than a misleading figure. Both arguments must be real dates - Excel serial numbers underneath, whatever the display format - and text that merely looks like a date returns #VALUE!.
Syntax
=DATEDIF(start_date, end_date, unit)| Argument | Required | What it does |
|---|---|---|
start_date | Required | The earlier date, as a cell reference, a DATE() call or a serial number. Text that looks like a date returns #VALUE!; wrap it in DATEVALUE first. |
end_date | Required | The later date. It must be on or after start_date - the other way round returns #NUM!, not a negative count. |
unit | Required | Quoted text: "Y" complete years, "M" complete months, "D" days, "YM" months after whole years, "MD" days after whole months, "YD" days after whole years. Unquoted, Y is read as a defined name and returns #NAME?. |
More worked examples
A staff list with a start date of 15/03/2021 in A2 and a leaving date of 01/11/2024 in B2, and you want tenure phrased the way HR writes it.
=DATEDIF(A2, B2, "Y")&" years, "&DATEDIF(A2, B2, "YM")&" months"Returns: "3 years, 7 months"
"YM" is the remainder after the whole years, so it never exceeds 11. Using "M" in the second slot would give 43 - the total months, not the leftover.
A project that ran from 01/09/2025 in A2 to 15/03/2026 in B2, reported in months and days.
=DATEDIF(A2, B2, "M")&" months, "&DATEDIF(A2, B2, "MD")&" days"Returns: "6 months, 14 days"
"MD" is reliable here because the start day, the 1st, is earlier in the month than the end day. Start on the 30th and end on the 1st and this unit is the one Microsoft warns about.
A date of birth of 14/07/1998 in A2 and a fixed reporting date of 01/09/2026 in B2.
=DATEDIF(A2, B2, "Y")Returns: 28
The same calculation as (B2-A2)/365 gives 28.15, and flooring that happens to agree here but drifts out by a day roughly every four years because of leap days.
Common mistakes
- "MD" returning a negative or nonsensical day count
- This is a known defect Microsoft documents rather than a mistake in your formula: when the start day of the month is later than the end day, the borrow across unequal months goes wrong. Replace it with =B2-EDATE(A2, DATEDIF(A2,B2,"M")) when the figure has to be defensible.
- #NUM! from dates in the wrong order
- DATEDIF refuses to count backwards. A blank end date is read as 0, meaning January 1900, which is before every real start date - so an unfinished row shows #NUM! rather than a running total. Guard it with =IF(B2="", "", DATEDIF(A2,B2,"Y")).
- Forgetting the quotes around the unit
- =DATEDIF(A2,B2,Y) returns #NAME? because Y is treated as an undefined name. The unit is always a text string, and it is the one argument that cannot be a bare reference to a cell containing the letter unless that cell holds it as text.
- Month-end start dates under-counting
- "M" counts complete months by day of month, so 31/01 to 28/02 is zero complete months - February has no 31st for the count to reach. Where an end-of-month start date should count as a full month, EOMONTH-based arithmetic is a better fit than DATEDIF.
- Dates that are really text
- A column imported from CSV often holds dates as text; they left-align and DATEDIF returns #VALUE!. Convert with DATEVALUE or Text to Columns first. Excel's date system also has no dates before 01/01/1900, so historical dates cannot be measured this way at all.
Frequently Asked Questions
It is deliberately undocumented. DATEDIF was added for compatibility with Lotus 1-2-3 and never made it into the function list, the autocomplete dropdown or the argument tooltips. It calculates normally in every version - you just have to type the whole name and all three arguments unaided.
DATEDIF with "Y" for an age, because an age is a count of completed years and that is what it returns. YEARFRAC returns a decimal under a day-count convention - 30/360, actual/365 and so on - which is right for interest calculations and wrong for birthdays unless you floor it and pick the basis carefully.
"D" gives the full count, and is identical to writing =B2-A2 formatted as a number. "MD" and "YD" both give remainders instead - the days left after the whole months, and the days left after the whole years - so they are only meaningful next to an "M" or a "Y".
No. Every unit counts calendar time, weekends and holidays included. NETWORKDAYS is the function for business days, and NETWORKDAYS.INTL if your weekend is not Saturday and Sunday.
Yes, with the same six units, and Sheets actually documents it in its function list. LibreOffice Calc supports it too. The caution about "MD" applies wherever you use it, since all three implementations reproduce the same borrow behaviour.