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

Hi,

One way in G4

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

Mike

"Danny Boy" wrote:

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

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.


"Mike H" wrote:

Hi,

One way in G4

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

Mike

"Danny Boy" wrote:

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

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

"Junaid" wrote in message
...
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.


"Mike H" wrote:

Hi,

One way in G4

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

Mike

"Danny Boy" wrote:

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

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

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

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

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?

--
Rick (MVP - Excel)


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

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(E 4,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.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Danny Boy" wrote:

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