How to calculate just the year for a date format

[BNCOXUK]

I have a cell A1 with the value in the date format: 01/31/2008

In another cell B1, I put the number as 3, which means the number of years forward.

Now the question is: how can I get the new date which is just 3 years after the original date? Here in this example, the value is 01/31/2011.

You can see only the number is added to the year only, without affecting the date or month.

Can you give a general function / formula for this? (i.e., not necessarily 01/31/2008, it can be any date).

[ben_hensel]

To add three years?

A1= date
B1 = 3
C1 = A1 + DATE(B1,1,-1)

[BNCOXUK]

Hi Ben,

You code nearly worked. But if I input: 07/02/10 , 3

then the answer is: 07/01/13.

Thanks in advance.

To add three years?

A1= date
B1 = 3
C1 = A1 + DATE(B1,1,-1)

[ben_hensel]

Oh.... it's not handling leap-years the way you want....

Brute force it is.

A1 = date
B1 = 3
C1 = DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

This might not handle leap years exactly correct either... this is just adding "3" to whatever the year is and retaining the exact same number for month and day.

[BNCOXUK]

Thanks, Ben. This is also very good.

Oh.... it's not handling leap-years the way you want....

Brute force it is.

A1 = date
B1 = 3
C1 = DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

This might not handle leap years exactly correct either... this is just adding "3" to whatever the year is and retaining the exact same number for month and day.