Spreadsheet Practice

How to fix the #NUM! error in DATEDIF in Excel

Formula (Excel and Google Sheets)

=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")

Put the dates in order with MIN and MAX: =DATEDIF(MIN(B2,C2),MAX(B2,C2),"m"). DATEDIF returns #NUM! when the start is after the end.

Result on the example
one result per row in D2, filled down to D6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

How it works

DATEDIF needs the earlier date first and returns #NUM! when the start is after the end, as for Beta and Delta, which finished early. MIN and MAX put the two dates in order, so DATEDIF always gets a valid pair.

=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")

Result2

  1. B23/31/2026

    The value in B2.

  2. C26/15/2026

    The value in C2.

  3. MIN(B2,C2)46112

    MIN: Smallest number.

    • number1B23/31/2026
    • number2C26/15/2026
  4. MAX(B2,C2)46188

    MAX: Largest number.

    • number1B23/31/2026
    • number2C26/15/2026
  5. DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")2

    DATEDIF: Difference in "Y", "M", "D", "YM", "MD" or "YD".

    • start_dateMIN(B2,C2)46112
    • end_dateMAX(B2,C2)46188
    • unit"m"

In Google Sheets

The same formula works in Google Sheets: =DATEDIF(MIN(B2,C2),MAX(B2,C2),"m"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(C2>=B2,1,-1)*DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")2, -2, 0…Different resultKeeps the direction: negative when the actual date is earlier. Beta and Delta show -2 and -9.
=IFERROR(DATEDIF(B2,C2,"m"),"Check dates")2, Check dates, 0…Different resultFlags the reversed rows instead of fixing them. Shows "Check dates" for Beta and Delta.

Common mistakes

  • A blank date counts as 0, January 0, 1900, which DATEDIF accepts and turns into a huge number. Check for blanks first.
  • DATEDIF also returns #NUM! for an unknown unit. The units are "y", "m", "d", "ym", "yd" and "md".

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Fix the #NUM! error in DATEDIF

In D2, calculate the complete months between the planned and actual finish dates, whichever comes first, without a #NUM! error. Fill down to D6.

Questions

Why does DATEDIF return #VALUE!?

One of the dates is text. Convert it with DATEVALUE, or retype it.

How do I get a signed number of days instead?

Subtract: =C2-B2 is negative when C2 is earlier.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.