# DATEDIF function in Excel

> DATEDIF counts the complete years, months or days between two dates.

Source: https://spreadsheetpractice.com/functions/datedif · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** DATEDIF counts the complete years, months or days between two dates.
- **Syntax:** `=DATEDIF(start_date, end_date, unit)`
- **Most common use:** `=DATEDIF(B2,C2,"Y")` returns 7 (Ana has 7 complete years)
- **Available in:** Every current version
- **Category:** [Date functions](https://spreadsheetpractice.com/functions/category/date)
- **Returns:** A whole number of complete units.
- **Google Sheets:** Works the same in Google Sheets, where it is documented.

## What it does

DATEDIF counts the complete years, months or days between two dates. It is the standard way to calculate age or length of service. It works in every version of Excel, but it is undocumented: it doesn't appear in the function list or autocomplete, so you have to type it in full.

## Syntax

`=DATEDIF(start_date, end_date, unit)`

| Argument | Required? | What it is |
|---|---|---|
| `start_date` | Required | The earlier date. |
| `end_date` | Required | The later date. If it is before start_date, DATEDIF returns #NUM!. |
| `unit` | Required | "Y" complete years, "M" complete months, "D" days, "YM" months after the whole years, "MD" days after the whole months, "YD" days after the whole years. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=DATEDIF(B2,C2,"Y")` | 7 | Ana has 7 complete years |
| E3 | `=DATEDIF(B3,C3,"M")` | 22 | Raj has 22 complete months; the 23rd isn't finished |
| E4 | `=DATEDIF(B2,C2,"Y")&" yrs, "&DATEDIF(B2,C2,"YM")&" mos"` | 7 yrs, 6 mos | Years plus leftover months, as a label |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Name | Start date | As of |  | Result |
| 2 | Ana | 4/15/2019 | 10/28/2026 |  | 7 |
| 3 | Raj | 11/30/2024 | 10/28/2026 |  | 22 |
| 4 |  |  |  |  | 7 yrs, 6 mos |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #NUM! | The start date is after the end date. Swap them so the earlier date comes first. |
| #NUM! or #NAME? with a valid pair of dates | The unit is wrong. It must be one of "Y", "M", "D", "YM", "MD" or "YD" in quotes; a misspelled unit gives #NUM! and a unit without quotes gives #NAME?. |
| Can't find it in the function list | DATEDIF is undocumented in Excel, so autocomplete won't offer it. Type the whole formula; it still works. |
| Odd results with "MD" | Microsoft warns that the "MD" unit can return wrong values near month ends. Prefer "Y" and "YM", or subtract dates for days. |

## Tips

- Use TODAY() as the end date to keep ages and tenure current: =DATEDIF(A2,TODAY(),"Y").

## Related functions

- [DAYS](https://spreadsheetpractice.com/functions/days): DAYS returns the number of days between two dates.
- [YEAR](https://spreadsheetpractice.com/functions/year): YEAR returns the four-digit year from a date.
- [TODAY](https://spreadsheetpractice.com/functions/today): TODAY returns the current date, with no time attached, and updates every time the workbook recalculates.
- [EDATE](https://spreadsheetpractice.com/functions/edate): EDATE returns the date a given number of months before or after a start date, keeping the same day of the month.

## Questions

### How do I calculate age in Excel?

Use =DATEDIF(birth_date,TODAY(),"Y"). It counts complete years, so the age only goes up on the birthday.

### Why can't I find DATEDIF in Excel?

It is an undocumented compatibility function. It doesn't show in autocomplete or the Insert Function dialog, but typing it works in every version.

### How do I show years and months of service?

Combine two DATEDIF calls: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months".

## Practice

A graded DATEDIF exercise ("Years and months of service") is on [the DATEDIF page](https://spreadsheetpractice.com/functions/datedif). It is checked instantly, then re-checked on changed data.

Lessons that use DATEDIF:

- [Lesson 25: Tenure, quarters & weekdays](https://spreadsheetpractice.com/learn/tenure-quarters-weekdays) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
