Excel Formula for Anniversary Date by Current Year

Hey everyone,

Earlier I had help with an anniversary date, basically taking someones Month and Date of their work anniversary and setting the year to the current year. There is an additional step I'm trying to do, and trying to find out if its possible. Any guidance is greatly appreciated.

P4 (Anniversary Date): January 29, 1996
R4 (Current Year Anniversary): January 29, 2019 (using formula: DATE(YEAR(TODAY()),MONTH(P4),DAY(P4)) )

Can we alter the R4 formula to show it once that date is active. For example, its not January 29, 2018 yet, so how can we alter the formula to make it show January 29, 2017. Once the current calendar dateis January 29, 2018, then the anniversary date would become this. Basically we are making a range from their anniversary date, to the current date. The problem with the current formula is the anniversary date has not arrived yet. This might be a confusing scenario here, so hope my explanation makes somewhat of sense.

In short, the formula should always produce the current year's anniversary date, unless that date has not been reached. Then it would show the previous year (if possible).

Hi, to both!

Another option could be:
=EDATE(P4,12*(1+DATEDIF(P4,TODAY(),"y")))

Blessings!

Originally Posted by daddylonglegs

Looks good.....but I believe the requirement is to show the previous year's date unless this year's has been reached

Yeah! You're right. I missread the OP requirement. Blessings!