#NUM! in DATEDIF: When Start Date is after End Date

The #NUM! error in DATEDIF occurs when the start_date argument is chronologically later than the end_date argument. Unlike standard subtraction (=End_Date - Start_Date), which cleanly returns a negative number of days, DATEDIF requires the start date to be earlier than or equal to the end date. If the timeline is reversed, the function cannot compute a negative duration, halting calculation with #NUM!.

Quick Fix

Ensure your earlier date comes first in the formula: =DATEDIF(EarlierDate, LaterDate, "D"). To automatically prevent errors regardless of which date is entered first, wrap the cell references in MIN and MAX: =DATEDIF(MIN(A2, B2), MAX(A2, B2), "D").

---

Understanding DATEDIF Syntax and Constraints

DATEDIF is a legacy compatibility function originally supported for Lotus 1-2-3 models. Because of its age, it does not appear in Excel’s standard formula autocomplete list, though it remains fully functional in all modern versions:

=DATEDIF(start_date, end_date, unit)

The function supports six interval units:

• "Y": Complete elapsed years.
• "M": Complete elapsed months.
• "D": Complete elapsed days.
• "YM": Remaining months after whole years are subtracted.
• "YD": Remaining days after whole years are subtracted.
• "MD": Remaining days after whole months are subtracted.

Regardless of which unit is selected, the core constraint remains identical:

$$
\text{start\_date} \le \text{end\_date}
$$

If start_date is even a single day later than end_date, Excel produces #NUM!.

---

Common Scenarios That Invert Dates

1. Dates Stored in Regional Text Formats (DMY vs. MDY)

When working with imported data, dates stored as text can easily cause an unintended date reversal.

Suppose you have two dates:

• Start Date: March 5, 2024
• End Date: April 2, 2024

If your operating system uses the US format (MM/DD/YYYY), but your source file contains UK text dates (DD/MM/YYYY):

• 05/03/2024 is read as May 3, 2024 (instead of March 5).
• 02/04/2024 is read as February 4, 2024 (instead of April 2).

To Excel, the start date appears to be in May, while the end date appears to be in February. Because the start date is now chronologically later, DATEDIF returns #NUM!, a parsing issue also seen in #VALUE! in NETWORKDAYS: Invalid Date formats.

Diagnostic Check:
Test whether Excel reads the dates properly:

=MONTH(A2)

If the formula returns 5 when you expected 3, your date components are reversed. Clean the data using Data > Text to Columns to re-map text columns to true serial dates.

---

2. Blank Cells Treated as Date Serial 0

Excel stores dates as numeric serial numbers starting with January 1, 1900 (Day 1). An empty, unpopulated cell evaluates to 0 in date math (representing January 0, 1900).

If your formula references an unpopulated end_date cell:

• Start Date (A2): 2024-06-01 (Serial number ~45444)
• End Date (B2): Blank cell (Evaluates to 0)

Because 45444 is greater than 0, start_date > end_date, and DATEDIF fails with #NUM!.

The Fix:
Verify that both date cells contain values before running the function:

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

---

3. Cross-Midnight or Cross-Year Operational Data

Tracking shifts or project turnaround times across calendar years often triggers #NUM! if dates are entered without years, as shown in Unit Turnaround Time: Fixing DATEDIF errors when a unit is vacant across calendar years..

If a worker checks in on December 30 and checks out on January 2, entering only "12/30" and "01/02" causes Excel to assign both dates to the current calendar year. The check-out date (January 2) is chronologically earlier than the check-in date (December 30), producing #NUM!.

---

Step-by-Step Diagnostic and Repair Strategies

Strategy 1: The MIN/MAX Absolute Duration Wrapper

If you need to calculate the elapsed time between two milestones regardless of the order in which they were recorded:

=DATEDIF(MIN(A2, B2), MAX(A2, B2), "Y") & " Years, " &
 DATEDIF(MIN(A2, B2), MAX(A2, B2), "YM") & " Months"

How it works: MIN(A2, B2) always selects the earlier date for the first argument, while MAX(A2, B2) guarantees that the later date becomes the second argument. This eliminates #NUM! errors across the entire column.

---

Strategy 2: Preserving Directional Signs (+/-)

If your workflow requires knowing whether a project was completed early or late, using MIN/MAX is unsuitable because it strips away negative intervals.

Because DATEDIF cannot return negative outputs, replace it with standard subtraction:

=B2 - A2

• If the project finishes 5 days early, this evaluates cleanly to 5.
• If you need elapsed months or years with directional awareness, use the YEARFRAC function: =YEARFRAC(A2, B2) * IF(B2 < A2, -1, 1)

---

Strategy 3: Controlled Error Suppression

If an inverted date represents an input error that should be flagged for correction, avoid blanket IFERROR functions that conceal the issue, as discussed in IFERROR vs. IFNA: When to be surgical with error catching. Instead, use an explicit logical check:

=IF(A2 > B2, "Invalid Date Order", DATEDIF(A2, B2, "D"))

This formula alerts users to data-entry mistakes while calculating valid records normally.

Technical Warning: Avoid using the "MD" unit in complex date calculations. Microsoft has documented an internal calculation bug where "MD" can return negative numbers or irregular results when calculating differences across short months, covered in DATEDIF “MD” Bug: Why calculating “Days between months” can result in negative numbers..

For broader coverage of spreadsheet error handling, consult Fixing #VALUE!, #DIV/0!, and #NUM! Errors: The Excel Data Logic Guide.