Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Happy Anniversary Flag?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Happy Anniversary Flag?
Hi,
Try =IF(E4=TODAY(),"",IF(AND(DAY(E4)=DAY(TODAY()),MONT H(E4)=MONTH(TODAY())),"Happy Anniversary","")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Danny Boy" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Happy Anniversary Flag?
Danny Boy wrote:
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","") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Happy Anniversary Flag?
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)) "Glenn" wrote: Danny Boy wrote: 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","") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Happy Anniversary Flag?
=COUNTIF(G4:G3500,"Happy*Anniversary") Danny Boy wrote: 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)) "Glenn" wrote: Danny Boy wrote: 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","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Happy Birthday Formula Flag Needed | Excel Worksheet Functions | |||
next anniversary date | Excel Discussion (Misc queries) | |||
Please Reply to this Thanks to all - Merry Xmas!, Happy Chanukah!, Happy ID | Excel Discussion (Misc queries) | |||
anniversary dates | Excel Discussion (Misc queries) | |||
Calculating Anniversary | Excel Discussion (Misc queries) |