DATEDIF - Count completed years, months, or days
Calculate a service interval using the requested whole unit instead of estimating completed years from a day count.
By the end, you can
- Choose Y, M, or D for an interval.
- Distinguish complete units from approximate fractions.
- Recognize invalid date order and the MD limitation.
Before you start: Store valid numeric dates and understand DATE construction.
In this lesson
An anniversary changes the completed-year count
DATEDIF returns an interval in a specified unit. HR may need completed years of service as of a review date, not elapsed days divided by 365. Someone joining on 15-Apr-2021 has not completed five years by 14-Apr-2026, even though the review is nearly five calendar years later.
Syntax and units
=DATEDIF(start_date, end_date, unit)Use numeric Excel dates and a quoted unit string. The start date must not be later than the end date.
| Argument | Purpose | Example |
|---|---|---|
start_dateRequired | Beginning of the interval. | B2 |
end_dateRequired | Date at which the interval is evaluated. | C2 |
unitRequired | Y complete years; M complete months; D elapsed days. Other units describe remainders and need care. | "Y" |
Service as of a fixed review date
4| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Joined | Review date | Complete years |
| 2 | Anika | 15-Apr-2021 | 14-Apr-2026 | 4 |
| 3 | Dev | 01-Oct-2020 | 08-Oct-2026 | 6 |
| 4 | Meera | 08-Oct-2026 | 08-Oct-2026 | 0 |
=DATEDIF(B2,C2,"Y")The fifth anniversary has not arrived, so Anika has four complete years, not five.
- Use the stored join and review dates, not TODAY unless the policy calls for a changing review date.
- Choose "Y" for completed years.
- Check a row just before an anniversary and a same-day start/end row.
- Format the returned count as a number; it is an interval, not a calendar date.
Total months, elapsed days, and a remainder
=DATEDIF(B2,C2,"M")The review is one day short of 60 complete months, so the count is 59.
=DATEDIF(B2,C2,"D")The modern date difference is 1,825 days. This does not count working days or attendance.
=DATEDIF(B2,C2,"YM")After the complete years, the remaining complete-month component is 11.
Remainders, legacy compatibility, and limits
- Y and M count complete units, not rounded fractional years or months. D is the day interval between the dates.
- YM reports the month remainder ignoring whole years; YD reports a day difference ignoring years. Their interpretation is different from total M or D.
- An end date before the start gives #NUM!. Incorrect text-date interpretation can produce a wrong interval or an error.
- DATEDIF is retained for compatibility with older workbooks and may be less discoverable in formula help. It is available in older/current Excel, but its documented known issues matter.
Common mistakes and tips
DATEDIF is not implemented in the workspace and the current challenge catalog has no matching task. The interval examples are for Microsoft Excel.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.