Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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!

"Junaid" wrote:

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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

Happy Birthday Formula Flag Needed
 

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


"Rick Rothstein" wrote:

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