Happy Anniversary Flag?

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
 
S

Shane Devenshire

Hi,

Try

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

Glenn

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


=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","")
 
D

Danny Boy

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))
 
G

Glenn

=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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top