Calcuate date of retirement based on combination of age and years of service.

[Susan Altemus]

I need to determine a date an employee would be eligible to retire with 85 points (this is a combination of age and years of service). 3 things I know: 85 points, birthdate and date of hire. Thank you,Susan

 

[NeonRedSharpie]

What is the calculation to determine eligibility?

 

[Susan Altemus]

A combination of 85 points. For example as of today, someone might be 58.8 years old and have 24.4 years of service. 58.8 + 24.4 = 82 years and 12 months, therefore it's really 83 years. What date would the employee reach the required 85 points, adding 1 year of age and 1 year of service to each year. That's a more complicated example because the months added up equated to another year. Another example: As of today, an employee's age is 56.7 with 26.2 years of service = 82.9 points. What date would they reach the 85 points?I know it's confusing.

 

[NeonRedSharpie]

=(31046-(TODAY()-A2)-(TODAY()-B2))+TODAY()

A2 is DOB and B2 is the start date of the employee. 31025 is 85 years in days (using 365.25 days per year). This calculates the number of days to reach 85 points, then removes the number of days they have been alive + the number of days they have been employed. Adding today's date to the number of days remaining will give you the expected end date.

Test it out and see if it makes sense with your data.

 

[jasonb75]

And how would the exact retirement date be determined if you did the calculation with pen and paper?

A couple of examples using actual dates to show D.O.B. Start date, retirement date, and how you got the answer would help us give you the correct solution.

It should be =age at start + ((85 - age at start) / 2)

But there are several ways of doing this which would all give different results.

edit:-

Neon, it should be 31046 days for 85 years.

 

[barry houdini]

This will depend on exactly how you are calculating the years/months of service and/or age, are you rounding those to the nearest month or using exact figures?

If you take the age at hire date and add 85 then divide that total by 2 you will get the amount of years you need to add to the birthdate to get the retirement date.

In Excel terms that's this calculation

=(A2+B2+85*365)/2

where A2 is the birth date and B2 the hire date

 

[Susan Altemus]

Can't get the formula to work. Not sure why. Will keep trying.

 

[NeonRedSharpie]

Which formula...and why isn't it working? What results are you expecting and what results are you getting?