How to Add/Subtract Years to a Date in Excel

In MS Excel, working with date-type values is a necessary requirement. It involves tasks like adding/subtracting days, months, or years to existing dates. In this article, I have presented 3 such ways to add or subtract years to an existing date in MS Excel.

Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

3 Ways to Add/Subtract Years to a Date in MS Excel

1. Using Simple Arithmetic Operations

Follow these steps to apply this solution:

  1. Select the cell you want to show the output to (In my case, cell E6).
  2. Enter the equal (=) sign in the cell. It should enable you to type formulas.
  3. Select the cell with the existing date (In my case, Cell D6). You can also enter the cell number manually.
  4. Add an addition (+, For adding years)  or subtraction (-, For subtracting years) operator after the cell reference (In my case, Addition +).
  5. Now, enter the number of years you want to add or subtract and multiply it by the number 365, and hit the Enter button (In my case, =D6+(2*365) ).Adding years using arithmetic operations

It will add the entered number of years (In my case, 2 years) to the existing date by adding the number of days to it. You can copy this formula and use it in other cells as well. See this article for copying formulas in Excel.

Breakdown:

D10+(2*365)

=> 01-01-18 + (730 days)

=> 01-01-18 + (2 years)

=> 01-01-20

[Note: This method will add or subtract the year specified, but it may show some changes in the days/months as there are leap years with 366 days, and this method uses days to perform its operation. In that case, you may check the other methods.]

2. Using the “EDATE” function

The EDATE function adds the entered number of months to the entered data and returns the value.

The syntax of the EDATE function is as follows:

=EDATE(start_date, months)

start_date: The existing date-type value.

months: The number of months to add.

[Note: You can enter negative values to subtract the months.]

Follow these steps to apply this solution:

  1. Select the cell you want to show the output to (In my case, cell E6).
  2. Enter the equal (=) sign in the cell. It should enable you to type formulas.
  3. Type in “EDATE” or select the EDATE function from the drop-down menu.
  4. Select the start_date and add a comma (In my case, Cell D6).
  5. Now, enter the number of years you want to add or subtract and multiply it by the number 12 (For subtracting year, enter -12), and hit the Enter button (In my case, =EDATE(D6,(5*12) ).Adding years using the EDATE function

It will add the entered years (In my case, 5 years) to the existing date by creating a new date with the given values. You can copy this formula and use it in other cells as well. See this article for copying formulas in Excel.

Breakdown:

EDATE(D6,(5*12)

=> 01-01-18 + (5*12 months)

=> 01-01-18 + (60 months)

=> 01-01-18 + (5 years)

=> 01-01-23

3. Using the “DATE” function

There are a number of functions in Excel for altering date values, but the DATE function is by far the most versatile and straightforward. It constructs a valid date from the individual year, month, and day values.

The syntax of the EDATE function is as follows:

=DATE(year, month, day)

year: The number of years for the date.

month: The number of months for the date.

day: The number of days for the date.

As this function creates dates with individual years, months, and days, you can simply input a date and increase or decrease the number of years you want by adding or subtracting that number to the year argument.

Follow these steps to apply this solution:

  1. Select the cell you want to show the output to (In my case, cell E6).
  2. Enter the equal (=) sign in the cell. It should enable you to type formulas.
  3. Type in “DATE” or select the DATE function from the drop-down menu.
  4. Type in “YEAR” or select the YEAR function from the drop-down menu. Enter the date’s cell as input and add a comma (In my case, Cell D6).
  5. Now, add or subtract years by adding or subtracting that number (In my case, YEAR(D6)+5).
  6. Repeat Step-4 for the MONTH and DAY argument and hit the Enter button (In my case, =DATE(YEAR(D6)+5,MONTH(D6),DAY(D6)) ).Adding years using the DATE function

It will add the entered years (In my case, 5 years) to the existing date by adding the number of years. You can copy this formula and use it in other cells as well. See this article for copying formulas in Excel.

Breakdown:

DATE(YEAR(D6)+5,MONTH(D6),DAY(D6))

=> DATE (18+5, 01, 01)

=> DATE (23, 01, 01)

=> 01-01-23

Conclusion

Excel allows you to alter dates in a variety of ways. I’ve discussed 3 methods for adding or removing years from a given date in this article. I hope you were able to solve your problem. Please leave a comment if you have any suggestions or questions. Thank you.


Further Readings

Chinmoy Mondol

Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo