# Why does DATEDIF return #NUM!?

The start date is after the end date, usually because the end date is blank. Fill the gap: =DATEDIF(B2,IF(C2="",$G$1,C2),"y").

**Excel:** `=DATEDIF(B2,IF(C2="",$G$1,C2),"y")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Start | End | Years |  | As of | 2026-10-29 |
| Ana | 2018-03-12 |  |  |  |  |  |
| Ben | 2021-07-01 | 2025-02-14 |  |  |  |  |
| Cara | 2024-11-04 |  |  |  |  |  |
| Dev | 2015-09-30 | 2023-06-30 |  |  |  |  |
| Eli | 2026-02-02 |  |  |  |  |  |
| Fay | 2019-10-29 |  |  |  |  |  |

Formula in D2, filled to D7: `=DATEDIF(B2,IF(C2="",$G$1,C2),"y")`

Result: 8; 3; 1; 7; 0; 7

## How it works

DATEDIF returns #NUM! when the start date is after the end date. A blank end date counts as 0, the earliest date there is, so every current employee errors. IF swaps in the as-of date when the end date is empty, so DATEDIF always gets a valid pair of dates.

## Other ways

- `=IF(C2="",DATEDIF(B2,$G$1,"y"),DATEDIF(B2,C2,"y"))`: The same choice, made outside DATEDIF.

## Common mistakes

- A start date later than a real end date also returns #NUM!. That is a data error worth fixing, not hiding.
- The unit goes in quotes: "y", "m" or "d".
- Put `=TODAY()` in `G1` for tenure that updates every day.

## Questions

**How do I show years and months?**

`=DATEDIF(B2,$G$1,"y")&" yrs "&DATEDIF(B2,$G$1,"ym")&" mos"`.

**Why isn't DATEDIF in Excel's function list?**

It is kept for compatibility and doesn't appear in autocomplete, but it works in every version and in Google Sheets.

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/datedif-num-error