PDA

View Full Version : Calculate Retirement Age: I Failed!



david000
05-02-2008, 03:47 PM
Me local state government is lowering me branch?s retirement age if you meet the following criteria:

Your >=50 year of age; your >=20 years of service; your age and service >=75 (down from 85)

I have been killing me-self trying to write the most elegant solution that will tell you the (m/d/yyyy) you can leave (I never tried the first time for 85 I?m too young to care) in excel and FAILED.

But ? I did come up with this! It works!

B2 = your DOB; B3 = your Hire date

=MAX(YEAR(TODAY())+50-(YEAR(TODAY())-YEAR(B2)),YEAR(TODAY())+20-(YEAR(TODAY())-YEAR(B3)))

It returns the MAX year of the criteria that still must be met to retire.

I can?t seem to expand the formula to include the month and day too.
Help would be really super!

Bob Phillips
05-02-2008, 05:10 PM
I cannot see this is being absolutely accurate, but it seems close to me

=TODAY()+((DATE(YEAR(DoB)+75,MONTH(DoB),DAY(DoB))-((TODAY()-DoB)+(TODAY()-HireDate)))-DoB)/2