Excel DATEDIF #NUM! Error: Causes, Checks, and Fixes

Excel’s DATEDIF returns #NUM! most often because its start date is later than its end date; it can also return that error when the unit argument is invalid. Check the two dates and the unit first, then correct the inputs or deliberately reorder the dates if you want an unsigned interval.

What the DATEDIF #NUM! error means

DATEDIF calculates the time between two dates in years, months, or days. Its argument order matters: the first date must be the earlier date, and the second must be the later date. If Excel sees the first date as later, it cannot calculate the requested forward interval and returns #NUM!.

The function’s syntax is DATEDIF(start_date,end_date,unit). The error name can be confusing: this is not usually a sign that a cell contains a number that is too large. It is commonly a signal that the dates are in the wrong order. A misspelled or unsupported unit can also cause #NUM!.

DATEDIF is supported in Microsoft 365 for Windows, but it is an older compatibility function and may not appear in Excel’s function autocomplete or provide the same guidance as newer functions. Its absence from autocomplete does not, by itself, mean the function cannot be used.

Check the date order first

Suppose A2 contains March 15, 2024, and B2 contains March 14, 2023. With those dates in that order, the function asks Excel to calculate a period starting in 2024 and ending in 2023. That reversed chronology is the usual cause of the error.

With the start date in A2 and the end date in B2, this formula returns #NUM! for those example dates:

=DATEDIF(A2,B2,"Y")

To calculate elapsed time from the earlier date to the later one, put March 14, 2023, in A2 and March 15, 2024, in B2. With that layout, the same formula returns 1, meaning one complete year has elapsed. If the dates represent a start and finish, correcting the cells is usually better than silently changing their order.

When the dates can arrive in either order and you only need the elapsed interval, you can make the earlier date the start automatically. With the two dates in A2 and B2, regardless of which is earlier, this formula returns 1 for March 14, 2023, and March 15, 2024:

=DATEDIF(MIN(A2,B2),MAX(A2,B2),"Y")

That approach discards direction. It is unsuitable when the order has meaning—for example, when checking whether a project finished before it began or whether a payment date precedes an invoice date.

Confirm that Excel recognizes both entries as dates

Excel stores dates as serial numbers and displays them using a date format. Two cells can look like dates while holding text instead, particularly when values were imported from a website, CSV file, or another system. Text dates may lead to #VALUE! or other unexpected results rather than the usual reversed-date #NUM!, so it is worth checking the underlying values before changing the formula.

Click each cell and inspect the formula bar. A date entered and recognized by Excel will generally behave like a number: changing the cell’s format to General displays its serial value. A text entry may remain left-aligned by default or refuse to respond as expected to number formatting. Alignment is only a clue, not a definitive test, because formatting and alignment can be changed.

Enter dates using a format Excel recognizes in your regional settings, or build them with the DATE function.
For imported data, use Data > Text to Columns when appropriate, or convert the source values to real dates before calculating.
Check for leading apostrophes, spaces, or inconsistent date formats in the source cells.
Confirm the displayed month and day. A value such as 04/05/2024 can mean April 5 or May 4 depending on regional settings.

With year in 2024, month in 3, and day in 15, this formula constructs March 15, 2024 as an actual Excel date:

=DATE(2024,3,15)

The result is a date serial displayed as a date if the cell has a date format. Using DATE avoids ambiguity about whether a typed string such as 03/04/2024 means March 4 or April 3.

Check the unit argument

The third argument must be one of the recognized text codes. Type it in straight double quotes. The available units are:

"Y" — complete years in the interval.
"M" — complete months in the interval.
"D" — total days in the interval.
"YM" — months after subtracting complete years.
"YD" — days after subtracting complete years.
"MD" — days after subtracting complete months and years; Microsoft cautions that this unit can produce inaccurate results.

For example, a typo such as "YY", or a unit not enclosed as intended, can make the argument invalid. If you are troubleshooting, start with "D", which asks for total days and avoids questions about how a partial month or year should be counted.

With A2 holding March 14, 2023, and B2 holding March 15, 2024, this formula returns 367, the number of days between those dates:

=DATEDIF(A2,B2,"D")

By contrast, the "Y" unit returns 1 for the same dates because it counts completed years, not a decimal fraction of a year.

Handle blank cells and make errors easier to spot

A blank date cell is not a meaningful start or end date. Depending on the formula and cell contents, Excel may treat a blank reference as zero and produce a misleading interval or an error. If a row is not ready for calculation, test for missing dates before calling DATEDIF.

With the start date in A2 and end date in B2, this formula returns a blank if either cell is empty; otherwise it returns the number of complete years, or an error if the dates are reversed:

=IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"Y"))

To show a useful prompt for reversed dates, use a separate check. With A2 as the start and B2 as the end, this formula displays Check date order when A2 is later; for a valid interval from March 14, 2023, to March 15, 2024, it returns 1:

=IF(A2>B2,"Check date order",DATEDIF(A2,B2,"Y"))

Using IFERROR to hide every problem can make a report look clean while concealing reversed dates, bad imports, or misspelled units. If you use error handling, make the output informative and investigate the source problem rather than treating every error as a zero. Also remember that DATEDIF returns a number for valid inputs, while a formula that sometimes returns a message returns mixed text and numeric results.

Understand complete years and month-end results

A frequent misconception is that DATEDIF(start,end,"Y") reports the number of calendar years touched by the interval. It does not: it counts completed anniversaries. For example, an interval from December 31, 2023, to January 1, 2024, touches two calendar years but contains no complete year. The result for the "Y" unit is 0.

Likewise, "M" counts full months, not the difference between the month numbers on the calendar. With a start date of January 31 and an end date of February 28 in a non-leap year, a reader may expect one month because the dates fall in adjacent months. DATEDIF’s completed-month logic can surprise users around month ends, so verify the result against the rule your report needs.

The "MD" unit deserves extra care. Microsoft warns that it may return a negative number, zero, or an inaccurate result in some situations. Avoid relying on it for important month-end calculations; test representative dates and consider a different calculation designed around the exact business rule. Age calculations also need a defined convention for leap-day birthdays: Excel can calculate completed years, but the appropriate anniversary rule may depend on the purpose of the calculation.

When to use a different formula

Use DATEDIF when you need its completed-years, completed-months, or total-days behavior and have verified the date order and unit. Choose another function when you need a fractional year, a count of calendar boundaries crossed, or transparent component calculations. Those are different questions, so they should not be treated as interchangeable answers.

For a year fraction using an actual/actual day-count basis, with A2 as the earlier date and B2 as the later date, YEARFRAC returns approximately 1.0055 for March 14, 2023, through March 15, 2024:

=YEARFRAC(A2,B2,1)

That decimal is not a completed-year count, and the basis argument affects the result. If a report needs separate years, months, and days, build and test those components against clear examples rather than assuming one compact formula matches every definition of elapsed time. For the standard age-in-completed-years question, DATEDIF can be suitable as long as its dates are in chronological order.

Frequently Asked Questions

Why does DATEDIF return #NUM! when both cells show dates?
Most often, the first date is later than the second. Compare the underlying dates, not just their display format, and make sure the unit is one of the recognized codes such as "Y", "M", or "D".

Does DATEDIF work in Excel for Microsoft 365?
Yes. It is available in Excel for Microsoft 365, although it may not appear in autocomplete because it is an older compatibility function. The same basic syntax works in Excel for Mac and Excel for the web; menus for inspecting or importing dates can differ by platform.

How do I calculate the difference when the dates might be reversed?
If direction does not matter, use the earlier of the two dates as the start and the later as the end, for example with MIN and MAX. If direction matters, correct the source data or display a warning rather than silently reordering the values.

Why does DATEDIF show zero years for dates in different years?
The "Y" unit counts complete years, not calendar-year changes. An interval from December 31 to January 1 crosses into another year but has not completed an anniversary, so the result is zero.

Can I fix #NUM! by wrapping DATEDIF in IFERROR?
You can replace an error with a message, but that does not correct the dates or unit. Prefer a specific check for blank cells or reversed dates so a genuine data problem remains visible and can be fixed.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.