Excel formula: Add years to date

Generic formula

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

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))

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.

Author

Dave Bruns

Related formulas

Add months to date

The EDATE function is fully automatic. Simply supply a valid date and a number of months and EDATE will return a new date. To subtract months from a date, supply a negative value. Adding years To move forwards and backwards in years from a certain...

Add days to date

Dates in Excel are just serial numbers. The number 1 represents January 1, 1900, the number 1000 is September 26, 1902, and so on. When you have a valid date in Excel, you and just add days directly. Day values can be positive or negative. For...

Related functions

Excel DATE Function

The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.

Excel YEAR Function

The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the...

Excel MONTH Function

The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the...

Excel DAY Function

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.