Announcing birthday

  • Thread starter Thread starter Krzys
  • Start date Start date
K

Krzys

Please help,
I want formula to announce birthday in 10-1 days before event. Some
kind if formula: =if (someone birthday is in (10 to 1 days) before
today() , “birthday”, “ “)
Thanks for any help
Krzys
 
Assuming the names and dates are in a table M1:N20

Put all the names in A1:A20, and in B1 add
= IF(ISNA(VLOOKUP(A1,$M$1:$N$20,2,False))."","Birthday)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Or maybe.........
= IF(ISNA(VLOOKUP(A1,$M$1:$N$20,2,False)),"","Birthday")

Vaya con Dios,
Chuck, CABGx3



Bob Phillips said:
Assuming the names and dates are in a table M1:N20

Put all the names in A1:A20, and in B1 add
= IF(ISNA(VLOOKUP(A1,$M$1:$N$20,2,False))."","Birthday)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Krzys said:
Please help,
I want formula to announce birthday in 10-1 days before event. Some
kind if formula: =if (someone birthday is in (10 to 1 days) before
today() , “birthday”, “ “)
Thanks for any help
Krzys
Click to expand...

This will do it for you ...

Assuming that

1. today's date is in Cell A1 and
2. the birthday is in Cell B1

your formula is

=IF(AND(A1>=B1,A1-B1<=10),\"BIRTHDAY\",\"\")

NOTE: Cells A1 and B1 must be both "date" formatted.

Regards.
 
BenjieLop said:
This will do it for you ...

=IF(AND(A1>=B1,A1-B1<=10),\"BIRTHDAY\",\"\")

Regards.
Click to expand...

BenjieLop thanks a lot. But if I have
a1 - today
b1 -7/1/1988

Now I have problem. Your formula is working for dates in the same year.
I guess I should convert B1 from 7/1/1988 to 7/1/2005 to make your
formula working. how to do this?
Thanks Krzys
 
Krzys said:
BenjieLop thanks a lot. But if I have
a1 - today
b1 -7/1/1988

Now I have problem. Your formula is working for dates in the same year.
I guess I should convert B1 from 7/1/1988 to 7/1/2005 to make your
formula working. how to do this?
Thanks Krzys
Click to expand...


The formula that I gave you works fine with me even if the years are
not the same.

Other than that, I do not really know what else to say.

Regards.
 
Doesn't work on my site.

A1-today (7/9/2005)
B1- 7/7/2000
=IF(AND(A1>=B1,A1-B1<=10),"Birthday","")

Brings me empty cell
I will work on it.
Krzys
 
There may be a more elegant solution but, in the meantime, you can try
this.

In Cell C1, enter this formula

=TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),\"MM/DD\")

where A1 contains today's date.

Similarly, in Cell D1, enter this formula

=TEXT(DATE(YEAR(B1),MONTH(B1),DAY(B1)),\"MM/DD\")

where B1 contains the birthday.

NOTE: Cells C1 and D1 are helper columns so you can hide these

In Cell E1, enter the formula

=IF(AND(C1>=D1,C1-D1<=10),\"BIRTHDAY\",\"\")

Like I said, there may be a more elegant/efficient solution, but this
will work in the meantime.

Regards.
 
BenjieLop,
You are genius !!!
It works perfectly, It is what I was looking for. I was thinking about
the same but my knowledge is limited.
I put everything together and I got this monster :)
=IF(AND((TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"mm/dd"))>=(TEXT(DATE(YEAR(B1),MONTH(B1),DAY(B1)),"mm/dd")),(TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"mm/dd"))-(TEXT(DATE(YEAR(B1),MONTH(B1),DAY(B1)),"mm/dd"))<=10),"Birthday","")

Thanks again
Krzys
 
All you need is

=IF(AND(MONTH(A1)=MONTH(B1),DAY(A1)-DAY(B1)<=10),"Birthday","")

No need to TEXT it.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
<< Bob Phillips]All you need is

=IF(AND(MONTH(A1)=MONTH(B1),DAY(A1)-DAY(B1)<=10),"Birthday","")

No need to TEXT it.

--

HTH

RP
(remove nothere from the email address if mailing direct) >>



Bob,


Please note that the OP's condition is that there is a "Birthday"
message if the birthday is *10 days before * a certain date

Overall, your formula is indeed less cumbersome and more efficient.
However, there are two situations where it will not work:

1. *A1=July 2 & B1=June 30 *

The months are different yet the birthday in Cell B1 is still within 10
days of the date in Cell A1.

2. *A1=July 2 & B1=July 3*

Date in Cell B1, although is within 10 days of the date in Cell A1, is
already after the date in Cell A1.

... just thought I'd let you know.

Regards.
 
Back
Top