Countdown Birthday Flag Options: Help Needed!

[Danny Boy]

Someone kindly gave me this beautiful formula that creates a countdown to a
members birthday at our fitness club. On the actual birthday, a flag appears
which states "Happy Birthday-Send Letter".

=IF(TEXT(E4,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday-Send
Letter",(TEXT(E4,"d-mmm-")&(YEAR(NOW())+(TEXT(E4,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())

Is there a way of altering this formula so that the flag "Happy
Birthday-Send Letter" appears between days 10-1, and on the actual Birthday,
the flag changes to "Happy Birthday". This was (as was pointed out to me), we
can send the letter to our member PRIOR to their birthday.

Thanks for the help!

Dan

PS: I posted this earlier, but I fear it got lost among other responses to a
different question!

 

[Rick Rothstein]

You should stick with your original thread; I posted a formula there for
you. To save you the trouble of looking for it, here is what I posted back
there...

I think this formula will do what you want (at least my tests indicate it
will)...

=IF(TEXT(E4,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy
Birthday",(TEXT(TODAY(),"d-mmm-")&(YEAR(TODAY())+1))-(TEXT(E4,"d-mmm-")&(YEAR(TODAY())+(TEXT(E4,"mmdd")<TEXT(TODAY(),"mmdd"))))&IF((TEXT(TODAY(),"d-mmm-")&(YEAR(TODAY())+1))-(TEXT(E4,"d-mmm-")&(YEAR(TODAY())+(TEXT(E4,"mmdd")<TEXT(TODAY(),"mmdd"))))<=10,"
- Send Letter",""))

By the way, I changed the output to match what I think you originally asked
for. My original formula showed the number of days that have passed since
the birthday... I think you wanted the number of days *until* the next
birthday. The above formula shows that number along with the text messages
you asked for. Try it out and let me know if it works correctly and if the
number I now show is what you really want.

 

[muddan madhu]

may be this ?

=IF(E4>TODAY(),365-(E4-TODAY()),IF(E4-TODAY(),IF(E4<TODAY(),IF(TODAY()-
E4<=10,TODAY()-E4&" days - send letter","still "&TODAY()-E4&" days to
go"))))

 

[Danny Boy]

Hi Rick:

I think this formula did the opposite, as when I tested it with Feb 1, 1957,
I got a value of "359" (as opposed to 8). When I tested it again with Jan 25,
1957, I got a value of "1-send letter" (as opposed to "364"-given that the
date of Jan 25 went by yesterday).

Any additional clarification would be helpful.

Best,

Dan

 

[Junaid]

I changed the <= 10 with >355 it works, its mean it will count the days and
when 355 days will pass it will display "# of days - send the letter".

 

[Danny Boy]

Hi Junaid:

If I understood your change correctly I made the replacement below, however
I am still obtaining the same result in testing:

=IF(TEXT(E4,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy
Birthday",(TEXT(TODAY(),"d-mmm-")&(YEAR(TODAY())+1))-(TEXT(E4,"d-mmm-")&(YEAR(TODAY())+(TEXT(E4,"mmdd")<TEXT(TODAY(),"mmdd"))))&IF((TEXT(TODAY(),"d-mmm-")&(YEAR(TODAY())+1))-(TEXT(E4,"d-mmm-")&(YEAR(TODAY())+(TEXT(E4,"mmdd")<TEXT(TODAY(),"mmdd"))))>355,"
- Send Letter",""))

When I type in a sample Birthdate (Jan 25, 1957) I receive a value of
"1-send letter" in cell G4, as opposed 364 (which would be the correct answer
given that the next birthday is a year away).

When I type in a sample Birthdate (January 27, 1957) I receive a value of
364 as opposed to "1-send letter" (given that the birthday in this example is
only 1 day away).

Still seems to be doing the opposite of what I need. Any further suggestios
would be greatly appreciated!

Best,

Dan

 

[Rick Rothstein]

You are right... I screwed up the count down. Here is the formula I think
you want...

=IF(TEXT(E1,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday-Send
Letter",((TEXT(E1,"d-mmm-")&(YEAR(NOW())+(TEXT(E1,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())&IF(((TEXT(E1,"d-mmm-")&(YEAR(NOW())+(TEXT(E1,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())<=10,"
- Send Letter",""))

 

[Danny Boy]

Rick:

Thank you so much for that help. It worked! The only thing I'd like to do
now is add a piece of conditional formatting so that I can change the color
of the cell, when the countdown is between 1-10 days (with no coonditional
formatting on the actual Birthday). I tried a few different options, however
I was only able to create a conditional formatting scenario for Day 1 and Day
10, but not the days in the middle (2-9).

I made a slight adjustment to the title of the flags on the formula you gave
me, just as an FYI. Those adjustments are provided below if that would be of
help in comming up with the conditional formatting. Again, THANKS SO MUCH!

Dan

=IF(TEXT(E4,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday
Today",((TEXT(E4,"d-mmm-")&(YEAR(NOW())+(TEXT(E4,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())&IF(((TEXT(E4,"d-mmm-")&(YEAR(NOW())+(TEXT(E4,"mmdd")<TEXT(NOW(),"mmdd"))))-TODAY())<=10,"-Day(s) Till Birthday-Send Letter",""))

 

[Rick Rothstein]

Since the only values that will have a dash in them are those with the "Send
Letter" text, I am using the presence or absence of the dash as my
conditional. Select the entire Column "G" (that is the column I think you
said you were putting this formula in), go into the Conditional Formatting
dialog, select "Formula is" from the drop down and put this in the field
next to it...

=ISNUMBER(FIND("-",G1))

If you choose not to select the entire column, then select the subset of
cells you want to conditionally format and use the cell address for the
active cell in the selection in place of G1.