Add years to date

In this example, the goal is to use a formula to add or subtract a given number of years (n) to a date. There are two main ways to go about this in Excel. The first method uses the YEAR, MONTH, and DAY functions to take apart the date into separate components: year, month, and day. Then it adds n to the year and reassembles the components into a date again. The second method uses the EDATE function to adjust the date by expressing years as months. Both methods are explained below.

DATE + YEAR, MONTH, and DAY

The traditional way to solve this problem is to use the DATE function together with the YEAR, MONTH, and DAY functions. This is the method shown in the worksheet above, where the formula in cell E5 is:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

Working from the inside out, the YEAR, MONTH, and DAY functions are first used to "take apart" the date into separate components like this:

=YEAR(B5) // returns 1960
=MONTH(B5) // returns 3
=DAY(B5) // returns 8

These individual values are then returned to the DATE function, which reassembles the date, adding n to the year value along the way. With the number 10 in cell C5, the formula evaluates like this:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
=DATE(1960+10,3,8)
=DATE(1970,3,8)

The final result is the date 8-Mar-1970. If the date in B5 or the number of years in C5 are changed, the formula calculates a new result. This formula works fine, and it nicely demonstrates how you can take a date apart and reassemble it again in an Excel formula. However, there is an easier way, as explained below.

EDATE function

Another simpler way to solve this problem is to use the EDATE function. EDATE is designed to move a date by a given number of months. For example, to move a date in cell A1 forward by 6 months, you can use EDATE like this:

=EDATE(A1,6) // move forward 6 months

We can adapt the formula above to move a date by years by multiplying by 12:

=EDATE(A1,n*12)

Where n represents the number of years to move the date. To use this approach in the example shown, we can use a formula like this in cell E5:

=EDATE(B5,C5*12)

You can see this formula applied in the worksheet below. Notice the results are the same, except for the result in E12:

EDATE is smart about end-of-month dates. The original date in cell B12 is 29-Feb-2024. Because 2024 is a leap year, there are 29 days in February. When we add 1 year, EDATE knows that the resulting date should land on February 28, 2025, which is the last day in February of that year.

Summary

There are two basic ways in Excel to add years to a date with a formula: (1) the DATE function combined with YEAR, MONTH, and DAY functions, or (2) the EDATE function. The first method breaks down the date into its components, adjusts the year, and then reassembles the date. The second method, using the EDATE function, shifts the date by a specified number of months. Both methods work fine, but the second method is simpler.