Retirement Date Needed??

[SassyTexan]

Hello,
I am stumped! I am trying to write a calculation that will yield a date based on the Hire Date and DOB. The total points must be equal to or greater than 80 (Points), Derived by (Age + Yrs of Svc). I need to convert this Point Value into a date of eligibility for retirement, month and yr. Can you help?

I have the age and Yrs of Svc calcs done, but cannot figure out the Eligibility date.

Thank you!

 

[scottylad2]

Hello,
I am stumped! I am trying to write a calculation that will yield a date based on the Hire Date and DOB. The total points must be equal to or greater than 80 (Points), Derived by (Age + Yrs of Svc). I need to convert this Point Value into a date of eligibility for retirement, month and yr. Can you help?

I have the age and Yrs of Svc calcs done, but cannot figure out the Eligibility date.

Thank you![/QUOTE

Can you provide an example of someone already hired with DOB and retirement eligibility and what date they will retire and let us know what makes someone eligible?

In my place it's an automatic at 65, only way you can go earlier is if you've worked at least 30 years and are at least 55..

 

[SassyTexan]

Sure,
Someone has a DOB of 12/13/1961 (Age = 49) Date of Hire is 05/03/1983(Yrs of Svc = 27).

Age + Yrs of Svc = 76 making eligibility for retirement at 75 points March 30, 2010.

I know I previously stated 80 points but there are several people that were grandfathered in at the old rule and I just happened to have all their details. The calculation will be the same I am sure.

Thank you!

 

[cb12]

If I understand you right this might work. Laid out as DOB in Col A, DOH in Col B and Today's date in G1. Ofcourse it might not actually be what you're looking for. However I hope its of some help

=SUM(G1-(((DATEDIF(A2,G1,"d")/365.25)+(DATEDIF(B2,G1,"d")/365.25)-75)*365.25))

 

[cb12]

One thing I forgot to mention was that it actually calculates the retirement date in your example as Dec/2009 due to it working out exact details. Thinking about it in this case you would really want to round down the two DATEDIFs in order to return the completed years.

 

[barry houdini]

Age + Yrs of Svc = 76 making eligibility for retirement at 75 points March 30, 2010.

How do you get March 30? Do you get an extra point on every birthday and anniversary of hire date....if so wouldn't the 75 points be triggered on one of those anniversaries, in May or December in your example........

 

[West Man]

If fractional years count and the dates of 12/13/1961 and 5/3/1983 are in A2 and B2, then try: =((75*365.25)+A2+B2)/2 yielding 2/21/2010. The diffeerence of this date and the two input date yield 48.19 and 26.81 respectively for a total of 75

 

[SassyTexan]

Barry,
When you add the age + Yrs of Svc when the rule of 75 is reached that is the date of eligibility of retirement. So I guess the anniversary date could be the trigger?