Add Years to Date (Excel Formula)

puneet-gogia-excel-champs-09-06-23

- by Puneet

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.

add-date-in-excel
  1. First, you need to EDATE function in cell B1.
  2. After that, in the first argument, refer to cell A1.
  3. Next, 12 for specifying 12 months.
  4. Now, multiply 12 by 3 to specify 3 years.
  5. In the end, enter the closing parentheses and hit enter.
edate-function
=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.

add-5-years-in-date
=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.

subtract-10-years-from-date

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.

add-and-subtract-years-from-date

In this formula:

  1. The year function gets the year from the date.
  2. The month function takes a month from the date and then adds five years (60 months) to it.
  3. The day function takes a day from the date.
  4. 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.

formula-to-add-and-remove-years-from-date
=DATE(YEAR(A1),(MONTH(A1)+(-12*5)),DAY(A1))

Get the Excel File