Excel Date Difference in Years: Formulas That Work

To find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date in A2 and the later date in B2. For a decimal year value instead, use =YEARFRAC(A2,B2,1); the right choice depends on what you mean by “in years.”

Choose the kind of year difference you need

Excel can report a date interval as completed calendar years or as a fraction of a year. Those are different measurements, so decide which one fits the question before choosing a formula.

Completed years: Use this for ages, service anniversaries, or how many full years have elapsed. A span of 23 years and 364 days counts as 23 complete years.
Decimal years: Use this when a calculation needs a fractional value, such as converting a duration into years for an estimate. The result depends on the day-count convention selected.
Calendar-year labels: Subtracting the year numbers tells you how many year labels are crossed, not necessarily how many full years have passed. A date in December and a date the following January cross into a new calendar year despite being only weeks apart.

For a birthday or work anniversary, completed years are usually the intended result. For financial or scientific calculations, confirm the required day-count convention rather than assuming that every fractional-year formula means the same thing.

Calculate completed years with DATEDIF

Assume A2 contains 15-Jun-2000 and B2 contains 14-Jun-2024, entered as real Excel dates. The formula below returns 23, because the 24th anniversary has not yet arrived.

With the start date in A2 and the end date in B2, use:

=DATEDIF(A2,B2,"Y")

If B2 is changed to 15-Jun-2024, the same formula returns 24. That one-day change illustrates why subtracting the year numbers is not a reliable way to count elapsed years.

DATEDIF is still available in Excel for Microsoft 365, including on Windows, though it may not appear in the function autocomplete or be listed alongside newer functions. Its syntax is DATEDIF(start_date,end_date,unit). The quoted unit tells Excel what part of the interval to return. The function expects the start date to be earlier than or equal to the end date; reversing them results in an error.

Understand the DATEDIF units

For a simple year difference, the unit is "Y". DATEDIF also supports other units, which can be useful when you need a related measure from the same pair of dates.

"Y" returns complete years.
"M" returns complete months.
"D" returns the number of days between the dates.
"YM" returns remaining months after complete years are excluded.
"YD" returns days after complete years are excluded.
"MD" attempts to return days after complete months and years are excluded, but can produce unexpected results. Avoid relying on it for important calculations.

For example, with 15-Jun-2000 in A2 and 14-Jun-2024 in B2, the year unit returns 23. A months calculation answers a different question: it counts complete months, not years. Do not treat a result in months as a decimal number of years by simply dividing by 12 unless that approximation is suitable for your purpose.

Calculate a fractional year with YEARFRAC

YEARFRAC returns the interval between two dates as a fraction of a year. Its third argument, called the basis, chooses the day-count convention. With 1-Jan-2023 in A2 and 1-Jul-2023 in B2, there are 181 days between the dates. The formula below uses the actual/actual convention and returns approximately 0.49589.

With those dates in A2 and B2, enter:

=YEARFRAC(A2,B2,1)

The available basis values are 0 for US 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, and 4 for European 30/360. If you omit the basis, Excel uses 0. The conventions can return different fractions for the same dates because they define the length of a year or month differently. For instance, a 30/360 convention treats months as 30 days for its calculation, while an actual-day basis uses the actual dates.

YEARFRAC is not a replacement for DATEDIF when the requirement is “number of birthdays already reached” or “completed years of service.” A decimal such as 23.99 is not the same answer as 23 completed years, even if both describe almost the same elapsed time.

Prepare date cells and interpret the result

Excel formulas work best when both inputs are genuine dates, not text that merely looks like a date. Excel stores dates as serial values and displays them according to the cell’s number format. If a date was imported from another system, check that Excel recognized it: changing its display format may not convert text into a date value.

To check a result, temporarily format the output as General or Number. If it shows a date instead of a count, the formula result cell is formatted as a date. On Windows, select the result and use Home > Number Format to choose General or Number. The formulas themselves work in Excel for Microsoft 365 on Windows, Mac, and the web; the interface for changing formats can vary slightly by platform.

Keep the meaning of the start and end dates consistent across rows. For example, if you are measuring an employee’s completed service years, use the hire date and the same agreed reference date for every employee. A blank end date should not automatically be interpreted as today unless that is the intended business rule; otherwise, a report can change from day to day without an obvious explanation.

Handle reversed dates and edge cases

If dates might arrive in either order and you want the absolute number of completed years, put the two dates in A2 and B2. The following formula sorts them logically using MIN and MAX before calculating, and returns 23 for 15-Jun-2000 and 14-Jun-2024 in either cell order.

With the two input dates in A2 and B2, use:

=DATEDIF(MIN(A2,B2),MAX(A2,B2),"Y")

This intentionally discards direction. If the order matters—for example, to flag an end date that precedes a start date—do not use this version to conceal the reversal. Validate the dates or report an error instead.

Leap-day anniversaries need an explicit policy. Someone born on 29-Feb may be treated as reaching an age on 28-Feb or 1-Mar in a non-leap year, depending on the context and applicable rules. A formula cannot determine which policy is appropriate. Check the result around the relevant dates and apply the convention required for your use case. Also make sure a date entered in a regional format such as 03/04/2024 is interpreted as intended; it can mean different dates under different locale settings.

Common mistakes and troubleshooting

Subtracting year numbers: Subtracting YEAR values can say that a year has elapsed even when the dates are only days apart. Use DATEDIF for complete years.
Reversing the dates: DATEDIF requires the earlier date first. Check the inputs or deliberately use MIN and MAX if an unsigned interval is what you want.
Using text dates: An imported text value may cause an error or an incorrect result. Convert it to a real date value and verify how Excel interpreted the day and month.
Expecting a fraction from DATEDIF: The "Y" unit returns a whole number. Choose YEARFRAC when a decimal year is required.
Forgetting YEARFRAC’s default: Omitting the basis selects US 30/360, not actual/actual. Specify the basis when a particular convention matters.
Seeing a strange display: A correct numeric result can look wrong if the cell is formatted as a date, percentage, or another number format. Set the output to General or Number.

For a quick day-count check, subtract the earlier date from the later date; Excel date values can be subtracted to return elapsed days. That can help diagnose an unexpected result, but it does not by itself define a year fraction or a completed-year count.

When each method is the better choice

Use DATEDIF with "Y" for ages and anniversaries where the answer should be a whole number of years completed as of a particular date. Use YEARFRAC when a calculation explicitly requires part of a year and its convention is known. If the requirement is simply the difference between calendar years—for example, grouping records by the year printed on each date—compare the year labels rather than describing that result as elapsed time.

For a worksheet that tracks ongoing durations, put the reference or “as of” date in a clearly labeled cell rather than typing different end dates into each row. This makes the calculation auditable and easy to update. Decide how blanks, future dates, and leap-day anniversaries should be handled before using the result in payroll, eligibility, legal, or financial decisions.

Frequently Asked Questions

What is the simplest formula to calculate years between two dates in Excel?
For complete years, use DATEDIF with the "Y" unit. Put the earlier date first and the later date second. Use YEARFRAC instead if you need a decimal result and have chosen the appropriate day-count basis.

Why does subtracting the dates’ years give the wrong answer?
Subtracting calendar-year numbers ignores the month and day. From 31-Dec-2023 to 1-Jan-2024, the year labels differ by one, but only one day has passed. DATEDIF checks whether a full anniversary has occurred.

Can Excel calculate age in years?
Yes. Put the birth date first and the date to calculate age on second, then use DATEDIF with "Y". Use a fixed reference date if you need a report that remains reproducible rather than changing with the current date.

Why does YEARFRAC return a different value than I expected?
Check the basis argument. The default is US 30/360, whereas actual/actual and actual/365 use actual day counts with different year conventions. A fraction is only meaningful alongside the convention used to calculate it.

Does DATEDIF work in Excel for Mac and Excel for the web?
Yes, the formula syntax is the same in current Excel for Microsoft 365 on Windows, Mac, and the web. The ribbon and number-format controls may look different, but the date calculation does not need a platform-specific version.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.