Excel calculates the difference between two dates in years with the DATEDIF function, using "Y" as the unit code: =DATEDIF(A1, B1, "Y") returns the number of completed whole years between the dates in cells A1 and B1. The DATEDIF function is the standard, built-in way to express a date gap in years, months, and days the way people actually say it: 1 year, 2 months, and 5 days. You can pair it with "YM" to get the leftover months after whole years and with "MD" to get the leftover days, so a single family of DATEDIF calls covers the full years-months-days breakdown that most projects, contracts, and HR records actually need. DATEDIF has shipped with Excel since the 1990s and works in every modern version, including Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, even though Microsoft never added it to the official function reference. Below is the full DATEDIF unit reference, the exact formulas for years, months, and days, the alternative YEARFRAC method, and a free online alternative that gives the same answer without touching a spreadsheet.
Use the DATEDIF Function to Get Years Between Two Dates
DATEDIF stands for "date difference" and takes three arguments in this order: the start date, the end date, and a unit code wrapped in quotes. The unit code tells DATEDIF whether to return the gap in whole years, whole months, or whole days, and it also has three "leftover" codes for the pieces that don't make up a complete year or month. The most common call is =DATEDIF(A1, B1, "Y"), which returns the number of completed whole years between the two cells.
The function does not round. It returns the number of completed whole years only, the same way you would say "she is 34" rather than "she is 34.7". If you want a fractional year, such as 34.7, you need YEARFRAC instead, which is covered later in this article. For HR records, contract lengths, lease terms, and subscription durations, the integer version from DATEDIF is what most readers actually expect.
Like every Excel date function, DATEDIF needs real date values in the cells you reference, not text strings. If a column holds dates as text, wrap them in DATEVALUE first or convert the cells to a Date format before you write the formula. The end date must be later than or equal to the start date; otherwise DATEDIF returns #NUM!. If you want a formula that gracefully handles either order, the datedifference calculator approach described below keeps the answer positive regardless of how the dates are entered.
How to Calculate Years, Months, and Days in Excel
The fastest way to get a clean years-months-days breakdown in Excel is to stack three DATEDIF calls in three adjacent cells. Each call uses a different unit code so the formulas share the work cleanly without overlap, and the three numbers always add back up to the original gap.
- Enter the earlier date in cell A1, formatted as a Date (for example 2000-01-01).
- Enter the later date in cell B1, also formatted as a Date (for example 2025-01-01).
- In cell C1, type =DATEDIF(A1, B1, "Y") and press Enter. This returns the number of completed whole years.
- In cell C2, type =DATEDIF(A1, B1, "YM") and press Enter. This returns the leftover months after the whole years.
- In cell C3, type =DATEDIF(A1, B1, "MD") and press Enter. This returns the leftover days after the whole months.
- Read the three cells together. The result is a years-months-days breakdown that reads naturally, such as "25 years, 0 months, 0 days".
Worked example with the inputs above: the formula =DATEDIF("2000-01-01", "2025-01-01", "Y") returns 25, because exactly 25 complete years separate the two dates. For that same input, the follow-up formulas =DATEDIF(A1, B1, "YM") and =DATEDIF(A1, B1, "MD") both return 0, because the two dates land on the same day and month 25 years apart. The three numbers always add back to the gap: start date plus 25 years plus 0 months plus 0 days lands precisely on the end date.
Other Year-Difference Methods: YEARFRAC and Simple Subtraction
Excel offers two other common ways to express the gap between two dates in years, and each is best for a different job. Use the table below to pick the right one before you write the formula.
YEARFRAC returns a decimal year, not an integer. The syntax is =YEARFRAC(start_date, end_date), with an optional basis argument that controls the day-count convention; basis 1 (actual/actual) is the one most users expect because it matches the real calendar. A 25-year-and-9-month gap with basis 1 comes back as roughly 25.75. YEARFRAC is the right choice for pro-rata calculations, depreciation schedules, interest accruals, and any situation that needs a fractional share of a year.
Simple subtraction gives total days, which you then divide by 365.25 to get a rough year count: =(B1-A1)/365.25. This method is fast but inaccurate near leap days and at the edges of months, because 365.25 is only an average year length. It is good enough for back-of-the-envelope estimates and unreliable for anything that has to reconcile to a calendar.
| Method | Best For | Output Type |
|---|---|---|
| DATEDIF with "Y" | Integer years, HR records, contracts, ages | Whole years only |
| DATEDIF with "Y" + "YM" + "MD" | Years-months-days breakdown the way people speak | Three integers that sum back to the gap |
| YEARFRAC (basis 1) | Pro-rata, depreciation, fractional ages | Decimal year |
| (B1-A1)/365.25 | Rough estimates only | Decimal year, drifts near leap days |
If your work is mostly about presenting a clean integer number of years, stick with DATEDIF. If you need decimals or proportional time, switch to YEARFRAC.
All Six DATEDIF Unit Codes in One Table
DATEDIF accepts six unit codes, and each one answers a slightly different question. Most users only need the first three, but the "leftover" codes are what let you build a years-months-days breakdown without doing any date arithmetic by hand.
| Unit Code | What It Returns | Typical Use |
|---|---|---|
| "Y" | Number of whole completed years | Tenure, age in years, contract length |
| "M" | Number of whole completed months | Total months on a subscription |
| "D" | Number of days between the dates | Raw day count for further math |
| "MD" | Days after the last whole month | Trailing days in a years-months-days breakdown |
| "YM" | Months after the last whole year | Middle field in a years-months-days breakdown |
| "YD" | Days after the last whole year | Year-to-day count that ignores the leftover year |
One pitfall worth flagging: the leftover codes "MD" and "YM" can return surprising results at month boundaries, because Excel skips whole months before counting leftover days. A span from March 31 to April 30, for example, can return 30 leftover days rather than the calendar-correct 30 days. The result is internally consistent for the way DATEDIF defines the fields, but if you need strict calendar correctness at month-end edges, the Date Difference Calculator handles the clamping for you.
Get the Same Years-Months-Days Answer Without a Spreadsheet
When you need the years-months-days gap fast and the spreadsheet feels like overkill, the Date Difference Calculator produces the same breakdown in three steps: pick a start date, pick an end date, read the days, weeks, months, and years-months-days breakdown that updates instantly below the inputs. There is no formula to write and no column to format, and you can tick the weekdays-only option to split the span into working days and weekend days, which always add up to the total days.
The tool handles leap years and month-end clamping the same way DATEDIF does for whole units, but it adds calendar-correct handling of the leftover days that occasionally trip Excel at month boundaries. Picking the same date twice returns zero, and entering the later date first still produces a clean positive answer rather than a negative one. Everything runs in your browser, so the dates never leave your device and nothing is uploaded or stored.
For related work in Excel, the How to Calculate Age in Excel: DATEDIF, YEARFRAC, INT guide walks through the same formulas applied specifically to age in years, with extra worked examples and a comparison against the INT(yearfrac) shortcut.
Related reading: Day of the Week Calculator Chart: Look Up Any Date.