Add years to date

To add a given number of years to a date, you can use a formula based on the DATE function together with the YEAR, MONTH, and DAY functions. In the example shown, the formula in E5 is:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

With the date 8-Mar-1960 in cell B5, and the number 10 in C5, the result in E5 is 8-Mar-1970.

Explanation

Ever needed to calculate someone's retirement date? Or figure out when a 30-year mortgage will end? Maybe you're planning project milestones for the next decade. Here's something that might surprise you: most Excel users tackle this problem with a formula that's 3x more complex than it needs to be. And worse, the solution can fail in leap years.

In this guide, I'll show you two ways to add years to dates: the traditional method that everyone learns first (and why it's not always ideal) and a lesser-known but simple and more robust formula that correctly handles the last day in February for leap years. You can use this formula to calculate things like:

Contract renewal dates
Project milestones
Financial maturity dates
Employee service anniversaries
Insurance policy renewals

Introduction

In this example, the goal is to use a formula to add or subtract a given number of years (n) to a date. The dates are in column B, and the years are in column C. There are two main ways to go about this in Excel. The first method uses the YEAR, MONTH, and DAY functions to take apart the date into separate components: year, month, and day. Then, it adds n to the year and reassembles the components to a date. The second method uses the EDATE function to adjust the date by expressing years as months. Both methods are explained below. But first, let's look at why we can't just add 365 days to a date.

Why not just add 365?

Before diving into the formulas, it's important to understand how Excel stores and handles dates. Every date in Excel is stored as a number, where January 1, 1900, is day 1, January 2, 1900, is day 2, and so on. As I write this on November 21, 2024, the date number is 45617. Given this system, you might wonder if we can't just add 365 days to move forward a year:

=A1+365

Or, with n as years, move my a multiple of 365:

=A1+(n*365)

While this may seem logical, the problem is not all years have 365 days. Leap years have 366 days, so using 365 will be off by one day after every leap year. Worse, the error compounds over time. Dates far into the future could be off by days or even weeks. We need a more robust method.

Method 1: DATE + YEAR, MONTH, and DAY

The traditional way to solve this problem is to use the DATE function together with the YEAR, MONTH, and DAY functions.

=DATE(YEAR(date)+n,MONTH(date),DAY(date))

In the worksheet shown, the formula in E5, copied down, looks like this:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))

Working from the inside out, the YEAR, MONTH, and DAY functions are used to "take apart" the date into separate components like this:

=YEAR(B5) // returns 1960
=MONTH(B5) // returns 3
=DAY(B5) // returns 8

These individual values are then returned to the DATE function, which reassembles the date, adding n to the year value along the way. With the number 10 in cell C5, the formula evaluates like this:

=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
=DATE(1960+10,3,8)
=DATE(1970,3,8)

The final result is the date 8-Mar-1970. If the date in B5 or the number of years in C5 are changed, the formula calculates a new result. This formula nicely demonstrates how you can take a date apart and reassemble it again in an Excel formula. It teaches you how the YEAR, MONTH, and DAY functions can extract the key parts of a date and how the DATE function can be used to create a date from these parts. However, it is a relatively complex formula, and it does have one flaw: If the starting date is February 29 in a leap year, and the end date is not in a leap year, the date will shift to March 1. See the result in cell E12.

There is a simpler way to add years to a date, and it doesn't have trouble with leap years. It's based on the EDATE function.

Method 2: EDATE function

A simpler way to solve this problem is to use the EDATE function. EDATE is designed to move a date by a given number of months. For example, to move a date in cell A1 forward by 6 months, you can use EDATE like this:

=EDATE(A1,6) // move forward 6 months

We can adapt the formula above to add whole years by multiplying by 12 in a generic formula like this:

=EDATE(A1,n*12)

Where n represents the number of years to add. To use this formula in the workbook shown, we enter this formula in cell E5:

=EDATE(B5,C5*12)

You can see this formula applied in the worksheet below. Notice the results are the same, except for the result in E12:

Adding years to a date with the EDATE function

EDATE is smart about end-of-month dates. The original date in cell B12 is 29-Feb-2024. Because 2024 is a leap year, there are 29 days in February. When we add 1 year, EDATE knows that the resulting date should land on February 28, 2025, and it automatically adjusts the result.

Summary

We looked at two methods to add years to a date with a formula: (1) the DATE function combined with YEAR, MONTH, and DAY functions, or (2) the EDATE function. The first method splits the date into its components, adjusts the year, and then reassembles the date. The second method, using the EDATE function, shifts the date by a specified number of months. Both methods work well. Both can handle dates far into the future, as well as negative year adjustments. However, I recommend the EDATE approach in most cases because it has some nice advantages: