LinkBack URL
About LinkBacks
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
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
This will do it for you ...Originally Posted by Krzys
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
Houston, TX
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)
"Krzys" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Krzys
> ------------------------------------------------------------------------
> Krzys's Profile:
http://www.excelforum.com/member.php...o&userid=18404
> View this thread: http://www.excelforum.com/showthread...hreadid=385833
>
Or maybe.........
= IF(ISNA(VLOOKUP(A1,$M$1:$N$20,2,False)),"","Birthday")
Vaya con Dios,
Chuck, CABGx3
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>
> "Krzys" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
> >
> > --
> > Krzys
> > ------------------------------------------------------------------------
> > Krzys's Profile:
> http://www.excelforum.com/member.php...o&userid=18404
> > View this thread:
http://www.excelforum.com/showthread...hreadid=385833
> >
>
>
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
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
Last edited by Krzys; 07-09-2005 at 01:15 PM.
Thank you for the kind words ... just glad that I can help.
Regards.
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)
"Krzys" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Krzys
> ------------------------------------------------------------------------
> Krzys's Profile:
http://www.excelforum.com/member.php...o&userid=18404
> View this thread: http://www.excelforum.com/showthread...hreadid=385833
>
<< 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks