Let’s say you have a date, and in that, you want to add a year to the get a same date for the next year. In this tutorial, we will look at some examples to create a formula for this.
Add a 3 Years in a Date in Excel
Here we are using the date 1-Jan-2021 to make it more understandable to you, and you need to add three years to this date, so the date would be 1-Jan-2024. For this, we will use the EDATE function.
- First, you need to EDATE function in cell B1.
- After that, in the first argument, refer to cell A1.
- Next, 12 for specifying 12 months.
- Now, multiply 12 by 3 to specify 3 years.
- In the end, enter the closing parentheses and hit enter.
=EDATE(A1,12*3)
Add 5 Years in a Date
Now in the same way, if you want to add 5 years to the date the formula would be something like this.
=EDATE(A1,12*5)
Subtract 10 Years from a Date
And if you want to subtract a year, let’s say ten years, you can use a formula just like below.
Other Method to Add and Subtract Years from a Date
You can also use a combination of (Date, Year, Month, and Day) functions to add or subtract years from a date.
In this formula:
- The year function gets the year from the date.
- The month function takes a month from the date and then adds five years (60 months) to it.
- The day function takes a day from the date.
- And the date function, in the end, creates a new date using input from all the functions.
=DATE(YEAR(A1),(MONTH(A1)+(12*5)),DAY(A1))
You can also use the same formula to subtract the years from a date.
=DATE(YEAR(A1),(MONTH(A1)+(-12*5)),DAY(A1))