2 Methods to add years to a date in Google Sheets:
- Use the EDATE function
- Use the DATE function
Let us begin by looking at these methods one by one.
Method #1: Use the EDATE Function
Technically, this function adds the number of months to a date. You can use the number of months corresponding to the number of years you want to add to get the desired result.
Step #1: Open a blank spreadsheet
On the Google Sheets site, click on the big plus sign in the box above Blank to open a new blank spreadsheet.
If you already have an existing spreadsheet, open that.
Step #2: Type in the original date
In the spreadsheet, type in the date to which you want to add years.
Step #3: Enter the formula
Double click on the adjacent cell to your date field, and type this formula:
=EDATE(start_date, [months])
- start_date is the original date to which you want to add years. In our example, it is A2.
- [months] is the number of months you want to add.
For example: The expiry date of a particular product is 5 years after the date of manufacture.
That means the input value for parameter [months] is 5×12 = 60 months.
Our example formula would then be:
=EDATE(A2, 60)
You can also add the desired date in the formula itself instead of referencing a cell.
For example: =EDATE(“1/4/2021”,60)
Step #4: Press Enter
After inserting the required values in the formula, press the Enter key on your keyboard to execute it.
Since we added 5 years to 01-04-2022, the result is 01-04-2027.
Method #2: Use the DATE Function
Instead of adding months, this method allows you to add years to a date directly. So there is no need to calculate the number of months corresponding to the years. This makes the method more straightforward and can save you valuable time.
Step #1: Open a blank spreadsheet
On the Google Sheets site, click on the big plus sign in the box above Blank to open a new blank spreadsheet.
If you already have an existing spreadsheet, open that.
Step #2: Type in the original date
In the spreadsheet, type in the date to which you want to add years.
Step #3: Enter the formula
Double click on the adjacent cell to your date field, and type this formula:
=DATE(YEAR(start_date)+x,MONTH(start_date),DAY(start_date))
- start_date is the original date to which you want to add years. In our example, it is A2.
- x is the number of years you want to add.
Our example formula would then be:
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))
For this example, we are adding 5 years to a date. To do that, 5 will have to be added along with the value for the year parameter in the formula.
Alternatively, you don’t have to use data in another field, but just add years in the formula itself.
For example, to add 5 years to a date, you would enter the formula:
=DATE(2022+5,4,1)
Which would then result in 1 April 2027.
Step #4: Press Enter
After inserting the required values in the formula, press Enter on your keyboard to execute it.
Note: With this formula, you can only add a complete year to a date. If you use figures like 1.2, 1.5, 1.9 etc, the formula will only consider the value 1, ignoring the rest.
Conclusion
These two methods help you get the same result using different approaches. If you want to add complete year(s) to a date, the second approach can come in handy. But if your requirement is the addition of partial years (e.g., a year and a half), then you can do so using the first method.