Arguments
start_date- The earlier date.end_date- The later date.unit- "Y" years, "M" months, "D" days, and "YM"/"MD"/"YD" for remainders.
DATEDIF
Returns the difference between two dates in complete years, months, or days.
Syntax
=DATEDIF(start_date, end_date, unit)=DATEDIF(A2, B2, "Y")Returns: Whole years between the dates
Common for age or tenure.
=DATEDIF(A2, TODAY(), "D")Returns: Days from A2 until today
Pair with TODAY() for a live counter.
How DATEDIF evaluates
DATEDIF returns the number of complete units between two dates. It is the odd one out in Excel's date family: it arrived for compatibility with Lotus 1-2-3, it has never appeared in the function wizard, and typing =DAT into a cell will not offer it in the autocomplete list. It still evaluates correctly in every version of Excel from 2007 to Microsoft 365 - you simply have to type the whole thing, including the arguments, without any of the usual prompting. Microsoft documents it but adds an explicit warning about one of its units.
The third argument is the unit, always in quotation marks. "Y", "M" and "D" give complete years, complete months and days. The three two-letter units give remainders, which is what makes DATEDIF worth using at all: "YM" is the months left over after the whole years, "MD" the days left over after the whole months, and "YD" the days left over after the whole years. Combining them produces the phrasing people actually want - 4 years, 7 months and 12 days - which no other single Excel function produces.
Complete is the operative word. DATEDIF("01/01/2024", "31/12/2024", "Y") is 0, because a full year has not elapsed; the anniversary matters, not the calendar year. Dates must be real dates, not text that looks like one, and start_date must not be later than end_date or the result is #NUM! - there is no signed mode. The unit Microsoft warns about is "MD": because it works by comparing day-of-month numbers across months of different lengths, it can return a negative number or a plainly wrong one when the start date falls near the end of a long month. Where accuracy matters more than phrasing, YEARFRAC gives a fractional year count, and a plain subtraction of two dates gives an exact day count with no quirks at all.
- Complete units only
- Everything is truncated, never rounded. From 15 March 2023 to 14 March 2024 is 0 years and 11 months; one more day makes it 1 year.
- Unit strings
- "Y", "M", "D", "YM", "MD" and "YD", in quotation marks and case-insensitive. Any other string returns #NUM!, which is how a typo like "YY" shows up.
- Order of the dates
- start_date must be on or before end_date. A later start returns #NUM! rather than a negative number, so a column of mixed-order rows needs IF or MIN/MAX to normalise it first.
- Times are ignored
- Only the whole-day part of each serial is used, so a start of 10pm and an end of 6am the next day still counts as one day, not zero.
- The MD quirk
- "MD" compares day numbers across months of unequal length and can produce a negative or incorrect result - Microsoft's own documentation advises against relying on it. Prefer DATEDIF(start, EDATE(start, DATEDIF(start, end, "M")), "D") arithmetic or a day count.
- Text dates
- A date typed as text usually works because Excel converts it, but conversion depends on the machine's regional settings. Wrap literals in DATE(2024, 3, 15) or DATEVALUE to make the formula portable.
Errors DATEDIF returns
| Error | What it means and how to fix it |
|---|---|
| #NUM! | Either start_date is later than end_date, or the unit string is not one of the six recognised codes. Check the argument order first - it is by far the more common cause - and swap the arguments or use =DATEDIF(MIN(A2,B2), MAX(A2,B2), "D"). |
| #VALUE! | One of the dates is text Excel cannot read as a date, or the unit was passed without quotation marks. Confirm with ISNUMBER(A2): a genuine date is a number, and a text date is not. |
| #NAME? | The function name is misspelled - DATEIF and DATDIF are the usual slips. Because there is no autocomplete for DATEDIF, this error is far more common with it than with other date functions. |
| A negative or unexpected number | Not an error value but a wrong answer, and almost always the "MD" unit at a month boundary. Verify the result against a manual count before publishing it. |
More examples
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"Returns: 37 years, 4 months
The standard age or tenure phrasing. "Y" gives the whole years and "YM" the leftover months; using "M" for the second part would give the total months instead and read as nonsense.
=IF(B2<A2, -DATEDIF(B2, A2, "D"), DATEDIF(A2, B2, "D"))Returns: A signed day count
DATEDIF refuses a reversed order, so the sign has to be reapplied by hand. For days only, =B2-A2 does the same thing with no wrapper at all.
=DATEDIF(A2, B2, "M") & " full months, " & (B2 - EDATE(A2, DATEDIF(A2, B2, "M"))) & " days"Returns: The months-and-days split without the MD unit
EDATE moves the start date forward by the whole months, and the remaining days come from a plain subtraction. This avoids the month-boundary errors that "MD" is known for.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Works, but is absent from the function wizard and from autocomplete. Type it out in full. |
| Excel 2019 | Same - still undocumented in the interface, still fully functional. |
| Excel 2021 / Microsoft 365 | Works, including in Excel for the web. Still no autocomplete entry, and the "MD" warning still stands. |
| Google Sheets | Supported with the same six units, and unlike Excel it is documented and offered by autocomplete. |
| LibreOffice Calc | Supported since LibreOffice 3.6, with the same arguments and the same #NUM! on a reversed date order. |
Does DATEDIF Work in Google Sheets?
Google Sheets supports DATEDIF with the same arguments.
In the ExcelTool editor
The ExcelTool editor implements DATEDIF with all six units - Y, M, D, MD, YM and YD - and matches the unit case-insensitively, so "y" and "Y" behave the same.
Unlike Excel, the editor lists DATEDIF in its formula-bar autocomplete with the signature DATEDIF(start, end, "unit"), so it can be discovered by typing rather than having to be memorised.
The date arguments accept a serial number, a numeric string or text the editor can parse as a date, and only the whole-day part of each serial is used. An end date earlier than the start date returns #NUM!, and so does an unrecognised unit string.
The editor reproduces Excel's MD arithmetic rather than correcting it - it measures the remaining days against the length of the month before the end date - so the same negative results Excel produces at month boundaries appear here too.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Build a DATEDIF formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
DATEDIF questions
It was kept for compatibility with Lotus 1-2-3 and was never added to the function list, the wizard or the autocomplete index. That is not a sign it is deprecated or about to be removed - it evaluates normally in every current version including Excel for the web - but you have to type the name and the arguments in full, which is also why #NAME? from a misspelling is such a common result.
Not without checking it. Microsoft's documentation warns that "MD" may return a negative number, a zero or an inaccurate result, because it compares day-of-month numbers across months that do not have the same length. A start date of 31 January is the classic trigger. Compute the leftover days with EDATE arithmetic instead, or accept a total day count from a plain subtraction.
Yes for "Y" - it compares the anniversary, so a birthday on 29 February simply does not complete a year in a non-leap year until 1 March by Excel's reckoning. The unit that gets leap years wrong more often is "YD", where the leftover day count crosses a 29 February and comes back one higher than a manual count of the same period.
YEARFRAC, which returns a decimal and takes a basis argument controlling the day-count convention - 1 for actual/actual, 0 for the US 30/360 convention used in finance. =YEARFRAC(A2, B2, 1) gives 4.53 where DATEDIF's "Y" would give 4. YEARFRAC is documented, appears in autocomplete, and accepts the dates in either order.