Function guides · Date
DATEDIF function
Returns the number of complete years, months or days between two dates.
Syntax
=DATEDIF(start_date, end_date, unit)
| Argument | What it is |
|---|---|
start_date | The earlier date. |
end_date | The later date. It must not be before the start date. |
unit | "y" complete years, "m" complete months, "d" days, "ym" months ignoring years, "md" days ignoring months and years, "yd" days ignoring years. |
What DATEDIF does
DATEDIF counts the complete years, months or days between two dates. The third argument, in quotes, says which.
It is the proper way to calculate age and length of service. Complete years means a birthday has to have passed. A person is 35 until the day they turn 36, however close it is.
Excel does not list it in the Insert Function dialog and shows no argument tooltip, so you must type it from memory. It works in every current version. Put the earlier date first, or the result is #NUM!.
The unit codes combine to give any breakdown. “y” gives complete years, “ym” the months left after those years, and “md” the days left after the months, though that last code has known problems and plain subtraction is safer for days. A common display is years and months joined into text, as in the second example.
For a live age, use TODAY as the end date. For a fixed point in time, such as age at the date of an event, put that date in a cell and refer to it, so the result does not change tomorrow.
DATEDIF examples
1. Age in complete years
| A | B | C | |
|---|---|---|---|
| 1 | Birthday | As of | |
| 2 | 3/14/1990 | 3/13/2026 | 35 |
DATEDIF with "y" counts whole years. Someone born March 14, 1990 is 35 on March 13, 2026 and turns 36 the next day. Dividing days by 365 would be wrong around leap years.
2. Years and months of service
| A | B | C | |
|---|---|---|---|
| 1 | Hired | As of | |
| 2 | 6/1/2020 | 11/8/2026 | 6 years, 5 months |
Two calls give a readable tenure. "y" returns the whole years and "ym" the months left over after them. Hired June 1, 2020 and measured on November 8, 2026: 6 years and 5 months.
3. Total months between two dates
| A | B | C | |
|---|---|---|---|
| 1 | Start | End | |
| 2 | 1/15/2026 | 11/14/2026 | 9 |
The "m" unit counts complete months, which is what a billing or lease calculation wants. From January 15 to November 14 is 9 complete months, because the tenth month has not finished.
Common DATEDIF mistakes
- Expecting it in the function list. DATEDIF is undocumented in the Insert Function dialog and gives no tooltip, but it works. Type it in full.
- End date before start date. The result is #NUM!. Put the earlier date first.
- Using \"md\". The "md" unit is known to give wrong results in some cases. Compute days with plain subtraction.
- Dividing by 365. Age by days divided by 365 is off near birthdays in leap years. Use DATEDIF with "y".
DATEDIF on Excel job tests
Age and length of service call for DATEDIF: "calculate each employee's age in years", "show length of service in years and months". Dividing by 365.25 matches on most rows and misses on the ones near a birthday, so test any age formula on a date close to a birthday.
Remember the order, earlier date first, and the unit in quotes. For a live age, use TODAY as the end date. DATEDIF is missing from the function list, so you have to type it in full.
Related functions
DATEDIF questions
- Is DATEDIF a real Excel function?
- Yes. It is kept for compatibility with Lotus 1-2-3 and does not appear in the Insert Function list.
- How do I calculate age?
=DATEDIF(birthday,TODAY(),"y").- What does the \"ym\" unit return?
- The months remaining after the complete years are removed.
- Why does DATEDIF return
- The start date is later than the end date.
Practice DATEDIF
The lesson DATEDIF: ages, tenure and elapsed time (module 10 of Everyday Excel) has checked DATEDIF exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample