Calculate years between dates

In this example, the goal is to calculate the number of years between a start date in column B and an end date in column C. An easy way to solve this problem is to use the YEARFRAC function, which returns the number of years between any two dates as a decimal number.

YEARFRAC function

The YEARFRAC function returns a decimal number representing the fraction of a year between two valid Excel dates. the generic syntax for YEARFRAC looks like this:

=YEARFRAC(start_date,end_date)

For example, here are a few results from YEARFRAC with some hardcoded dates:

=YEARFRAC("1-Jan-2019","1-Jan-2020") // returns 1
=YEARFRAC("1-Jan-2019","1-Jul-2020") // returns 1.5
=YEARFRAC("1-Jan-2019","1-Jan-2021") // returns 2

In the worksheet shown, the formula used to calculate years between dates in cell D5 looks like this:

=YEARFRAC(B5,C5) // returns 1

As the formula is copied down, it returns the number of years between each start and end date as a decimal number.

Rounding results

Once you have the decimal number from YEARFRAC, you can round the number as you like. For example, you could round to the nearest whole number with the ROUND function:

=ROUND(YEARFRAC(A1,B1),0)

Whole years only

You might also want to keep only the integer portion of the result with no decimal value so that you are only counting whole years. In that case, you can wrap YEARFRAC in the INT function like this:

=INT(YEARFRAC(A1,B1))

The INT function removes the decimal portion of the number altogether. If you need to calculate years on an ongoing basis, for example, to calculate current age based on a birthday, see the example here.

Note: The YEARFRAC function has an optional 3rd argument that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The YEARFRAC page provides more information.