Rule of 75 Retirement Calculation

[BAD]

Sorry! You are right! This formula works! Thank you so much!

 

[BAD]

My boss wants to know....

"how about if one of the factors ... years of service ... must be at least 8
years?
The rule is: Age plus years of service equal 75, with a minimum of 8 years
of service, i.e. a new hire who's 75 years old would not automatically be
eligible to retire on their hire date.

Do you any ideas on how to allow for additional years/days of service
elsewhere to count toward the service criteria, i.e. 7 years with the
ccompany plus 1 previous year with another eligible company would meet the 8
year criteria ... or 6 years with the company plus two one-year stints at two
other companies.

Any help would be appreciated.

 

[Spiky]

The simple answer is just wrap an IF around it. 8 years should be 2922
days, and I hope I grabbed the proper formula from this discussion, I
haven't tested it completely:

=IF(TODAY()-C2<2922,"Not Eligible",TEXT((27393.75+B2+C2)/2,"mm/dd/
yyyy"))

But for all the "another eligible company" parts, it gets more
complex. You'd have to have that data somewhere in the worksheet to
reference. No doubt it can be done, if those other dates are there.
So...what do you have?

 

[BAD]

Thanks Spiky. I tested with your formula but I got the "not eligible" text
in my field. Which is correct but I want the formula to tell me the date I
will be eligible.

 

[BAD]

I also have the date when the employee will complete 8 yrs of service if that
helps at all.

 

[~L]

To build on Spiky's formula:

=IF(TODAY()-C2<2922,"Not eligible prior to "&C2+(365.25*8)
,TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))

 

[BAD]

Hi ~L,

Your formula gives me Not Elibile prior to 41795. How do you turn that into
a date?

 

[~L]

Format the cell as a date (probably the best way) or:
=IF(TODAY()-C2<2922,"Not eligible prior to
"&TEXT(C2+(365.25*8),"mm/dd/yyyy"),TEXT((27393.75+B2+C2)/2,"mm/dd/yyyy"))