DATEDIF not working in Excel: causes and fixes

Quick diagnosis checklist

Match what you see to the cause, then jump to that section:

#NUM!: start date after end date, an empty end date, or a unit Excel does not know ("years", "w", "m " with a space). #VALUE!: a date stored as text that Excel cannot read with your regional settings. #NAME?: the unit typed without quotes, or the function name misspelled. A date like 1/7/1900 instead of 7: the cell has a date format. No tooltip or autocomplete: normal for DATEDIF, keep typing.

The sheet is a staff list with start and end dates, and one problem per row. Ana's row works, so it is the reference. Her 15 Mar 2019 to 4 Nov 2026 is 7 complete years, 91 complete months and 2,791 days.

#NUM!: the start date is after the end date

DATEDIF(start_date, end_date, unit) needs the earlier date first. It never returns a negative number; it returns #NUM! instead. Ben's row has the columns swapped: a start of 1 Sep 2026 and an end of 12 Feb 2024. Plain subtraction, =C3-B3, gives -932, and the minus sign is the clue.

If you only need the gap and the order does not matter, sort the two dates with MIN and MAX. If a backwards row means a data-entry mistake, flag it instead: =IF(B3>C3,"Check dates",DATEDIF(B3,C3,"y")) shows Check dates so the row gets fixed rather than hidden.

#NUM!: the end date is blank

Dev is still employed, so his end date is empty. An empty cell counts as 0, which comes before any real date, so the start is after the "end" and you get #NUM! again. This is the most common cause in HR and project sheets.

Use today's date when the end is missing. In a live sheet write IF(C5="",TODAY(),C5) as the end date. Here an as-of date in E2 (4 Nov 2026) stands in for TODAY so the example stays fixed.

#VALUE!: the date is text

Cara's start date came from a European export as 15.03.2019. It looks like a date but it is text (ISTEXT(B4) is TRUE), and Excel set to US-style dates cannot read dots, so DATEDIF returns #VALUE!. Text that Excel can read, such as 2019-03-15, is converted on the fly and works, which is why the error shows up on some rows and not others.

The lasting fix is to convert the column: select it, Data > Text to Columns, click Next twice, choose Date: DMY and Finish. For a formula fix, rebuild the date from its pieces with DATE.

#NAME? or #NUM!: the unit is wrong

The unit must be text in quotes and one of six codes: "y" (complete years), "m" (complete months), "d" (days), "ym" (months left after the years), "yd" (days left after the years) and "md" (days left after the months). Case does not matter, so "Y" works too.

Without quotes, Excel reads y as a name it does not know and returns #NAME?. Anything else in quotes, such as "years", "w" or "m " with a trailing space, returns #NUM!. There is no week unit: use =INT((C2-B2)/7) for complete weeks, 398 for Ana.

The result shows as a date like 1/7/1900

If you type DATEDIF into a cell that was formatted as a date, or next to date columns, Excel may keep a date format. The answer 7 then shows as 1/7/1900, because day 7 of Excel's calendar is 7 Jan 1900. The formula is right; the format is wrong.

Select the cell and choose Home > Number Format > General (or press Ctrl+Shift+~). The same fix applies to =C2-B2, which can show a date instead of 2,791 days.

"md" gives odd or negative days: use EDATE instead

For "7 years, 7 months, 20 days" people often combine "y", "ym" and "md". Microsoft's own documentation warns that "md" can return a negative number, a zero or an inaccurate result, and it goes wrong around month ends, like Eli's 31 Jan 2024 to 1 Mar 2024. Do not trust it for end-of-month dates.

A safer way to get the leftover days: add the complete months to the start with EDATE, then subtract that from the end. For Eli, DATEDIF "m" gives 1 month, EDATE(B6,1) lands on 29 Feb 2024 (EDATE moves to the last day when the month is shorter), and 1 Mar minus 29 Feb is 1 day. For Fay's row the full formula gives 2 years, 5 months, 16 days.

No tooltip, and which function to use instead

DATEDIF is an old compatibility function. Excel still calculates it but does not show it in autocomplete, the argument tooltip or the Insert Function list, so it looks as if it does not exist. Type the whole formula yourself, with the start date first.

You often do not need it. For days, =C2-B2 or =DAYS(C2,B2) both give 2,791 and show a negative number instead of an error when dates are swapped. Watch DAYS: it takes the end date first, so =DAYS(B2,C2) gives -2,791. For years with decimals, YEARFRAC(B2,C2) is the documented function. Keep DATEDIF for complete years and months, where it is the simplest option.