How to Add or Subtract Years in Google Sheets

Need to add or subtract years from dates in Google Sheets? This guide covers two proven methods: the EDATE function (simplest) and the DATE function (more flexible). Both account for leap years automatically.

Adding or subtracting years in Google Sheets is not as straightforward as you would think. You can blame the solar system for this since it takes 365.256 days for the earth to orbit the sun. Fortunately, there are several methods that account for leap years. Let’s explore two different approaches to accomplish this task. Use the one that makes the most sense for your data.

These are useful for calculating future anniversaries, projecting payment dates, or analyzing historical trends.

Make a copy of the sheet with the examples to follow along.

Method 1 – EDATE Function

The simplest way to add years to a date is with the EDATE function, which lets you add or subtract months from a date. Since we want to add years, we’ll multiply the number of years by 12 to convert to years.

Let’s take a look at the function’s syntax before we use it: =EDATE(start_date,months). The two inputs are the start_date and number of months.

For example, if your date is in cell A2 and you want to add 4 years, the formula would be:

=EDATE(A2, 12*4)

The result in cell B2 shows that four years from February 15, 2022, is February 15, 2026. Seems obvious now, but getting there with the formula with a formula was a bit tricky, right?

Now let’s add a negative sign to the months input in EDATE function to subtract 4 years.

=EDATE(A2, -12*4)

Now, we know how to move by whole years earlier or later using EDATE. However, spreadsheet data comes in endless different formats. Let’s look at an alternative method that may make sense for you.

💡 Tip: Working with lots of Calendar events? Instead of manually typing event dates and details, use our Calendar Importer add-on to automatically pull events into Sheets. Then apply these formulas to shift dates, find patterns, or create custom views.

Method 2 – DATE Function

Another approach is using the DATE function combined with the YEAR, MONTH, and DAY functions. This method gives you more flexibility in manipulating the date components. To add 4 years to a date in cell A2, you would write:

=DATE(YEAR(A2)+4, MONTH(A2), DAY(A2))

Leap Year Treatment

⚠️ Be cautious when using the DATE and EDATE functions with a leap year’s February 29. For instance, consider the following results: using the DATE function to move one year forward from February 29 yields March 1, while the EDATE function results in February 28. While both outcomes are valid, select the appropriate function based on your requirements.

Apply to Multiple Cells

To apply any of these formulas to multiple cells, simply click and drag the fill handle (the small blue square in the bottom-right corner of the selected cell) down your column. Google Sheets will automatically adjust the cell references.

Bonus Tip – Formatting

When working with dates, ensure your cells are formatted correctly by selecting “Format > Number > Date” from the menu. This will display the result as a date rather than a number.

Need to import Calendar data? If you’re working with Google Calendar events, our Calendar Importer add-on automatically pulls them into Sheets. Free 7-day trial.

Related Articles