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

 

[Special-K99]

Example data?
Description of data layout?

 

[cwood81]

DOB - in cell H1
Date of Hire - in cell K1

Example is employee was born 9/1/1962 (59 yrs old) and date of hire is 9/1/2007 --> They have met the eligibility requirement of min age 58 and age plus service equals 70 on 9/1/2018

 

[cwood81]

sorry the DOB is 9/1/1960 to be 59

 

[Special-K99]

DOB - in cell H1
Date of Hire - in cell K1

Example is employee was born 9/1/1962 (59 yrs old) and date of hire is 9/1/2007 --> They have met the eligibility requirement of min age 58 and age plus service equals 70 on 9/1/2018

9/1/2018 is not correct.
On 9/1/2018 the employee born 9/1/1960 will be 58, but if they started working 9/1/2007 they will only have worked 11 years on 9/1/2018.
58+11 is 69 not 70.
They will not be eligble for retirement until 9/1/2019 when they will be 59 years old and have worked for 12 years.

 

[cwood81]

sorry -- this is why I need help with a formula because I can't even do the simple math Can you help?

 

[Special-K99]

What does the ACTUAL rule for retirement eligibility say?

 

[cwood81]

It says you are retirement eligible when you are age 58 or older and your age plus service is 70 years or greater

In this instance, age 58 plus 12 yrs service = 70;
age 59 plus 11 years service = 70;
age 60 plus 10 years service = 70 and so on

 

[Special-K99]

This should be very simple to work out, but it's giving me trouble too...

 

[shg]

[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]

17 Nov 1980​

[/td][td]

12 Aug 2011​

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

17 Nov 2038​

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

3​

[/td][td]

27 Jan 1951​

[/td][td]

15 Oct 1984​

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

27 Jan 2009​

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

4​

[/td][td]

20 Mar 1977​

[/td][td]

11 Oct 1998​

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

20 Mar 2035​

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

5​

[/td][td]

21 Jul 1951​

[/td][td]

5 Sep 1997​

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

5 Sep 2009​

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

6​

[/td][td]

11 Dec 1954​

[/td][td]

10 Aug 1991​

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

11 Dec 2012​

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

7​

[/td][td]

1 Feb 1989​

[/td][td]

1 Apr 2039​

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

1 Apr 2049​

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

8​

[/td][td]

11 Apr 1988​

[/td][td]

12 Dec 2011​

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

11 Apr 2046​

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

9​

[/td][td]

14 Sep 1968​

[/td][td]

27 Apr 2020​

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

14 Sep 2029​

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

10​

[/td][td]

10 Jul 1970​

[/td][td]

17 Jul 2008​

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

10 Jul 2028​

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

11​

[/td][td]

26 Dec 1964​

[/td][td]

30 Jan 2013​

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

30 Jan 2024​

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

12​

[/td][td]

1 Oct 1960​

[/td][td]

22 Dec 2012​

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

22 Dec 2021​

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

13​

[/td][td]

30 Sep 1951​

[/td][td]

1 Jul 1992​

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

30 Sep 2009​

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

14​

[/td][td]

13 Apr 1983​

[/td][td]

22 Jun 2013​

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

13 Apr 2041​

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

15​

[/td][td]

30 Mar 1996​

[/td][td]

2 Mar 2040​

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

30 Mar 2054​

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

16​

[/td][td]

21 Sep 1970​

[/td][td]

15 Nov 2023​

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

21 Sep 2032​

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

17​

[/td][td]

9 Feb 1951​

[/td][td]

14 Aug 1984​

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

9 Feb 2009​

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

18​

[/td][td]

2 Jul 1998​

[/td][td]

23 Jun 2045​

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

23 Jun 2057​

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

19​

[/td][td]

21 Jan 1975​

[/td][td]

28 Jun 2017​

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

21 Jan 2033​

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

20​

[/td][td]

3 Dec 1979​

[/td][td]

10 Oct 2006​

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

3 Dec 2037​

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

21​

[/td][td]

24 Feb 1961​

[/td][td]

13 Feb 2011​

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

24 Feb 2021​

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

Messy formula in C2 and copied down:

=MAX(EDATE(DoB, 58 * 12),
     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))))