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(),"m mdd"))))&IF((TEXT(TODAY(),"d-mmm-")&(YEAR(TODAY())+1))-(TEXT(E4,"d-mmm-")&(YEAR(TODAY())+(TEXT(E4,"mmdd")<TEXT(TODAY(),"m mdd"))))<=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.

--
Rick (MVP - Excel)


"Danny Boy" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"Junaid" wrote in message
...
Mike Thanks it is working but again one problem that from Jan 01 to 24
it
is
not working.

"Mike H" wrote:

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" wrote:

Although i checked the analysis tool but still the name error

"Mike H" wrote:

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

On Sun, 25 Jan 2009 09:23:26 +1200, Mike H < wrote:

Hi,

Try this and you get a countdown

=IF(AND(DAY(E4)=DAY(TODAY()),MONTH(E4)=MONTH(TODA Y())),"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