To add years to date in google sheets you can use the EDATE formula in google sheets.
Here is how to add years to date in google sheets:
1. Open google sheets on your computer or mobile
2. List date in cell A1
3. In cell B1, list the number of years to add to A1
4. In column C1, Write =EDATE(A1, B1*12)
5. Press enter and now C1 will have A1 date+2 years
Once you have added 2 years to date in the C1 cell, drag the cell down to copy the formula for other cells.
Here is an animation showing this in action:
Table of content
Add years to date in google sheets with example
Let’s see an example of adding years to date in google sheets.
We have a list of dates in column A as you see below:
We want to add 1 year, 2 years, 4 years, and 6 years to these dates. To add years, follow the below steps:
Step-1: List dates in column A and the number of years to add in column B
Step-2: In cell C2, write the formula =EDATE(A2, B2*12)
Step-3: Press enter and you will have 1 year added to A2
Step-4: Drag C2 to all cells to apply the EDATE formula to other cells
Below is the final result after adding 1 year, 2 years, 4 years, and 6 years.
As you can see above,
Cell C2 has a date value that is +1 year of the date in cell A2.
Cell C3 has a date value that is +2 years of the date in cell A3.
Cell C4 has a date value that is +6 years of the date in cell A4.
Cell C5 has a date value that is +4 years of the date in cell A5.
The EDATE formula also takes negative numbers as input. When you pass a negative number to the formula, instead of adding the year, it will subtract the year. Let’s see it in detail.
Subtract years from the date in google sheets with example
For subtracting years from the date in google sheets, use the below formula:
=EDATE(date, -months*12)
For the same example above, now we can subtract 1 year, 2 years, 3 years, and 6 years from the dates in column A.
Below is the screenshot showing the example in action:
As you can see from the above,
Cell C2 has a date value that is -1 year of the date in cell A2.
Cell C3 has a date value that is -2 years of the date in cell A3.
Cell C4 has a date value that is -3 years of the date in cell A4.
Cell C5 has a date value that is -6 years of the date in cell A5.
FAQ
How to add years in google sheets?
To add years in google sheets, use the EDATE formula with a little variation. The formula is =EDATE(date, number_of_months_to_add*12).
How to remove years in google sheets?
To remove or subtract years in google sheets, you can use the EDATE formula and pass a negative number to the second parameter. The formula is =EDATE(date, – number_of_months*12)
Wrapping up
In this tutorial, you learned how to add years to date in google sheets. Also, you saw an example of how to subtract years from the date in google sheets.
Adding or subtracting years is an important calculation while working with dates. Now go ahead and use these formulas in your worksheet. Let me know in the comments if you face any issues.
Appendix
[1] SUM formula in google sheets – Link
Further Reading
New to google sheets ? Start here
More related to Formulas:
Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets
