Anniversary Date

[Guest]

How do you calucate employee anniversary dates in excel?

 

[Guest]

Tonnia
This will give you thier 5th anniversary date

=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)

Assumes date start is in A1. Adjust for other anniversaries

Good Luck
Mark Graesse
(e-mail address removed)

----- Tonnia wrote: ----

How do you calucate employee anniversary dates in excel?

 

[Norman Harker]

Hi Tonnia!

With the joining date in A1:

Try:
=(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)))
Returns the anniversary date this year.

If this year was not a Leap Year, then if the employee joined on
29-Feb the anniversary date would be returned as 1-Mar.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

I would like to create a formula to show me the next review date fo
employees. We do reviews every six months, so I would need a formula t
figure out how to show me when their next 6 month anniversary woul
be.

Any ideas?

Thanks in advance for any replies.

darro

 

[Norman Harker]

Hi djeans!

This is easy to ask but as a formula it's quite difficult.

I have start date in A1 and today's date in B1

Try:
=IF(DATE(YEAR(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MON
TH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))),MONTH(IF(B1<DATE(
YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR
(B1)+1,MONTH(A1),DAY(A1))))-6,DAY(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))
)))>B1,MIN(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(
A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))),DATE(YEAR(IF(B1<DATE(
YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR
(B1)+1,MONTH(A1),DAY(A1)))),MONTH(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))
))-6,DAY(IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1
),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))))),IF(B1<DATE(YEAR(B1),
MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MO
NTH(A1),DAY(A1))))

And even with this, you have a problem if the DoM of joining is >=29.

Life is a lot easier if you use "helper" columns! I used 3 to build
the formula:

E1
=IF(B1<DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1),MONTH(A1),DAY(A1
)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))
Returns the next anniversary date
F1
=DATE(YEAR(E1),MONTH(E1)-6,DAY(E1))
Returns 6 months before the anniversary date.
G1
=IF(DATE(YEAR(E1),MONTH(E1)-6,DAY(E1))>B1,MIN(E1,F1),E1)
Returns the next 6 monthly anniversary.

With these helper columns I can get over the DoM >=29 potential
problem by:

F1
=DATE(YEAR(E1),MONTH(E1)-6,MIN(DAY($E$1),DAY(DATE(YEAR(E1),MONTH(E1)-5
,0))))

G1
=IF(DATE(YEAR(E1),MONTH(E1)-6,MIN(DAY($E$1),DAY(DATE(YEAR(E1),MONTH(E1
)-5,0))))>B1,MIN(E1,F1),E1)

I'm sure that these formulas can be improved upon but they seem to
test OK so....

I think that a VBA approach might be a lot easier.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

Thanks Norman!

That has to be the longest Excel formula that I have ever seen.

You are truly an Excel Genius.

I tried entering dates higher than 29, and didn't seem to have any
problems that I could tell. Plus, it would be a shame not to use that
formula after you went to all that trouble.

Thanks again!

Darron

 

[Norman Harker]

Hi Darren!

The long version still suffered the defect of DoM >=29 and to correct
for that puts it beyond the formula length limit.

I'd recommend the helper column approach; apart from being inclusive
of DoM coverage, it's a lot easier to understand.

Alternative to formula would be a UDF but you'd have to use the same
logic as with building the formula.

On building; I cheated! I already had an anniversary formula and that
was adjustable for 6 monthly anniversary and then it was a question of
which to use.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

Can you explain the >=29 error?

I tested the formula with several dates and didn't seem to run into any
problems.

darron

 

[djeans]

Hopefully this is an easy one.

I was trying to write a formula to let me know if the next review was
an annual salary review, or a 6 month performance review.

In this example, Cell C2 is the hire date, and cell E2 is the next
review date. (the result of the long formula)

This is the formula that I tried.

=IF(MONTH(DAY(E2))=(MONTH(DAY(C2))),"Salary Review","6 Month Review")

The idea, was if the next review date had the same month/day as the
hire date, it would return the result, Salary Review, if not 6 month
review, but for some reason, it returns Salary Review for all of my
records.

Any help would be greatly appreciated.

thanks

 

[beeawwb]

I may or may not know what I'm talking about, but, without loading int
excel and checking, aren't you donig this?

"=IF(MONTH(DAY(E2))=(MONTH(DAY(C2))),"Salary Review","6 Mont
Review")"

=(IF(The Month(Of the Day(In E2))=(The Month(Of The Day(In C2)))
"Salary", "6 Month")

Wouldn't you need to do

=IF(MONTH(E2)=(MONTH(C2)),"Salary Review","6 Month Review")

I'm gonna go check that in Excel.

-Bo

 

[beeawwb]

Yeah, this makes it work.

=IF(MONTH(E2)=(MONTH(C2)),"Salary Review","6 Month Review")

-Bob

 

[rob nobel]

But that doesn't take care of the day which was also part of the requirement
me thinks.

 

[beeawwb]

I know, it's just all I could think of with my limited knowledge.

Learning as I go here. Can you embed code like that for Day and Month
functions? As I understand it, =(Day) will give a number 1 through 7,
and =Month will give a number 1 through 12, assuming that a date is
inputted. I have no idea what happens if you put a 1-7 in =Month. Ie,
=Month(6).

*checks*

It returns a "1" each time. So, maybe the way to do it, is to use
Nested If's... Or an IF/AND? Do those exist?

Like...

=IF(MONTH(E2)=(MONTH(C2)),(IF(DAY(E2)=(DAY(C2)),"Salary Review","6
Month Review")),"6 Month Review")

Is my syntax for that correct? I think it's donig what's asked when I
put it in Excel, but I'm not quite sure.

-Bob

 

[beeawwb]

I tried this as well.

F2 is

=AND(MONTH(E2)=MONTH(C2),DAY(E2)=DAY(C2))

And G2 is

=IF(H2=TRUE,"Salary Review","6 Month Review")

Is that a better way to do it?

-Bo

 

[djeans]

That worked like a charm.

I am not very good at writing the formulas, obviously.

I really appreciate the help.

Darron

 

[rob nobel]

May need an expert on this. My comment only reflected the fact that the
question had not been answered and I was interested in a correct result
myself.
Rob

 

[rob nobel]

and combining those....
=IF(AND(MONTH(E4)=MONTH(C4),DAY(E4)=DAY(C4))=TRUE,"Salary Review","6 Monthly
Review")
Rob

 

[Norman Harker]

Hi djeans!

Try it on employment commenced 31 August.

Incidentally, the formula might be made a tad shorter using EDATE but
that makes use of the file dependent upon Analysis ToolPak being
installed and selected as an Addin.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi djeans!

Not too difficult. I get:

=IF(MONTH(E2)=MONTH(C2),"Salary Review","6 Monthly Review")

Seems to test OK.

What does it say?

If the month of the 6 monthly review is the same as the month of
joining it must be the (Annual) salary review. If it isn't then it
must be the 6 monthly performance review.

There's no need to test for the day because the day is not the
decider. In other circumstances you'd need to use a more complicated
formula for determining the anniversary date but here the formulas
that build E2 have done this.

BTW the joining on 31-Aug-2000 confirms the need for the helper
formulas given before. It won't just be an inconvenience and peculiar
date but will also screw up this formula.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[djeans]

I entered a hire date of 8/31/00 and the formula returned a next revie
date of 3/2 - 6 month review. Is there an error I am not seeing?

I am using Office for Mac if that makes a difference.

I hate to abuse a great resource, but If I could ask one more question
It would be a great help.

I also would like to have a column (or two if necessary) that will tel
me how many years and months an employee has worked for us.

For example, if the employee was hired 2/19/92 and today is 1/14/04
would like it to tell me that they have worked for me 11 years and 1
months.
Is that possible?

You are all awesome.

Darro