# Formula to add specific amount of years to a date

1. ## Formula to add specific amount of years to a date

Cell A1 has a date of, for example, 12/04/2005. I would like to put a formula in Cell B1 which will add 65 years to the date on A1, and should therefore = 12/04/2070.

Thus, when I enter dates down Column A, I can drag the formula down from B1, and Excel will automatically add 65 years to every date in Column A.

Effectively, this formula is A + 65 years = B. Unfortunately, Bill Gates doesn't agree!  Register To Reply

2. First, here is text from the help screen on dates

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

So your formula could be: =A1+(365*65), since each day equals to 1.  Register To Reply

3. Thanks Diver. Problem is, if I use the formula you have provided here, the date is 65 years in the future - but not to the month and date; which is probably because of leap years involved?  Register To Reply

4. ## Re: Formula to add specific amount of years to a date

I Use =DATE(YEAR(H6)+M6,MONTH(H6),DAY(H6)) for this work. In your case you would use A1 instead of H6 as the location of your date and M6 would need to be a field on that line where you enter the number 65 so =DATE(YEAR(A1)+C3,MONTH(A1),DAY(A1)) would work (you would type the number 65 into the field C3)
Capture.JPG  Register To Reply

5. ## Re: Formula to add specific amount of years to a date

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## Re: Formula to add specific amount of years to a date

anemae and Metoo7 thanks for the input, perhaps you missed that this thread is over 16 years old?  Register To Reply

7. ## Re: Formula to add specific amount of years to a date Thank you for your reminder, I will check the posting date in the future.  Register To Reply

8. ## Re: Formula to add specific amount of years to a date Originally Posted by Metoo7  Thank you for your reminder, I will check the posting date in the future.
No problem, I have done the same thing myself   Register To Reply