Date notifications

pbt · Apr 6, 2007

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

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

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 · Apr 7, 2007

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 · Apr 7, 2007

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 · Apr 7, 2007

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 · Apr 7, 2007

=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 · Apr 7, 2007

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 · Apr 7, 2007

Hello Tom,

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

Tom Urtis · Apr 7, 2007

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 · Apr 7, 2007

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 · Apr 7, 2007

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

Date notifications

pbt

Well-known Member

Excel Facts

pbt

Well-known Member

Tom Urtis

MrExcel MVP

pbt

Well-known Member

barry houdini

MrExcel MVP

Tom Urtis

MrExcel MVP

barry houdini

MrExcel MVP

Tom Urtis

MrExcel MVP

pbt

Well-known Member

barry houdini

MrExcel MVP

Similar threads

Forum statistics

Share this page

Date notifications

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock