Happy Anniversary Flag?

  • Thread starter Thread starter Danny Boy
  • Start date Start date
D

Danny Boy

Here is a formula that I have which works to produce a "Happy Anniversary"
flag:

=IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),"Happy Anniversary","")

The flag is triggered based upon the date entered into cell E4 (date of
original member registration). At the moment, the flag triggers annually, on
the members anniverary date, however it also triggers if I type in "todays"
date.

I'd like help in altering the formula somewhat so that the "Happy
Anniversary" flag does NOT appear when the actual date typed into cell E4 is
today (e.g. Jan 27, 2009), but only triggers annually on the actual date of
anniversary (e.g. Jan 27, 2010, Jan 27, 2011, etc).

In addition (if possible), I'd love for the flag to change with the years
(e.g Happy 1st Anniversary, Happy 2nd Anniversary, etc) if possible.

Thank you very much for any help given!
Best,

Dan
 
Hi,

Try

=IF(E4=TODAY(),"",IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),"Happy Anniversary",""))
 
Danny said:
Here is a formula that I have which works to produce a "Happy Anniversary"
flag:

=IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),"Happy Anniversary","")

The flag is triggered based upon the date entered into cell E4 (date of
original member registration). At the moment, the flag triggers annually, on
the members anniverary date, however it also triggers if I type in "todays"
date.

I'd like help in altering the formula somewhat so that the "Happy
Anniversary" flag does NOT appear when the actual date typed into cell E4 is
today (e.g. Jan 27, 2009), but only triggers annually on the actual date of
anniversary (e.g. Jan 27, 2010, Jan 27, 2011, etc).

In addition (if possible), I'd love for the flag to change with the years
(e.g Happy 1st Anniversary, Happy 2nd Anniversary, etc) if possible.

Thank you very much for any help given!
Best,

Dan
Click to expand...


=IF(AND(E4<TODAY(),DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),
"Happy "&YEAR(TODAY())-YEAR(E4)&IF(AND(MOD((YEAR(TODAY())-YEAR(E4)),100)>10,
MOD((YEAR(TODAY())-YEAR(E4)),100)<14),"th",IF(RIGHT((YEAR(TODAY())-YEAR(E4)),1)="1",
"st",IF(RIGHT((YEAR(TODAY())-YEAR(E4)),1)="2","nd",IF(RIGHT((YEAR(TODAY())-YEAR(E4)),1)="3",
"rd","th"))))&" Anniversary","")
 
Thank you for the help Glenn.

What I'd also like to do is create a counting formula (in cell G3), that
would indicate how many occurances of Happy 1st Anniversary, Happy 2nd
Anniversary, etc. are present in Column G (cells G4:G3500). I tried the
formula below (as an array), but it didn't work. I also tried it without the
array, and that didn't work either.

Again, the formula below would be contained in cell G3 for the purpose of
counting the occurances of the various Happy Anniversary Flags (Happy 1st,
Happy 2nd, etc).

Thanks much for any suggetions!

Dan

=SUM(--(IF(ISNUMBER(VALUE(SUBSTITUTE(LEFT(G4:G3500,2),"-",""))),VALUE(SUBSTITUTE(LEFT(G4:G3500,2),"-","")),"")<=10))
 
=COUNTIF(G4:G3500,"Happy*Anniversary")




Danny said:
Thank you for the help Glenn.

What I'd also like to do is create a counting formula (in cell G3), that
would indicate how many occurances of Happy 1st Anniversary, Happy 2nd
Anniversary, etc. are present in Column G (cells G4:G3500). I tried the
formula below (as an array), but it didn't work. I also tried it without the
array, and that didn't work either.

Again, the formula below would be contained in cell G3 for the purpose of
counting the occurances of the various Happy Anniversary Flags (Happy 1st,
Happy 2nd, etc).

Thanks much for any suggetions!

Dan

=SUM(--(IF(ISNUMBER(VALUE(SUBSTITUTE(LEFT(G4:G3500,2),"-",""))),VALUE(SUBSTITUTE(LEFT(G4:G3500,2),"-","")),"")<=10))
Click to expand...
 
Back
Top