Calculating a retirement date based on retirement rules?

[SoonerTexan]

Hi,

I need to calculate the retirement date of an employee based on the following 3 rules (in essence, the employee's retirement date is whichever rule the employee meets first)

At least 55 years old AND 20 years of service
OR
At least 60 years old AND 5 years of service
OR
At least 65 years old AND 1 year of service

I have Date of Birth in Column F and Hire Date in Column G.

I was able to hack something together when I had only two rules to deal with, but it was a very hard to follow IF statement. With three rules I'm having trouble. Any suggestions?

Thanks!

 

[RonB1111]

Try,

=IF(DATEDIF(F2,G2,"y")<=35,DATE(YEAR(F2)+55,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<40,DATE(YEAR(G2)+20,MONTH(G2),DAY(G2)),IF(DATEDIF(F2,G2,"y")<55,DATE(YEAR(F2)+60,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<64,DATE(YEAR(G2)+5,MONTH(G2),DAY(G2)),DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))))))

It is based on this logic:
if hire age 35 or less, then 55th birthday,
if hire age less than 40, then hire date +20
if hire age less than 55, then 60th birthday
if hire age less than 64, then hire date +5
all others = hire date +1

 

[SoonerTexan]

Thanks! It got me somewhere, but there are still some situations I'm trying to account for.

For example, one employee had a birth date of 8/1/1950 and a hire date of 10/1/2011. In this case, the result of the formula was 10/1/2016, when the result should have been 8/1/2015. In effect, the formula assumed he would hit the 2nd rule (60 years old, 5 years of service), but he turned 65 and hit the third rule (65, 1 year of service) before meeting the criteria of the second.

That's what I'm really having a hard time with on this one--the two variables of age and service year.

 

[lancerj017]

Old

=IF(DATEDIF(F2,G2,"y")<=35,DATE(YEAR(F2)+55,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<40,DATE(YEAR(G2)+20,MONTH(G2),DAY(G2)),IF(DATEDIF(F2,G2,"y")<55,DATE(YEAR(F2)+60,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<64,DATE(YEAR(G2)+5,MONTH(G2),DAY(G2)),DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))))))

New

=IF(DATEDIF(F2,G2,"y")<=35,DATE(YEAR(F2)+55,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<40,DATE(YEAR(G2)+20,MONTH(G2),DAY(G2)),IF(DATEDIF(F2,G2,"y")<55,DATE(YEAR(F2)+60,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<64,DATE(YEAR(F2)+5,MONTH(F2),DAY(F2)),DATE(YEAR(F2)+1,MONTH(F2),DAY(F2))))))

 

[RonB1111]

Thanks! It got me somewhere, but there are still some situations I'm trying to account for.

For example, one employee had a birth date of 8/1/1950 and a hire date of 10/1/2011. In this case, the result of the formula was 10/1/2016, when the result should have been 8/1/2015. In effect, the formula assumed he would hit the 2nd rule (60 years old, 5 years of service), but he turned 65 and hit the third rule (65, 1 year of service) before meeting the criteria of the second.

That's what I'm really having a hard time with on this one--the two variables of age and service year.

I see. I need to modify the logic of the 4th item as follows:
if hire age 35 or less, then 55th birthday,
if hire age less than 40, then hire date +20
if hire age less than 55, then 60th birthday
if hire age less than 64, then the lesser of hire date +5 or 65th birthdate
all others = hire date +1

The modified formula will be:

=IF(DATEDIF(F2,G2,"y")<=35,DATE(YEAR(F2)+55,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<40,DATE(YEAR(G2)+20,MONTH(G2),DAY(G2)),IF(DATEDIF(F2,G2,"y")<55,DATE(YEAR(F2)+60,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<64,MIN(DATE(YEAR(G2)+5,MONTH(G2),DAY(G2)),DATE(YEAR(F2)+65,MONTH(F2),DAY(F2))),DATE(YEAR(G2)+1,MONTH(G2),DAY(G2))))))

 

[SoonerTexan]

Well hot dang I think that is the answer! Thanks so much--that was a huge help!

 

[RonB1111]

You're welcome. Thank you for providing feedback.

 

[SoonerTexan]

Okay...how about one more. How would I change the formula with the following rule changes? (in bold)

At least 55 years old AND 10 years of service
OR
At least 60 years old AND 5 years of service
OR
At least 65 years old AND 0 years of service

 

[shg]

Is one eligible at 56 with 9 years service, or 59 with 6 years or 64 with 1 year?

In other words, are you eligible at 55 + years of service?

 

[SoonerTexan]

No, years of service is always required.

So a 56 year old with 11 years of service is retirement eligible, but a 57 year old with 5 years of service is not.