Bdtyktl

I am trying to use Excel to calculate the date that the sum of an employee's age and years of service equal to 90. This is for the purpose of determining retirement eligibility.

I have two dates - the birth date and hire date. Currently I'm doing the kindergarten version and have two columns, with the age and years of service in months calculating from a date field, that I'm plugging future dates into until the sum equals 1080 (90 yrs).

I'm trying to eliminate the plugging and chugging aspect but I can't wrap my mind around how to incorporate all of this into formula(s).

The formulas I'm using so far:

• 
  E:E = Birth Dates


• 
  F:F = Hire Dates



• G2 = I am plugging a potential retirement date into each field in this column and the below formula adds the age and years of service (in months) as of this date. I change the date until the total is greater than or equal to 1080, the lowest number as possible (to determine earliest eligible retirement date).




• G2 = H2=DATEDIF(E2,G2,"m")+DATEDIF(F2,G2,"m")



• G2 = H2=[Age in months + years of service in months]

Example:

• G2 = E2 = Birth Date = 01-May-1990


• G2 = F2 = Hire Date = 01-Feb-2011


• G2 = G2 = Earliest Retire Date = 01-Oct-2045
  


• G2 = H2 = 1081

01-Sep-2045 equals 1079 months so 01-Oct-2045 is the first month the employee is eligible to retire (earliest date that age + YOS >= 90). Is there a way to automate this in Excel rather than by trial and error?

This is the formula that's in C2 in my sheet as shown, and copied down:

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

1. Compare 32873 (90 years of days **)




2. To the sum of two quantities:

(TODAY()-A2) days since birth

(TODAY()-B2) days since hire

to find how many days remain until eligibility;

3. Divide that number of days by 2, because we want to 'double count' each calendar day between now and eligibility (each day goes toward both age and tenure)




4. add that length of time to TODAY() today's date to find the future date of eligibility.

For the record I used about 4-5 columns to put this together, going step by step, until I had the answer that satisfied spec and then combined formulas backwards until I had one overall formula. I didn't actually even have this plan of combining these four values as shown until I had done half the work and was thus able to contain the entire relationship in my mind at one time. I imagine there are people that are able to function that way, but I'm not one.

It's a good approach to divide tasks like this into chunks until you can see how they're supposed to work together, if you can't see the whole picture at first.

NOTE I see since you've expanded your question that length of time in months is critical to the calculation. @fixer1234 's questions are necessary here to produce results that are better than almost always right.

** Thank you @fixer1234 for pointing out my mistake - 90 years of days is actually not 32850. A problem is that there are going to be EITHER 22 or 23 leap days added to the calendar in any given 90-year sample between the years 1901 and 2199 CE, depending on whether the period counted starts or ends on a leap year. So, this is a start, but may not be sufficiently precise depending on the exact policies in question.