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]

Barry,

I tried and tested the [/b]shortened[/b] formula and it works just as well. The "1 day early" will not be a problem in this case.

This is just to give the boss a 30 (or 31) day window to give his regards before the date in question.

I think I will start a new thread for the second part (the auto email) since this one got off to a discussion on DATEDIF. I think if there will be anymore replies to this thread, it will be on the DATEDIF.

Thanks again
Harry

EDIT: This is the link to the auto email part
http://www.mrexcel.com/board2/viewtopic.php?p=1283993#1283993

 

[Tom Urtis]

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

Hello Tom,
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

Thanks again Barry, I got the same results as you did (using XP) and assumed that the Analysis ToolPak was required for DATEDIF because I've read so often from different resources that this was the case for all versions. I wonder if this is the case for only earlier versions. I've been purposely avoiding DATEDIF in my work, as I do with all ToolPak functions, because end-user versions and loaded utilities run the full unpredictable gamut.

If anyone reading this and using 97, 2000, or 2002 can test DATEDIF with and without the ATP installed, I'd like to know if it works either way on your system. Enter your birth date in cell A1, and paste this formula into any cell and let us know your results, again, with and without ATP installed.

="You were born "&DATEDIF(A1,TODAY(),"Y") & " years, " & DATEDIF(A1,TODAY(),"YM") & " months, and " & DATEDIF(A1,TODAY(),"MD") & " days ago."


Thanks again Barry.

 

[barry houdini]

Hi Tom,

I'm not sure that DATEDIF has ever been part of ATP but, in any case, there might be other reasons why you might want to avoid it. I recently had a discussion on here where I defended DATEDIF against charges that it produces unreliable results, especially when using "m" or "ym" as the third argument, but there's no doubt you can get some "odd" results.

For instance, if my date of birth were 31st December 1966 and today was 1st March 2007 then that fairly standard formula you show above will give the result

You were born 40 years, 2 months, and -2 days ago.

 

[Tom Urtis]

Interesting info, thanks Barry. When I find a property, method or function that is unreliable (example, UsedRange) I make a note of it and be sure to not use it, unless it's the only way to accomplish the desired result with mitigating conditions, such as maybe in this case, if month and day of dob are 12 and 31, that sort of thing.

This still brings me back to my original question for anyone reading, which is, can this result HH asked for be gotten by a native formula without DATEDIF or a UDF.