Happy Birthday Formula Flag Needed

[Mike H]

Hi,

Try this and you get a countdown

=IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),"Happy Birthday
send a
letter",DATEDIF(TODAY(),EDATE(E4,(YEAR(NOW())-YEAR(E4))*12),"d"))

You also may wat to try an additional enhancement. Apply a conditional
format of

Cell value is - Between - 10 - 0
Pick a colour and with 10 day to go the cell turns your colour.

Mike

 

[Mike H]

I should have mentioned if you get a NAME error then you need to load
the analysis toolpak

Tools|Addins - Check the 'Analysis Toolpak'

Mike

 

[Danny Boy]

The formula (in column F) below does a nice job at providing me with
someone's current age (e.g. if Jan 24, 1988 is placed in cell E4, than cell
F4 would generate the person's age of 21).

=IF(E4="","",IF(DATEDIF(E4,TODAY(),"y")<1,DATEDIF(E4,TODAY(),"m")&"
months",DATEDIF(E4,TODAY(),"y")))

What I would like to do now is add a "Birthday Flag" type reminder. My
friend owns a fitness center, and he would like to send out a letter to his
client's on their birthdays. So, I need some type of formula that might
generate a "flag" in Column G when the birthday comes around (next to the
Column F current age column) stating "Happy Birthday-Send Letter".

I suspect that this flag would trigger off of the birthdate, entered into
Column E, however I just can't seem to wrap my brain around HOW to set this
type of formula up. Any help would be greatly appreciated:

Column E Column F Column G

Jan 24, 1988 21 Happy Birthday-Send Letter

 

[Mike H]

Hi,

One way in G4

=IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODAY())),"Happy Birthday send
a letter","")

Mike

 

[Rick Rothstein]

You could use this formula in G4...

=IF(TEXT(E4,"mmdd")=TEXT(TODAY(),"mmdd"),"Happy Birthday-Send Letter","")

but, just out of curiosity, wouldn't your friend want to send the letter out
in advance of the client's birthday instead of belatedly?

 

[Shane Devenshire]

Hi,

A separate issue: you might want to consider changing the formula you
currently use in F4 to read:
=IF(E4="","",DATEDIF(E4,TODAY(),"y"))&IF(DATEDIF(E4,TODAY(),"m")>0,"
"&DATEDIF(E4,TODAY(),"ym")&" Months","")

And you might add a formula such as this to column G

=IF(DATEDIF(E4,TODAY(),"ym")>=11,"Birthday","")

This will display the flag starting 1 month prior to the birthday. Or

=IF(DATEDIF(E4,TODAY(),"yd")>=358,"Birthday","")

which will display the flag beginning about 1 week before the birthday.

You could also add conditional formatting to change the cell or row to a
different color during the week prior to the birthday.

 

[Junaid]

Dear Mike,

This formula is ok but if we want to display the remaining days then what
will the solution. if true than "happy birth day send letter" if not then
ramaining days.

 

[David Biddulph]

You'll find details of the DATEDIF function at
http://www.cpearson.com/excel/datedif.aspx

 

[Junaid]

Although i checked the analysis tool but still the name error

 

[Mike H]

There are 2 and it's not the VBA one you want. having checked the correct
one, select the formula cell and tap f2 - Enter

Mike

 

[Junaid]

Mike Thanks it is working but again one problem that from Jan 01 to 24 it is
not working.

 

[Rick Rothstein]

This is a completely different approach from Mike's, but I think it will
work in all situations. Give it a try and see...

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

 

[Danny Boy]

I love the countdown formula, however I too have found what Junaid found.
Between Jan 1 to 24 the formula produces the #NUM! error. It works for days
other than this. If Mike or Junaid have a suggestion, the countdown formula
would be AWESOME!

 

[Danny Boy]

I actually spoke to soon, and just found that Rick's suggestion works, and
does NOT produce the #NUM! error.

Rick (or anyone else), is there a way that the formula can say create the
birthday Flag-Send Letter reminder between days 10, and up until the actual
birthday. This way the letter is generated (as was suggested previoulsy)
PRIOR to the actual birthday. Once the countdown gets to "0", the flag can
merely say "Happy Birthday".

Thanks, Dan

 

[Rick Rothstein]

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.

 

[Danny Boy]

Hi Rick:

I think this formula did the opposite of what I was expecting, as when I
tested it with Feb 1, 1957,
I got a value of 359, as opposed to a value of 8 (days to go until the
Birthday). When I tested it again with Jan 25,
1957 (a date that passed yesterday), I got a value of "1-send letter", as
opposed to 364. Any additional clarification would be helpful.

And yes Rick you are correct, I was looking for a countdown to the Birthday,
with the "send letter" flag appearing between 10-1 days prior (and then the
counter beginning all over again at 364 once the Birthday date passes).

Sorry for the double posts (I also responded accidently on the other thread
too). I haven't had my coffee yet!

Best,

Dan