# 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.

##### Bill Jelen is the author / co-author ofMrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.