Announcing birthday

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?action=getinfo&userid=18404
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 1:22:12 PM
excel 39879 articles. 2 followers. Follow

11 Replies
650 Views

Similar Articles

[PageSpeed] 23

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" <Krzys.1rwo6b_1120917901.2616@excelforum-nospam.com> wrote in
message news:Krzys.1rwo6b_1120917901.2616@excelforum-nospam.com...
>
> 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?action=getinfo&userid=18404
> View this thread: http://www.excelforum.com/showthread.php?threadid=385833
>


0
bob.phillips1 (6510)
7/9/2005 2:30:44 PM
Or maybe.........
= IF(ISNA(VLOOKUP(A1,$M$1:$N$20,2,False)),"","Birthday")

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OWqUMLJhFHA.1372@TK2MSFTNGP10.phx.gbl...
> 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" <Krzys.1rwo6b_1120917901.2616@excelforum-nospam.com> wrote in
> message news:Krzys.1rwo6b_1120917901.2616@excelforum-nospam.com...
> >
> > 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?action=getinfo&userid=18404
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=385833
> >
>
>


0
croberts (1377)
7/9/2005 2:42:27 PM
Krzys Wrote: 
> 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 ...

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


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 2:48:35 PM
BenjieLop Wrote: 
> This will do it for you ...
> 
> =IF(AND(A1>=B1,A1-B1<=10),\"BIRTHDAY\",\"\")
> 
> Regards.

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
------------------------------------------------------------------------
Krzys's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18404
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 3:18:18 PM
Krzys Wrote: 
> 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.


-- 
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 3:38:20 PM
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


-- 
Krzys
------------------------------------------------------------------------
Krzys's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18404
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 3:49:44 PM
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


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 4:21:43 PM
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?action=getinfo&userid=18404
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 5:09:05 PM
Thank you for the kind words ... just glad that I can help.

Regards.


-- 
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/9/2005 5:11:59 PM
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" <Krzys.1rwzaa_1120932300.8791@excelforum-nospam.com> wrote in
message news:Krzys.1rwzaa_1120932300.8791@excelforum-nospam.com...
>
> 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?action=getinfo&userid=18404
> View this thread: http://www.excelforum.com/showthread.php?threadid=385833
>


0
bob.phillips1 (6510)
7/11/2005 2:00:36 PM
<< 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.


-- 
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11019
View this thread: http://www.excelforum.com/showthread.php?threadid=385833

0
7/11/2005 8:57:24 PM
Reply:

Similar Artilces:

Utilities
Hi to all, I decided to publish some of my small tools (for free). They are collected at http://www.prosource.de/Downloads/utilities_en.html I use them for my daily work, may be others find them useful too. They are somewhat programmer centric (especially regarding their GUI :-)) At the moment only a color selector tool is there, but more will follow. Its rather tedious to write a meaningful description, so be patient with me :-) -- Ulrich Korndoerfer VB tips, helpers, solutions -> http://www.proSource.de/Downloads/ ...

Happy birthday, Publisher!
Did y'all know that next month will be Publisher's 15th birthday? If I had the time I'd make it a card.........anybody have a nice template? ;-) -- JoAnn Paules MVP Microsoft [Publisher] JoAnn Paules [MVP] wrote: > Did y'all know that next month will be Publisher's 15th birthday? If I had > the time I'd make it a card.........anybody have a nice template? ;-) Woohoo <:o) Another excuse to make cake! -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org I remember when Publisher 1.0 came out. Shortly after 1.0a was out, had a bunch of r...

Announcing birthday
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?action=getinfo&userid=18404 View this thread: http://www.excelforum.com/showthread.php?threadid=385833 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))."&qu...

Announce: Winmail Opener 1.3
We are pleased to announce that the Winmail Opener 1.3 is now available. Winmail Opener is a small and simple utility that allows you to view and extract contents of TNEF-encoded messages (infamous winmail.dat). Winmail Opener features: * Winmail Opener is absolutely free - no nag screens, no ads, no spyware, no time limit. * Supports RTF message text. * You can drag-and-drop TNEF-encoded files into Winmail Opener for opening them. * You can work with enclosed attachments as they are usual files on your disk: open, print, save, drag-and-drop them from Winmail Opener. * Supports command line...

URGENT: WINDOWS 8 ANNOUNCEMENT. July 2011!
Think Windows7 just came out & too young? Welcome to the brave future: Windows8! 1) Windows 8 To Contain New Anti-Hacking Software. Our group is on a mission to build a rich, powerful platform to help protect Microsoft products against piracy. Here is your chance to join a team of exceptional developers as we build an extensible cross-company platform to support this vision. This is a fast-paced group that rewards smart and motivated individuals. Some may consider anti-piracy measures similar to whistling in the wind, but that's certainly not the case. George has post...

Expand Announcements
Good day to all. In SP 2007 I'd like to expand my Announcement so the entire message can be viewed, that is I have one announcement and it's truncated but I'd prefer that it not be. Can't seem to figure this out in Views and perhaps there's simply a more appropriate web part for that. Recommendations? Thanks, AJ Hi AJ, This post on "Expanding the Size of Announcements Web Part" may be what you are looking for. http://sharepointsolutions.blogspot.com/2009/02/expanding-size-of-announcements-web.html. Johnathan "AJ" &l...

Filtering for birthday
How can I setup a filter to show all the contacts with birthday this month? I have tried the Advanced Filter option with "birthday = this month", but it will select only contacts with birthday in the CURRENT YEAR. Your help is very much appreciated! Thanks Try the method described on the following page: http://www.slipstick.com/calendar/filterbirthdays.htm -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:0bf501c39d3c$bffcf310$a101280a@phx...

Announcing a new Microsoft Dynamics GP Newsgroup
I'm pleased to announce the creation of the Microsoft Dynamics GP newsgroup for Developers! This group was created to reduce the amount of effort it takes to find relevant newsgroup discussions in this newsgroup. Please start using the new newsgroup immediately for any Microsoft Dynamics GP Developer questions and comments. Web-based Newsreader http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.dynamics.gp.developer&lang=en&cr=US Default Newsreader news://msnews.microsoft.com/microsoft.public.dynamics.gp.developer ...

Announcing a new chemistry search engine for SQL Server
Hello all, I wish to announce a new plugin for SQL Server designed to integrate various kinds of chemical search operations and other chemical functionality into ordinary relational databases. The product existed before as a cartridge for Oracle database, and now it is ported to Microsoft database technology. It scales well: databases containing 20 millions of molecules are not a problem. The product is written in a mix of C# and C++ and works inside SQL CLR engine. It has been successfully tested on SQL Server 2008 Express and Standard editions, both 32-bit and 64-bit. It...

Announcements on Dashboard
Hello, I finally found the table that the announcements are stored in (BusinessUnitNewsArticleBase). I am customizing a dashboard using Sharepoint, and I don't see this as an available entity. Are we missing something? We had thought that announcements would be an obvious thing to place on a sharepoint dashboard. I could use a data view web part to build a custom query, or I could use sharepoint's announcements, but it seems that this is such a natural item that I must be missing something. Any help would be appreciated. Thanks! Sarah ...

ANNOUNCE: Exchange Whitelisting tool PHIMF
Hi All I was looking for a quick n dirty (read free) whitelisting tool for the Exchange IMF and didn't find anything suitable (IMFcompanion doesn't really give itself to be run like a service) Therefore, I wrote my own. It's not particuarly flash, just picks out email from the archive folder and moves them to the pickup folder, but I've been using it in anger and seems to be pretty reliable. So I thought I'd share it with the world. The program is free with all the usual caveats. URL is http://www.chaplin.me.uk/programs/phimf.htm Happy New Year all.. Colin ...

[ANN] The Entourage Help Blog: "Date bug in Entourage 2008 causes birthday information loss"
Microsoft Entourage newsgroup poster Jean Fr�d�ric raised this question: > Each time I try to enter a birthday date in the contact, it is rewritten. > > Example: when I enter 12/06/1942, it turns to 12/06/39 The range of date > concerned goes from 1940 to 1950. > > In addition, when it syncs with iSync, there is a conflict between > Address Book and Entourage that can only be solved by overwriting the > Address Book data with Entourage's. > > Both problems made me lose 70% of my birthday information... > > Do you have any clue? This reminded me of ...

Announcement update
See the update on http://www.microsoft.com/money/default.mspx for some good news. They don't say if: * They will charge existing MoneyPlus users for this version; or * If this version will be crippled so that it will not accept manual downloads from Bank websites. After all �This version will allow you to keep your Money files and transactional history but will not allow access to online services or premium services.� could mean simply that there will be no more automatic downloads (including stock quotes), or that manual downloads would no longer work, or even that you ca...

Template for Theater announcement?
I'm looking for a template for Word or Publisher for a theater announcement. What I have in mind would be single unfolded sheet, blank except for a border containing masks (as symbolic of theater) and ribbon. Does anyone know of such a template? Thanks, Jim Go here for masks: http://office.microsoft.com/clipart/results.aspx?Scope=MC&Query=theater&CTT=6&Origin=EC010331121033 and here for ribbon http://office.microsoft.com/clipart/results.aspx?Scope=MC&Query=ribbon&CTT=6&Origin=EC010331121033 Draw a rectangle, select, click the line and border style, more lines, ...

announcement/thank you for attending
need an announcement thank you for coming to our first church ball ? -- Stefan Blom Microsoft Word MVP "cmcookie" <cmcookie@discussions.microsoft.com> wrote in message news:BD226841-62BB-4ABE-A4D4-2BB99C2535CD@microsoft.com... > need an announcement thank you for coming to our first church ball ...

Reminder of birthday
Hi All, I'm shiro,and trying to create my first project with ms access. I have a databse which contain birthday my family and my friends. Inside my table I keep OLE object ( photo.jpg ) of all my friends and family. I want to create a reminder form that can remind me about my friends birthday. I want to create aform that can be open automatically one week before the birthday and a textbox or label that can count backward the remaining days. May be it will look nice if the form opened 4 or 5 times a day,and the form also contain the photo of those who will celebrate their birth. Is it...

Announcements Permissions
This may seem really stupid, but I am completely unable to locate where I can assign permissions to specific users to post announcements in CRM. I have searched in various documentation and it all references the ability to do so, but thoughtfully neglects to say where. Perhaps its just me. A roadmap would be nice, if someone has one. Thanks connect to crm as the crm administrator click on home>settings>business unit settings>security roles and open the users role who you want to allow to add announcements click on the core records tabs and give them write access to the ne...

XML 2004 Program Announced
XML 2004 Program Announced - Discounted Registration Fees Available Now IDEAlliance and the XML 2004 Planning Committee have released the advanced program for XML 2004, http://www.xmlconference.org. XML 2004 takes place in Washington DC, at the Marriott Wardman Park Hotel, November 15-19. XML 2004 will cover such topics as Web Services, Publishing, e-Learning, Government Applications, Core Technologies, X-Query, and much more. Keynote speakers this year include Rod Smith, IBM Fellow/VP, Emerging Technologies, IBM, Michael Daconta, Chief Executive Officer, Smart Data Associates, Inc., Jack Gr...

Announcements in Crm
Can u set it that announcements shows to all users when they log on,as there is no workflow for announcements. is there any way to intergrate a ticker at the bottom of Crm to show information? not without making unsupported changes to the announcement section. ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Zombie9" <Zombie9@discussions.microsoft.com> wrote in message news:B0ED5A80-84DE-42E3-BDD2-9D25CD846E93@microsoft.com... > Can u set it that announcements shows to all users when they log on,as > there > is no w...

Macro for birthdays
I've imported 200 birthdays and anniversaries into my outlook 2007 contact list using a csv file. The dates are showing up in the details in the contact list but they are not showing up on the calendar. I've found that if I open each file, go to the details, click on the date, and save the contact it will show up on the calendar. However, I'm dreading doing this for 200 entries. Any advice? Thanks! "Malka" <Malka@discussions.microsoft.com> wrote in message news:B24DFEBC-C1BC-4488-A37A-A2056AE9EA19@microsoft.com... > I've imported 200 birthdays an...

how do i view all alert recipients for an announcements page?
I currently am not able to confirm who I have set up for alerts for an announcements page I own. Where do I go to see this information? Thanks, Bruce Im not sure you can do it from the web but you can dig down with SharePoint Manager http://spm.codeplex.com/Wikipage there is a 2010 and 2007 version... pretty neat tool, i havent used it a ton but i was able to find alert info in there.. "BruceE" <BruceE@discussions.microsoft.com> wrote in message news:8E49B16A-9B20-4795-B17A-674A3ECED704@microsoft.com... >I currently am not able to confirm who I have set...

Announcement security
Hi, How do I adjust the security role on announcement readability in CRM? For example, Business Unit A can only view the announcement for business unit A, and they cannot view the announcement of business unit B. Thank you. Joe, Unfortunately, Announcements are global. There is not a way to "filter" them based on Org Unit. Matt "Joe" <joselok@hotmail.com> wrote in message news:OvRUxr6eDHA.1888@TK2MSFTNGP12.phx.gbl... Hi, How do I adjust the security role on announcement readability in CRM? For example, Business Unit A can only view the announceme...

Announcement Coming: Holiday Card Contest 2003
Keep your eyes peeled for the upcoming details and full announcement for the 2nd Annual Microsoft Publisher Holiday Greeting Card contest! Stay tuned........ Brian Kvalheim Microsoft Publisher MVP http://www.kvalheim.org ...

Birthday stuff
Hi folks. I have a formula that gives a person's age. I want to add on below it that says something like: "There are 42 days until Joe's next birthday..." Where the apostrophe after BirthName is added. Or you could say the number of the age ala "XX-tyeth" type format, so if Joe was 50 (1960 year), it would say: There are 42 days before Joe (BirthName) becomes 51 years old. I know it is a simple variant of my formula. =CONCATENATE(BirthName," is ",(DATEDIF(DOB,TODAY(),"y") & " years, " & DATEDIF(DOB,TODAY()...

graduation announcement cards
Trying to find download to create and print graduation announcement cards through publisher. If you can't find any you like in Microsoft's bank of templates, you could design your own. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Fred" <Fred@discussions.microsoft.com> wrote in message news:4AA70707-7AB1-451F-87FD-9A8337E74D25@microsoft.com... > Trying to find download to create and print graduation announcement cards > through publisher. There are new ones here http://of...