Date notifications

[pbt]

My boss has asked me to come up with something that will notify him of upcoming Birthdates and Anniversary dates for all employees in all branches of our company.

This is so he can extend his wishes to them. (what a nice boss eh)
He Also thought of an automatic email to notify him of when he should send his regards.

This is a new path for me to follow, but I have been reading the help files and doing countless searches in my attempt to do this.

I think I might have the email part down once I figure out the rest. Except that right now I can't figure out once the email is sent, not to send it again. That part I will post after I figure out this part that I have trouble with.

This is my layout:

Birthday.xls
	A	B	C	D	E	F	G	H
1	4/6/2007
2							B'DAY	YRS-SVC
3	BRANCH	NAME	DOB	HIREDATE	AGE	YRS-SVC	MESSAGE	MESSAGE
4	GSP	TOMCLANCEY	1/10/1953	4/4/1981	54	26
5	GSP	SIDNEYSHELDON	4/20/1961	10/26/1990	45	16	YES
6	GSP	ROSEMARYROGERS	6/8/1965	5/9/2001	41	5
7	J-FSP	STEVENKING	4/28/1940	5/4/1905	66	101	YES	YES
8	J-FSP	JUDITHMcNAUGHT	10/10/1936	6/19/2002	70	4
9	J-FSP	ROSEMARYROGERS	3/6/1971	5/1/1994	36	12		YES
10	L-IWS	JOHNGRISHAM	8/14/1946	12/22/1985	60	21
11	L-IWS	SCOTTTUROW	1/26/1946	2/14/1987	61	20
12	L-IWS	DANIELLESTEEL	5/1/1970	6/27/2004	36	2	YES
Sheet1

Columns "G & H'" is where I need some help.

I can not figure out what formula to put in there that will take Todays date, minus 30 days , and compare it to the DOB or the YRS -SVC, to produce a "YES"

From there I may need help on the auto email part of this project.

Any help, greatly appreciated.
h.h

Disclaimer names may be familar, but dates are random. No offense intended.

 

[pbt]

Okay, this is my attempt.

In G4 I put =IF(C4<=$A$1-30,"YES") and copied down.

This will give me a "YES" because all dates are less then the today minus 30 days.

I need help on only looking at the Month & Day.

h.h.

 

[Tom Urtis]

I see your company hired Steven King 35 years before he was born, now that's a proactive company.

In cell G4, enter and copy down to G12, and then copy and paste special formulas from G4:G12 into H4:H12

=IF(AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))<=30,TODAY()-DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))>=-30),"Yes","")


If there really is today's date in cell A1, you could use this:

=IF(AND($A$1-DATE(YEAR($A$1),MONTH(C4),DAY(C4))<=30,$A$1-DATE(YEAR($A$1),MONTH(C4),DAY(C4))>=-30),"Yes","")

 

[pbt]

Thanks Tom

I see your company hired Steven King 35 years before he was born, now that's a proactive company.

That was a typo. Didn't see it until after I posted.

I used the second formula and it works great. I always get confused on the Day, Month, Year formulas.

Harry

 

[barry houdini]

=IF(AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))<=30,TODAY()-DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))>=-30),"Yes","")

Hello Tom/Harry

You'll have a problem with the above formula when the month is December, e.g. if TODAY() [or A1] is 31st December 2007 then Tom Clancey's birthday is only 10 days away but the formula won't return "YES" because

TODAY()-DATE(YEAR(TODAY()),MONTH(C4),DAY(C4))

is the same as

12/31/2007-1/10/2007 = 355

Try this formula in G4 copied across and down

=IF(DATE(YEAR(C4)+DATEDIF(C4,$A$1-1,"y")+1,MONTH(C4),DAY(C4))-$A$1<=30,"YES","")

Note: "YES" will disappear the day after the anniversary

 

[Tom Urtis]

Thank you Barry.

For my own knowledge because I only develop projects without ever relying on the Analysis ToolPak, could you or anyone please provide a formula that returns the same results as yours without DATEDIF.

I have a UDF that does it so I am not asking for a VBA answer either, just a native formula without invoking the ToolPak.

Thanks again.

 

[barry houdini]

Hello Tom,

DATEDIF is a native Excel function, just not very well documented, see here

 

[Tom Urtis]

Barry - -

Thanks; I'm aware of DATEDIF and its functionality. My question was regarding its availability only when the anaysis toolpak is loaded, which I think is the case, based on this and other links that support my assumption.

http://www.bettersolutions.com/excel/EDH113/LD711511811.htm


My question was and still is, what formula, using functions other than DATEDIF or any other functions that rely on the analysys toolpak being loaded, could return the same result as the formula you earlier provided?

 

[pbt]

Thanks Barry,

I put your formula in and did testing. It works they way I wanted.

I didn't notice at first that Tom's formula was giving a "YES" with a 30 day swing in both directions. That is 30 days prior and 30 days before.

The intent is to give a B'day or YRS - SVC greeting before the date not after.

Thanks again to both of you for your time.
Harry

Now to work on the automatic email part of this.

 

[barry houdini]

My question was regarding its availability only when the analysis toolpak is loaded, which I think is the case

Hello Tom,

I don't believe DATEDIF is part of the Analysis ToolPak, that's why I posted Chip's link, as he says "Datedif....is a normal worksheet function".

I have seen others refer to it as part of the Analysis ToolPak too, but the ultimate proof, I think, is that if you uninstall Analysis ToolPak - DATEDIF still works

Note: you could probably achieve the above more simply with the following formula, also using DATEDIF

=IF(DATEDIF(C4,$A$1-1,"yd")>333,"YES","")

although you may get some small discrepancies with leap years that means you'll get "YES" a day earlier