Age and Number Years of Service

[Guest]

I am trying to write a formula that is able to return the earliest possible
year of retirement based on years of service and age. The conditions are
summarized in the following table:

Age Years of Service
2005 60 36
2006 60,5 36,5
2007 61 37
2008 61,5 37,5
2009 62 38
2010 62,5 38,5
2011 63 39
2012 63,5 39,5
2013 64 40
2014 64,5 40

To retire in any given year, someone must simultaneously meet the two
conditions. An example, for someone to retire in 2009 they must be at least
62 years old and have 38 years of service.

Any ideas?

Thanks in advance!

 

[Guest]

=if(and(age>=62,YoS>=38),"Yes","No")

 

[Guest]

Assuming we know the age(D2) and service(E2) of the candidate, then with the
following table in A2:C11, the we can find if the candidate is eligible for
retirement:
This assumes ages are in half-yearly increments and matches an entry in the
table

in F2:

=IF(E2>=VLOOKUP(D2,A2:C11,2),VLOOKUP(D2,A2:C11,3),"Too early!")

If length of service is >= to service amount in table for this age, then
return Year, else "Too early!"

A B C
Age Service Year
60.00 36.00 2005
60.50 36.50 2006
61.00 37.00 2007
61.50 37.50 2008
62.00 38.00 2009
62.50 38.50 2010
63.00 39.00 2011
63.50 39.50 2012
64.00 40.00 2013
64.50 40.00 2014

HTH

 

[Guest]

With your existing Table in say J7:L16 (excluding headers)
In Cell G4 - enter Year you wish to test for, say 2005
Birthdate in Cell C7 11/27/1944
EmployDate in Cell D7 6/6/1966
In cell E7 (with header - Do you Qualify?)enter:

=IF(AND(DATEDIF(C7,DATE($G$4,1,1),"y")>=VLOOKUP($G$4,$J$7:$L$16,2,FALSE),DATEDIF(D7,DATE($G$4,1,1),"y")>=VLOOKUP($G$4,$J$7:$L$16,3,FALSE)),"YES","NO")
watch for wrapping after pasting..
HTH,
Jim May