Summary

To add a given number of years to a date, you can use a formula based on the DATE function, with help from the YEAR, MONTH, and DAY functions.

In the example shown, the formula in D5 is:

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

Generic formula

=DATE(YEAR(date)+years,MONTH(date),DAY(date))

Explanation 

Working from the inside out, the YEAR, MONTH, and DAY functions extract those respective date components:

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

At the outer level, the DATE function simply reassembles the component values back into a valid Excel date. To add years to the date, we just need to add the value in C5 to the year component prior to reassembly:

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

The formula is then solved like this:

=DATE(1960+10,3,8)
=DATE(1970,3,8)
=8-Mar-1970

Note: if you need to add an "even" multiple of 12 months to a date (i.e. 12, 24, 36, 48, etc) you can use a much simpler formula based on the EDATE function. See: add months to a date.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.