Is this a dumb way to create a switch for an anniversary?

[doctortt]

I'm sure there is a better way to do this.

L3 = the cell on the current worksheet with the date
Assumptions!$E$4 = the reference date that L3 has to check to determine whether L3 is an anniversary.


OR(L3=DATE(YEAR(Assumptions!$E$4)+1,MONTH(Assumptions!$E$4),DAY(Assumptions !$E$4)), L3=DATE(YEAR(Assumptions!$E$4)+2,MONTH(Assumptions!$E$4),DAY(Assumptions!$E $4)), L3=DATE(YEAR(Assumptions!$E$4)+3,MONTH(Assumptions!$E$4),DAY(Assumptions!$E $4)))

[p45cal]

try:
=AND(MONTH(L3)=MONTH(Assumptions!$E$4),DAY(L3)=DAY(Assumptions!$E$4))
This simply checks the month and day are the same, ignoring the year altogether. You may also want to add that one must be greater than the other:
Assumptions!$E$4 > L3
or
Assumptions!$E$4 < L3
leaving perhaps:
=AND(MONTH(L3)=MONTH(Assumptions!$E$4),DAY(L3)=DAY(Assumptions!$E$4),Assump tions!$E$4 < L3)

[mikerickson]

Perhaps
=IF(TEXT(Assumptions!$E$4,"md")=TEXT(L3,"md"),"same day/month","not")

But,...,leap year,...?? It would be a bit much to expect that nothing happens on Feb. 19.
The OP formula will do leap years better than any of the other offerings.

[Teeroy]

You could also try =IF(AND(NOT(MONTH(L3)-MONTH($E$4)),NOT(DAY(L3)-DAY($E$4))),"Anniversary","")
It's similar to the @p45Cal and @mikeericson approach in that it checks only that the the month and day.

Mike, I wouldn't worry about any anniversary on Feb 29, I'm not sure it is possible (legally anyway). Even someone born on this date gets to choose between Feb 28 and Mar 1 as their birthday (OK, the parents most probably do it :rotlaugh:).