Bizarre date problem - help appreciated!!

[cdhoughton]

I have a cell containing a date field dd-mmm-yy. I am trying to put a formula in the next cell to add a year onto the original cell.

Say A1 is original date, A2 is formula cell to add a year.

I am using:

=DATE(YEAR(B6+1),MONTH(B6),DAY(B6))

However, it won't work for any other date except 31/12/xx - has anyone encountered this and if so how do I rectify it!!?!?

Cheers for any help offered!!!

 

[just_jon]

Try -

=DATE(YEAR(B6)+1,MONTH(B6),DAY(B6))

The way you had it was added 1 day, that's why it worked at end of year. Date values are -- integer part +1 each day...

 

[cdhoughton]

Cheers, thanks for that!!

 

[Ekim]

You could also use the EDATE function from the Analysis Toolpak add-in.

=EDATE(B6,12)

where 12 is the number of months from the start date.

Regards,

Mike