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!

 

[shg]

So a 56 year old with 11 years of service is retirement

He was eligible a year earlier ...

 

[RonB1111]

So the revised logic is:
if hire age 45 or less, then 55th birthday,
if hire age less than 50, then hire date +10
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 +0

The formula for the revised logic would be:

=IF(DATEDIF(F2,G2,"y")<=45,DATE(YEAR(F2)+55,MONTH(F2),DAY(F2)),IF(DATEDIF(F2,G2,"y")<50,DATE(YEAR(G2)+10,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)+0,MONTH(G2),DAY(G2))))))

If you compare the 2 formulas, you'll see where I changed the 35 to 45, the 20 to 10, etc.