Formula to add years to a given date

 

Past Tip of the Day

 

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.

By Bill Jelen on 14-Oct-2001
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.