Formula to Add Years to a Given Date

October 14, 2001 - by Bill Jelen

From this morning's mailbag:

I want to add years to a given date. For instance I want to add 5 years to 16th Nov.2001. How can I do that?

There are a lot of cool functions for dealing with dates in Excel. One of the most versatile functions is the =DATE() function. It accepts three arguments, Year, Month, and Day. You can, of course have calculations for those arguments and the function is incredibly flexible. For example, as expected:

=DATE(2001,10,14) will return October 14, 2001.

=DATE(2001,10,14+7) will return October 21, 2001

Here are some more complicated situations that Excel handles with ease:

=DATE(2001,10,14+30) will return November 13, 2001

=DATE(2001,10+6,14) will return April 13, 2002

Three other simple functions for dealing with dates are =YEAR() which will return the year from a given date, =MONTH() which will return the month number of a date, and =DAY() which will return the day of the month of a given date. You can combine all four of these functions together to solve today's problem. Assuming that you have a date in cell A1. The following formula is one simple way to come up with a date one year later.

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

Bob wrote with a similar problem. He has a column of dates in column A. Each date is the first of the month. Bob would like a formula that would extend the series. You can change the formula to:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

In Bob's case, you would not need a formula. If he has 1-Jan-2001 in A2 and 1-Feb-2001 in A3, he can highlight A2:A3, then click the fill handle in the lower right corner of the selection, drag down, and the series should correctly extend.