Calculate Anniversary

[PHnew]

I have a cell that contains 1-1-2011. (CalculationData!$A$12)

I have a list of employees where there is a column that lists their hire date. (in the example below L2)

I have a column where I need to show where they are currently at with respect to their anniversary date for the current year. In this column, I have the following formula. It seems to work for more tenured people, but not so much for people with less than 1 year or just having passed the 1 year mark.

=IF(D2="Active",DATEDIF(L2,CalculationData!$A$12,"y"),"none")

Example: One person's hire date was 1-25-10. The result in her anniversary column is 0; where it should be 1.

Solutions are appreciated.

 

[shg]

Maybe just ...

=IF(D2="Active", year(CalculationData!$A$12) - year(L2), "none")

 

[andrewman]

Dear PHnew,

Just =int((CalculationData!$A$12-$L2)/365.25)

Andrew

 

[barry houdini]

DATEDIF only counts whole years so 1-25-10 to 1-1-2011 is just over 11 months so the result in years is zero. If it should be 1 then at what point do you consider 1 year to have been reached, are you rounding to the nearest year?

 

[shg]

I read the question as "what anniversary will the employee have this year?"

 

[barry houdini]

Ah yes, that looks about right, shg, hard of reading........

 

[PHnew]

shg: That appears to solve my issue.

You are correct. That is the question I am asking: What anniversary will employee have this year?

Would you be so kind as to help me understand the differences between the formula I had and your and how they work?

If I remember correctly, I got my original answer from this board.

But isn't life always about tweaking things to find the right answer?

I appreciate all the help.

 

[shg]

Irrespective of the date you are hired, your first anniversary is in the following year, so there's no need to consider anything but the year.