Retirement Eligibility Date based on 3 Variables

I have been searching forums for help with creating a single cell that returns a result of "Eligible" OR the earliest date that an employee would be eligible for retirement from the organization based on any one of the three variables that are possible. So far I haven't located the help I need, so I joined this forum. Again, I wasn't able to locate the formula I'm seeking on this forum so I apologize if this has been answered somewhere already and would ask to be pointed in that direction. I really like Excel, but am still relatively new at using it. I have 2003 Version at home and 2010 Version at work.

The elgibility variables are:

1. 50 years of age AND at least 20 years of service

2. 55 years of age AND at least 10 years of service

3. Any Age AND at least 25 years of service

Since I haven't been able to figure this out, I made three columns in the attached sheet that return a result of True or False. I was able to manually extract the data I was seeking in this arrangement by viewing everyone's age and years of service and then looking back at dates of birth and start date. I'd like to create a final worksheet that automatically displays "Eligible" OR the earliest DATE that an employee would be eligible for retirement so that the time necessary to extract the data is reduced.

My organization also has another set of criteria for another set of employees:

1. 60 years of age AND at least 10 years of service

2. 65 years of age AND at least 5 years of service

3. Any Age AND at least 30 years of service

I sincerely appreciate any assistance that can be provided.

Dale

This ended up being the longest/ugliest formula I think I've ever created, but it should get the job done. It's a single-cell formula that will return "eligible" if any of the criteria are met. It will return the earliest date of eligibility if the person has not met any of the criteria mentioned.

I included a formula for both types of employees that you mentioned.

Eligibility for Retirement Sample.xlsx

Originally Posted by PacNW

Can't thank you enough clabulis! I would never have been able to develop that. I will try and manipulate the full list. Can I contact you about it if I encounter any issues?

Thank you again.

Dale

Definitely Glad to help.

clabulis,

Once again, I can't think you enough.

Over the past week I had an opportunity to play with a list of employees and create a TEST worksheet. The formula works exactly as needed for some, but not correctly for some others. I have attached the entire list with a few of the examples noted as I haven't been able to track down what in the formula is not working correctly for some.

Those with your GREEN formula on the list meet the criteria for:

1. 50 years of age AND at least 20 years of service

2. 55 years of age AND at least 10 years of service

3. Any Age AND at least 25 years of service

Those listed below the black bar with your BLUE formula are those employees who meet the criteria for:

1. 60 years of age AND at least 10 years of service

2. 65 years of age AND at least 5 years of service

3. Any Age AND at least 30 years of service

Thank you again for taking the time to create this formula and help me out.


Dale

WOW is what I'm thinking! Thank you to everyone for your assistance and so quickly. I think the post from clabulis is precisely what I was looking for. Sincerely appreciate everyone, thank you.

Dale

Looks like I mixed up a MIN with a MAX in the original formula. I made a new column called "EDITED" where you'll see my revised formula (compare it to the original by dragging your formula bar all the way down and clicking back and forth between my original column and the new one and you'll see the change I made).

All of my new results match up with your notes. My formula for row 7 seems to grab a slightly earlier date than the one mentioned in your note so I would just double-check everything just to be sure.

I've supplied edited versions for both types of employees as well.

Hopefully this is the only issue that we should come across. Let me know if you find anything else

Thank you! I will give it a good review and let you know. Thanks again, this is a big help to me and my organization right now.

Dale

Thank you! I will give it a good review and let you know. Thanks again, this is a big help to me and my organization right now.

Dale

Thank you Ron for taking the time to look at this again. I will also review this during the next week and see how it works. Thank you shg for verifying and assisting as well. I really appreciate everyone being so willing to help out!

Dale

Thanks for the catch Ron and shg. My formula is just way too messy....

Attached though is a revised copy of mine again (now matching Ron's and shg's results). There was one more MIN/MAX that I had messed up. Obviously at this point, I would go with one of the more efficient formulas as given above.

Here's my ugly formula transformed into the same logic but with much better formula syntax (thanks to Ron and shg above). It has been tested and matches all correct results:

=IF((DATEDIF(C3,TODAY(),"y")>49)*(DATEDIF(E3,TODAY(),"y")>19)+(DATEDIF(C3,TODAY(),"y")>54)*(DATEDIF(E3,TODAY(),"y")>9)+(DATEDIF(E3,TODAY(),"y")>24)=0,MIN(MAX(EDATE(C3,600),EDATE(E3,240)),MAX(EDATE(C3,660),EDATE(E3,120)),EDATE(E3,300)),"Eligible")

Use this one for the other group of eligible employees:

=IF((DATEDIF(C3,TODAY(),"y")>59)*(DATEDIF(E3,TODAY(),"y")>9)+(DATEDIF(C3,TODAY(),"y")>64)*(DATEDIF(E3,TODAY(),"y")>4)+(DATEDIF(E3,TODAY(),"y")>29)=0,MIN(MAX(EDATE(C3,720),EDATE(E3,120)),MAX(EDATE(C3,780),EDATE(E3,60)),EDATE(E3,360)),"Eligible")

Looks good to me! Whatever makes it easier for the OP

Thank you again to everyone! This was far beyond my Excel capabilities. This information was extremely helpful to my organization as we are trying to prepare for the future and it has created a tool that they can continue to use for years to come.

Can anyone recommend courses that would assist me in developing Excel skills and proficiency?

Dale