Excel function · Dates
DATEDIF
Counts whole years, months or days between two dates.
What it does
DATEDIF returns the difference between two dates in a unit you choose: "Y" for complete years, "M" for complete months, "D" for days. It is the usual way to work out an age. Excel doesn't suggest it as you type, but it works.
Syntax
=DATEDIF(start_date, end_date, unit)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- start_date
- The earlier date, such as a date of birth.
- end_date
- The later date. It must not be before the start date.
- unit
- What to count, in quotes: "Y" years, "M" months, "D" days. "YM" gives the months left over after whole years.
Example
Someone born on 15 June 1990: how old are they on 1 March 2026?
| A | B | C | |
|---|---|---|---|
| 1 | Born | On | Age |
| 2 | 1990-06-15 | 2026-03-01 | 35 |
The formula in C2:
=DATEDIF(A2,B2,"Y")Result: 35
Their 36th birthday is not until June 2026, so on 1 March they have completed 35 years.
Common mistakes
- Swapping the dates. A start date after the end date gives
#NUM!. - Writing the unit without quotes. Y on its own is read as a name and fails.
- Dividing the days by 365 instead. Leap years make that drift, so ages come out wrong near birthdays.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | DATEDIF | , |
| Deutsch | DATEDIF | ; |
| Français | DATEDIF | ; |
| Español (España) | SIFECHA | ; |
| Español (México) | SIFECHA | , |
| 日本語 | DATEDIF | , |
| 한국어 | DATEDIF | , |
| 繁體中文 | DATEDIF | , |
| ไทย | DATEDIF | , |
| ខ្មែរ | DATEDIF | , |
| Tiếng Việt | DATEDIF | ; |