MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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:


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 of
MrExcel LX – The Holy Grail of Excel Tips

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