Date notifications

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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 :-D 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
ABCDEFGH
14/6/2007
2B'DAYYRS-SVC
3BRANCHNAMEDOBHIREDATEAGEYRS-SVCMESSAGEMESSAGE
4GSPTOMCLANCEY1/10/19534/4/19815426
5GSPSIDNEYSHELDON4/20/196110/26/19904516YES
6GSPROSEMARYROGERS6/8/19655/9/2001415
7J-FSPSTEVENKING4/28/19405/4/190566101YESYES
8J-FSPJUDITHMcNAUGHT10/10/19366/19/2002704
9J-FSPROSEMARYROGERS3/6/19715/1/19943612YES
10L-IWSJOHNGRISHAM8/14/194612/22/19856021
11L-IWSSCOTTTUROW1/26/19462/14/19876120
12L-IWSDANIELLESTEEL5/1/19706/27/2004362YES
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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","")
 
Upvote 0
Thanks Tom
I see your company hired Steven King 35 years before he was born, now that's a proactive company.
:laugh: :laugh: 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
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top