The DATEDIF function in Excel
DATEDIF returns the number of complete years, months or days between a start date and an end date, depending on the unit you choose.
It works in every Excel version, but Excel does not suggest it while you type, so you need to write the whole name yourself. The start date must not be later than the end date.
Syntax
=DATEDIF(start_date, end_date, unit)
| Argument | What it means |
|---|---|
| start_date | The earlier date. |
| end_date | The later date. |
| unit | "y" complete years, "m" complete months, "d" days, "ym" months left after the whole years, "md" days left after the whole months (unreliable), "yd" days left after the whole years. |
Examples
1. Age in complete years
| A | B | |
|---|---|---|
| 1 | Age | |
| 2 | Born | 1990-05-20 |
| 3 | On | 2026-03-01 |
| 4 | Years | 35 |
The birthday in 2026 (20 May) hasn't come yet on 1 March, so only 35 years are complete.
2. Complete months
| A | B | |
|---|---|---|
| 1 | Contract | |
| 2 | Start | 2026-01-15 |
| 3 | End | 2026-03-20 |
| 4 | Months | 2 |
From 15 January to 15 March are 2 complete months. The extra 5 days don't make a third.
3. Years and months, as two parts
| A | B | |
|---|---|---|
| 1 | Age | |
| 2 | Born | 1990-05-20 |
| 3 | On | 2026-03-01 |
| 4 | Extra months | 9 |
After 35 whole years (to 20 May 2025), another 9 whole months have passed. Together: 35 years and 9 months.
Common mistakes
Start date after end date. The result is the #NUM! error.
Fix: Swap the dates, or use
=DATEDIF(MIN(B2,B3),MAX(B2,B3),"d").Subtracting dates and dividing by 365 for the age. Leap years make it a little off, and it gives decimals.
Fix: Use DATEDIF with "y" for complete years.
Using the "md" unit. Microsoft warns it can return wrong results.
Fix: Calculate leftover days from EOMONTH or EDATE instead.
Frequently asked questions
Why doesn't Excel show DATEDIF in the function list?
It comes from Lotus 1-2-3 and is kept for compatibility. It is documented and works, but is hidden from the suggestions.
How do I count days between two dates?
Subtract them, or use DAYS(end, start). For working days only, use NETWORKDAYS.
Related functions
- EOMONTHEOMONTH returns the last day of a month; EDATE the same day a number of months later or earlier.
- NETWORKDAYSNETWORKDAYS counts the working days between two dates, Monday to Friday, optionally skipping holidays.
- WEEKDAYWEEKDAY returns the day of the week as a number and WORKDAY adds working days to a date, skipping weekends.