Formula for Retirement Eligibility Date - min age (58) and years of service plus age is min of 70

[cwood81]

I want to start off with saying that I have scoured the internet to find this answer -- it's driving me crazy. Someone please help!!

I need an excel formula that calculates a date based on:
1) Minimum age of 58 years
2) Age plus service is 70 or greater

 

[cwood81]

this doesn't exactly work.... for instance
DOB: 3/22/1951
DOH: 10/1/12

Retirement date should be 12/25/2016 but the above is giving me 3/22/2009, which is when they are 58 and not when they are "rule of 70"

 

[shg]

That's not what I get:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

DoB​

[/td][td="bgcolor:#F3F3F3"]

DoH​

[/td][td="bgcolor:#F3F3F3"]

DoR​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

22 Mar 1951​

[/td][td]

1 Oct 2012​

[/td][td="bgcolor:#CCFFCC"]

22 Mar 2017​

[/td][/tr]
[/table]

The retirement date is always either a birthday or an anniversary of hire, no?

 

[shg]

A correction for leaplings in the UK, Hong Kong, and other places where legal age is attained on Mar 1 in non-leap years:

=MAX(DATE(YEAR(DoB) + 58, MONTH(DoB), DAY(DoB)),
   MIN(EDATE(DoB, 12 * (DATEDIF(DoB, EDATE(DoB + DoH, 70 * 12) / 2, "y") + 1)),
       EDATE(DoH, 12 * (DATEDIF(DoH, EDATE(DoB + DoH, 70 * 12) / 2, "y") + 1))))

 

[cwood81]

I was able to make the formula work as follows:
1) Calculate when they achieve Age 58 58*12 = 696 months): =MIN(EDATE(H1,696))
2) Calculate age at date of hire ("DoH") = (DoH -DoB)/365.25
3) Calculate what age they are when 70 points is reached: =(DoH + 70)/2
4) Calculate date for #3 : = (Age at 70 points * 365.25)+DoB
5) Insert If Statement: =If(#1 >#4 ,#1 ,#4 )

Probably too many steps... but this is the only one that would work