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

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.

**Excel:** `=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Project | Planned finish | Actual finish | Months apart |
| Alpha | 2026-03-31 | 2026-06-15 |  |
| Beta | 2026-09-30 | 2026-07-01 |  |
| Gamma | 2026-01-15 | 2026-01-15 |  |
| Delta | 2026-11-20 | 2026-02-10 |  |
| Echo | 2026-05-01 | 2027-02-01 |  |

Formula in D2, filled to D6: `=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")`

Result: 2; 2; 0; 9; 9

## 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.

## Other ways

- `=IF(C2>=B2,1,-1)*DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")`: Keeps the direction: negative when the actual date is earlier. (Beta and Delta show -2 and -9.)
- `=IFERROR(DATEDIF(B2,C2,"m"),"Check dates")`: Flags 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".

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/fix-the-num-error-in-datedif