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

#### Susan Altemus

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
What is the calculation to determine eligibility?

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.

Code:
``=(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.

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.

Last edited:
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

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

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

Replies
9
Views
269
Replies
7
Views
702
Replies
2
Views
233
Replies
1
Views
134
Replies
3
Views
150

1,219,792
Messages
6,150,288
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back