Explanation
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.
Related formulas
Add months to date
Add days to date
Related functions
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.
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 DATE function.
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 DATE function.
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 into another function, like the...
EDATE Function
The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past.
