How to Add or Subtract Years in Google Sheets

Adam Steinfurth

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.

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)

Add four years to a date
Adding Four Years

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)

Subtract four years to a date with the EDATE function
Subtracting Four Years

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.

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))

Adding four years using the DATE function
Adding Four Years with the DATE Function

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.

Different treatment of leap year
Problems with Leap Year

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.

The Google Sheets fill handle at the bottom right of the active cell
Fill Handle

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.

One more thing…if you are subtracting dates to get a duration, try our TIMEDIF add-on! It calculates time differences with no formulas.

Related Articles