Calculate Earliest Retirement Eligibility Date, Multiple Conditions

[cj75]

Requesting help with a formula that meets the following requirements of eligibility:

1) Minimum Age - 55 Years (x)
2) Minimum Years of Service - 10 Years
Years of Service = Current Year minus Date of Hire plus 1
3) x + y ≥ 70 years

Available Data - DOB, DOH, Age, Years of Service

 

[JGordon11]

Change the Years of Service formula to whatever you use.

Book1
	A	B	C	D	E	F	G
1	Today	6/4/2024
2
3	Name	DOB	DOH	Years of service	Age	Earliest Retirement
4	Name1	12/22/1971	12/11/1998	25.48	52.45	12/21/2026
5	Name2	1/8/1962	8/27/1994	29.77	62.40	6/4/2024
6	Name3	4/29/1971	5/20/2010	14.04	53.10	4/28/2026
7	Name4	8/2/1997	5/21/2019	5.04	26.84	8/1/2052
8	Name5	5/5/1988	1/7/2018	6.41	36.08	5/5/2043
9	Name6	7/5/1986	6/4/2017	7.00	37.92	7/4/2041
10	Name7	12/6/1996	4/1/2019	5.18	27.49	12/6/2051
11	Name8	10/30/1965	1/6/1992	32.41	58.60	6/4/2024
12	Name9	8/16/1973	6/14/2005	18.97	50.80	8/15/2028
13
Sheet8

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D4:D12	D4	=(TODAY()-C4)/365.25
E4:E12	E4	=(TODAY()-B4)/365.25
F4:F12	F4	=LET(YearsTo55, 55-E4, YearsTo10Service, 10-D4, YearsTo70, (70 - (E4+D4))/2, Yrsmax, MAX(YearsTo55,YearsTo10Service,YearsTo70), MAX(TODAY(), TODAY()+Yrsmax*365.25))

 

[cj75]

Thank you!!!

 

[Tetra201]

Here is an alternative approach:

=LET(d,SEQUENCE(60*365,,EDATE(C4,10*12)),x,DATEDIF(B4,d,"y"),y,DATEDIF(C4,d,"y"),XLOOKUP(1,(x>=55)*(y>=10)*(x+y>=70),d,,1))

You may want to compare it vs. formula from Post #2 for, say, DOB of 6/14/1969 and DOH of 3/31/2014.

 

[cj75]

Here is an alternative approach:

=LET(d,SEQUENCE(60*365,,EDATE(C4,10*12)),x,DATEDIF(B4,d,"y"),y,DATEDIF(C4,d,"y"),XLOOKUP(1,(x>=55)*(y>=10)*(x+y>=70),d,,1))

You may want to compare it vs. formula from Post #2 for, say, DOB of 6/14/1969 and DOH of 3/31/2014.

This works great in identifying date especially when it is already in the past - however I don't think it doesn't captures our "years of service" consideration so would need to find a way to account for that.

 

[Tetra201]

Could you please give an example of not capturing the "years of service" consideration?

 

[cj75]

Could you please give an example of not capturing the "years of service" consideration?

Its not the years of service factor the formula aligns the retirement date with either the DOB or DOH only which seems to elongate the time to retirement:

Birthdate	Hire Date	Retirement Eligibility Date #1	Retirement Eligibility Date #2
06/25/1963	02/29/2016	02/28/2025	03/01/2026
04/08/1952	04/04/2016	04/04/2025	04/04/2026
10/17/1964	04/18/2016	04/18/2025	04/18/2026
12/08/1965	09/21/2015	04/30/2025	12/08/2025
09/12/1965	02/16/2016	05/30/2025	02/16/2026
03/09/1967	12/08/2014	07/23/2025	03/09/2026
07/17/1967	09/29/2014	08/22/2025	07/17/2026
09/29/1970	04/04/2011	09/28/2025	04/04/2026
03/11/1968	08/11/2014	11/25/2025	08/11/2026
09/22/1968	02/03/2014	11/28/2025	09/22/2026
10/17/1960	01/09/2017	01/09/2026	01/09/2027

 

[Tetra201]

So, could you please point out what is wrong with DOR #2?

DOB	DOH	DOR #1	Age	YOS	Age+YOS	DOR #2	Age	YOS	Age+YOS
6/25/1963​	2/29/2016​	2/28/2025​	61​	8​	69​	3/1/2026​	62​	10​	72​
4/8/1952​	4/4/2016​	4/4/2025​	72​	9​	81​	4/4/2026​	73​	10​	83​
10/17/1964​	4/18/2016​	4/18/2025​	60​	9​	69​	4/18/2026​	61​	10​	71​
12/8/1965​	9/21/2015​	4/30/2025​	59​	9​	68​	12/8/2025​	60​	10​	70​
9/12/1965​	2/16/2016​	5/30/2025​	59​	9​	68​	2/16/2026​	60​	10​	70​
3/9/1967​	12/8/2014​	7/23/2025​	58​	10​	68​	3/9/2026​	59​	11​	70​
7/17/1967​	9/29/2014​	8/22/2025​	58​	10​	68​	7/17/2026​	59​	11​	70​
9/29/1970​	4/4/2011​	9/28/2025​	54​	14​	68​	4/4/2026​	55​	15​	70​
3/11/1968​	8/11/2014​	11/25/2025​	57​	11​	68​	8/11/2026​	58​	12​	70​
9/22/1968​	2/3/2014​	11/28/2025​	57​	11​	68​	9/22/2026​	58​	12​	70​
10/17/1960​	1/9/2017​	1/9/2026​	65​	9​	74​	1/9/2027​	66​	10​	76​

 

[cj75]

Your formula seems to make individuals work longer than is required

Birthdate	Age (55)	Hire Date	Years of Service (10)	Retirement Eligibility Date #1	Age @ Retirement	YoS @ Retirement	Equals Age+YoS	Retirement Eligibility Date #2	Age @ Retirement	YoS @ Retirement	Equals Age+YoS	Difference #2 minus#1
01/06/1983	41	05/14/2024	1.06	03/11/2038	55.18	14.82	70.00	01/06/2039	56.00	15.65	71.65	1.65
12/26/1982	41	04/01/2024	1.18	02/12/2038	55.13	14.87	70.00	12/26/2038	56.00	15.74	71.74	1.74
04/02/1982	42	01/08/2024	1.41	08/20/2037	55.38	14.62	70.00	04/02/2038	56.00	15.23	71.23	1.23
02/27/1982	42	05/08/2023	2.08	04/02/2037	55.10	14.90	70.00	02/27/2038	56.00	15.81	71.81	1.81
09/13/1981	43	09/25/2023	1.70	03/20/2037	55.52	14.48	70.00	09/25/2037	56.03	15.00	71.03	1.03
08/02/1981	43	05/23/2023	2.04	12/26/2036	55.40	14.60	70.00	08/02/2037	56.00	15.20	71.20	1.20
04/23/1981	43	07/17/2023	1.89	12/03/2036	55.62	14.38	70.00	07/17/2037	56.23	15.00	71.23	1.23
04/19/1981	43	08/15/2022	2.81	06/16/2036	55.16	14.84	70.00	04/19/2037	56.00	15.68	71.68	1.68
03/24/1981	43	01/22/2024	1.37	02/21/2037	55.92	14.08	70.00	01/22/2038	56.83	15.00	71.83	1.83
02/16/1981	43	05/01/2023	2.10	09/23/2036	55.60	14.40	70.00	05/01/2037	56.20	15.00	71.20	1.20
02/14/1981	43	04/25/2022	3.12	03/20/2036	55.10	14.90	70.00	02/14/2037	56.00	15.81	71.81	1.81
09/12/1980	44	02/06/2023	2.33	05/25/2036	55.70	14.30	70.00	02/06/2037	56.40	15.00	71.40	1.40
08/28/1980	44	11/16/2022	2.56	04/07/2036	55.61	14.39	70.00	11/16/2036	56.22	15.00	71.22	1.22
08/05/1980	44	09/25/2023	1.70	08/30/2036	56.07	13.93	70.00	08/05/2037	57.00	14.86	71.86	1.86
07/21/1980	44	05/01/2023	2.10	06/10/2036	55.89	14.11	70.00	05/01/2037	56.78	15.00	71.78	1.78
03/19/1980	44	01/03/2023	2.42	02/10/2036	55.90	14.10	70.00	01/03/2037	56.79	15.00	71.80	1.80
03/10/1980	44	01/16/2023	2.39	02/12/2036	55.93	14.07	70.00	01/16/2037	56.85	15.00	71.86	1.86
11/22/1979	45	02/01/2022	3.34	06/28/2035	55.60	14.40	70.00	02/01/2036	56.19	15.00	71.19	1.19
09/24/1979	45	06/01/2021	4.01	01/27/2035	55.34	14.66	70.00	09/24/2035	56.00	15.31	71.31	1.31
09/16/1979	45	06/27/2022	2.94	08/06/2035	55.89	14.11	70.00	06/27/2036	56.78	15.00	71.78	1.78
08/22/1979	45	03/28/2022	3.19	06/09/2035	55.80	14.20	70.00	03/28/2036	56.60	15.00	71.60	1.60
08/13/1979	45	07/18/2022	2.89	07/31/2035	55.96	14.04	70.00	07/18/2036	56.93	15.00	71.93	1.93
03/02/1979	45	01/02/2024	1.43	01/31/2036	56.92	13.08	70.00	01/02/2037	57.84	14.00	71.84	1.84
02/28/1979	45	09/19/2022	2.71	06/09/2035	56.28	13.72	70.00	02/28/2036	57.00	14.44	71.44	1.44
02/18/1979	45	01/31/2022	3.35	02/09/2035	55.98	14.02	70.00	01/31/2036	56.95	15.00	71.95	1.95
02/04/1979	45	05/09/2022	3.08	03/23/2035	56.13	13.87	70.00	02/04/2036	57.00	14.74	71.74	1.74
01/25/1979	45	04/22/2024	1.12	03/09/2036	57.12	12.88	70.00	01/25/2037	58.00	13.76	71.76	1.76
01/09/1979	45	03/25/2024	1.20	02/16/2036	57.10	12.90	70.00	01/09/2037	58.00	13.79	71.80	1.80
10/27/1978	46	01/04/2024	1.42	11/30/2035	57.09	12.91	70.00	10/27/2036	58.00	13.81	71.81	1.81
07/21/1978	46	05/31/2023	2.02	06/25/2035	56.93	13.07	70.00	05/31/2036	57.86	14.00	71.86	1.86
05/18/1978	46	04/15/2024	1.14	10/31/2035	57.46	12.54	70.00	05/18/2036	58.00	13.09	71.09	1.09
03/23/1978	46	07/02/2019	5.93	05/12/2033	55.14	14.86	70.00	03/23/2034	56.00	15.72	71.72	1.72
02/10/1978	46	10/04/2021	3.67	06/08/2034	56.32	13.68	70.00	02/10/2035	57.00	14.35	71.35	1.35
02/07/1978	46	08/29/2022	2.77	11/18/2034	56.78	13.22	70.00	08/29/2035	57.56	14.00	71.55	1.55
11/08/1977	47	03/09/2020	5.24	07/09/2033	55.67	14.33	70.00	03/09/2034	56.33	15.00	71.33	1.33
10/08/1977	47	01/02/2024	1.43	05/21/2035	57.62	12.38	70.00	01/02/2036	58.23	13.00	71.23	1.23
09/26/1977	47	07/22/2019	5.88	02/22/2033	55.41	14.59	70.00	09/26/2033	56.00	15.18	71.18	1.18
09/04/1977	47	01/22/2024	1.37	05/14/2035	57.69	12.31	70.00	01/22/2036	58.38	13.00	71.38	1.38
06/01/1977	47	10/10/2023	1.66	02/04/2035	57.68	12.32	70.00	10/10/2035	58.36	13.00	71.36	1.36
05/03/1977	47	03/01/2021	4.27	10/01/2033	56.41	13.59	70.00	05/03/2034	57.00	14.17	71.17	1.17
02/24/1977	47	04/01/2024	1.18	03/14/2035	58.05	11.95	70.00	02/24/2036	59.00	12.90	71.90	1.90
01/30/1977	47	12/03/2018	6.51	07/02/2032	55.42	14.58	70.00	01/30/2033	56.00	15.16	71.16	1.16
01/24/1977	47	01/17/2022	3.38	01/20/2034	56.99	13.01	70.00	01/17/2035	57.98	14.00	71.98	1.98
12/11/1976	47	09/27/2021	3.69	11/03/2033	56.90	13.10	70.00	09/27/2034	57.79	14.00	71.79	1.79
10/25/1976	48	09/18/2023	1.72	10/06/2034	57.95	12.05	70.00	09/18/2035	58.90	13.00	71.90	1.90
08/31/1976	48	02/18/2020	5.30	11/24/2032	56.23	13.77	70.00	08/31/2033	57.00	14.53	71.53	1.53
08/22/1976	48	02/13/2023	2.31	05/19/2034	57.74	12.26	70.00	02/13/2035	58.48	13.00	71.48	1.48
07/23/1976	48	09/18/2017	7.72	08/21/2031	55.08	14.92	70.00	07/23/2032	56.00	15.84	71.84	1.84
03/08/1976	48	02/01/2019	6.34	02/19/2032	55.95	14.05	70.00	02/01/2033	56.90	15.00	71.90	1.90
03/04/1976	48	04/01/2019	6.18	03/17/2032	56.04	13.96	70.00	03/04/2033	57.00	14.92	71.92	1.92
12/16/1975	48	02/21/2017	8.29	01/18/2031	55.09	14.91	70.00	12/16/2031	56.00	15.81	71.81	1.81
12/01/1975	49	05/01/2024	1.10	08/16/2034	58.71	11.29	70.00	05/01/2035	59.41	12.00	71.41	1.41
09/22/1975	49	03/18/2019	6.22	12/19/2031	56.24	13.76	70.00	09/22/2032	57.00	14.52	71.52	1.52
09/17/1975	49	09/13/2021	3.73	03/16/2033	57.50	12.50	70.00	09/17/2033	58.00	13.01	71.01	1.01
09/17/1975	49	02/01/2024	1.34	05/25/2034	58.69	11.31	70.00	02/01/2035	59.38	12.00	71.38	1.38
07/25/1975	49	10/04/2021	3.67	02/27/2033	57.60	12.40	70.00	10/04/2033	58.20	13.00	71.20	1.20
07/22/1975	49	12/04/2023	1.51	03/28/2034	58.68	11.32	70.00	12/04/2034	59.37	12.00	71.37	1.37
07/06/1975	49	05/16/2024	1.06	06/10/2034	58.93	11.07	70.00	05/16/2035	59.86	12.00	71.86	1.86
05/11/1975	49	08/28/2017	7.77	01/03/2031	55.65	14.35	70.00	08/28/2031	56.30	15.00	71.30	1.30
04/06/1975	49	10/09/2018	6.66	07/08/2031	56.25	13.75	70.00	04/06/2032	57.00	14.49	71.49	1.49
03/30/1975	49	06/04/2018	7.01	05/02/2031	56.09	13.91	70.00	03/30/2032	57.00	14.82	71.82	1.82
01/22/1975	49	07/03/2018	6.93	04/13/2031	56.22	13.78	70.00	01/22/2032	57.00	14.56	71.55	1.55
01/20/1975	49	10/16/2018	6.64	06/03/2031	56.37	13.63	70.00	01/20/2032	57.00	14.26	71.26	1.26
12/26/1974	49	09/08/2020	4.74	05/02/2032	57.35	12.65	70.00	12/26/2032	58.00	13.30	71.30	1.30
12/03/1974	50	08/17/2020	4.80	04/10/2032	57.35	12.65	70.00	12/03/2032	58.00	13.30	71.30	1.30
11/13/1974	50	06/03/2024	1.01	02/22/2034	59.28	10.72	70.00	11/13/2034	60.00	11.44	71.44	1.44
11/05/1974	50	04/25/2022	3.12	01/29/2033	58.23	11.77	70.00	11/05/2033	59.00	12.53	71.53	1.53
08/10/1974	50	01/22/2024	1.37	10/31/2033	59.23	10.77	70.00	08/10/2034	60.00	11.55	71.55	1.55
07/25/1974	50	06/01/2017	8.01	06/28/2030	55.93	14.07	70.00	06/01/2031	56.85	15.00	71.85	1.85
07/11/1974	50	03/05/2020	5.25	11/07/2031	57.33	12.67	70.00	07/11/2032	58.00	13.35	71.35	1.35
05/23/1974	50	06/01/2020	5.01	11/26/2031	57.51	12.49	70.00	06/01/2032	58.03	13.00	71.03	1.03
05/16/1974	50	07/31/2018	6.85	12/22/2030	56.60	13.40	70.00	07/31/2031	57.21	14.00	71.21	1.21
04/25/1974	50	10/01/2018	6.68	01/12/2031	56.72	13.28	70.00	10/01/2031	57.43	14.00	71.43	1.43
04/17/1974	50	12/05/2016	8.50	02/09/2030	55.82	14.18	70.00	12/05/2030	56.64	15.00	71.63	1.63
02/15/1974	50	04/10/2017	8.16	03/14/2030	56.07	13.93	70.00	02/15/2031	57.00	14.85	71.85	1.85
02/05/1974	50	05/11/2020	5.07	09/23/2031	57.63	12.37	70.00	05/11/2032	58.26	13.00	71.26	1.26
01/05/1974	50	07/23/2018	6.87	10/14/2030	56.77	13.23	70.00	07/23/2031	57.54	14.00	71.54	1.54
12/30/1973	50	05/01/2023	2.10	02/28/2033	59.17	10.83	70.00	12/30/2033	60.00	11.67	71.67	1.67
08/28/1973	51	10/03/2023	1.68	03/16/2033	59.55	10.45	70.00	10/03/2033	60.10	11.00	71.10	1.10
07/25/1973	51	10/16/2023	1.64	03/05/2033	59.61	10.39	70.00	10/16/2033	60.23	11.00	71.23	1.23
07/24/1973	51	06/15/2015	9.98	01/02/2029	55.45	14.55	70.00	07/24/2029	56.00	15.11	71.11	1.11
07/05/1973	51	05/16/2024	1.06	06/10/2033	59.93	10.07	70.00	05/16/2034	60.86	11.00	71.86	1.86
05/31/1973	51	10/16/2023	1.64	02/06/2033	59.69	10.31	70.00	10/16/2033	60.38	11.00	71.38	1.38
05/16/1973	51	03/18/2024	1.22	04/16/2033	59.92	10.08	70.00	03/18/2034	60.84	11.00	71.84	1.84
04/19/1973	51	03/20/2017	8.21	10/03/2029	56.46	13.54	70.00	04/19/2030	57.00	14.08	71.08	1.08
09/20/1972	52	11/23/2015	9.54	10/21/2028	56.09	13.91	70.00	09/20/2029	57.00	14.83	71.83	1.83
07/27/1972	52	12/29/2020	4.44	04/13/2031	58.71	11.29	70.00	12/29/2031	59.42	12.00	71.42	1.42
07/23/1972	52	08/21/2023	1.79	08/20/2032	60.08	10.00	70.08	08/21/2033	61.08	11.00	72.08	2.00
07/19/1972	52	02/03/2016	9.34	10/26/2028	56.27	13.73	70.00	07/19/2029	57.00	14.46	71.46	1.46
03/01/1972	52	06/08/2020	4.99	10/19/2030	58.64	11.36	70.00	06/08/2031	59.27	12.00	71.27	1.27
01/18/1972	52	08/26/2019	5.78	05/07/2030	58.30	11.70	70.00	01/18/2031	59.00	12.40	71.40	1.40
12/16/1971	52	08/16/2021	3.81	04/16/2031	59.33	10.67	70.00	12/16/2031	60.00	11.33	71.33	1.33
12/13/1971	52	07/16/2018	6.89	09/28/2029	57.80	12.20	70.00	07/16/2030	58.59	13.00	71.59	1.59
11/02/1971	53	02/21/2023	2.29	02/21/2032	60.30	10.00	70.30	02/21/2033	61.31	11.00	72.31	2.00
08/26/1971	53	03/04/2024	1.26	03/04/2033	61.52	10.00	71.52	03/04/2034	62.52	11.00	73.52	2.00
05/10/1971	53	02/27/2017	8.27	10/03/2028	57.40	12.60	70.00	05/10/2029	58.00	13.20	71.20	1.20
04/15/1971	53	01/04/2021	4.42	08/25/2030	59.36	10.64	70.00	04/15/2031	60.00	11.28	71.28	1.28
03/18/1971	53	04/05/2021	4.17	09/25/2030	59.53	10.47	70.00	04/05/2031	60.05	11.00	71.05	1.05
03/12/1971	53	11/17/2022	2.55	11/17/2031	60.69	10.00	70.69	11/17/2032	61.69	11.00	72.69	2.00
03/04/1971	53	04/06/2020	5.17	03/20/2030	59.05	10.95	70.00	03/04/2031	60.00	11.91	71.91	1.91
02/15/1971	53	05/26/2014	11.03	04/06/2027	56.14	13.86	70.00	02/15/2028	57.00	14.72	71.72	1.72
01/14/1971	53	06/05/2017	8.00	09/24/2028	57.70	12.30	70.00	06/05/2029	58.39	13.00	71.39	1.39
01/08/1971	53	05/02/2016	9.10	03/05/2028	57.16	12.84	70.00	01/08/2029	58.00	13.69	71.69	1.69
10/28/1970	54	01/08/2018	7.41	12/02/2028	58.10	11.90	70.00	10/28/2029	59.00	12.80	71.80	1.80
07/18/1970	54	02/10/2014	11.32	10/29/2026	56.28	13.72	70.00	07/18/2027	57.00	14.43	71.43	1.43
06/05/1970	54	03/16/2020	5.22	10/25/2029	59.39	10.61	70.00	06/05/2030	60.00	11.22	71.22	1.22
03/08/1970	54	04/18/2017	8.13	03/28/2028	58.06	11.94	70.00	03/08/2029	59.00	12.89	71.89	1.89
02/05/1970	54	01/30/2017	8.35	02/02/2028	57.99	12.01	70.00	01/30/2029	58.98	13.00	71.98	1.98
01/24/1970	54	09/08/2015	9.74	05/17/2027	57.31	12.69	70.00	01/24/2028	58.00	13.38	71.38	1.38
01/23/1970	54	10/09/2018	6.66	11/30/2028	58.85	11.15	70.00	10/09/2029	59.71	12.00	71.71	1.71
12/25/1969	54	02/24/2020	5.28	07/25/2029	59.58	10.42	70.00	02/24/2030	60.17	11.00	71.17	1.17
11/13/1969	55	06/03/2021	4.01	06/03/2030	60.55	10.00	70.55	06/03/2031	61.55	11.00	72.55	2.00
10/13/1969	55	05/04/2015	10.09	01/22/2027	57.28	12.72	70.00	10/13/2027	58.00	13.44	71.44	1.44
09/26/1969	55	06/01/2022	3.02	06/01/2031	61.68	10.00	71.68	06/01/2032	62.68	11.00	73.68	2.00
09/15/1969	55	04/16/2018	7.14	06/30/2028	58.79	11.21	70.00	04/16/2029	59.58	12.00	71.58	1.58
09/14/1969	55	04/02/2018	7.18	06/23/2028	58.77	11.23	70.00	04/02/2029	59.55	12.00	71.55	1.55
09/12/1969	55	08/27/2019	5.78	03/05/2029	59.48	10.52	70.00	09/12/2029	60.00	11.05	71.05	1.05
07/21/1969	55	04/23/2018	7.12	06/06/2028	58.88	11.12	70.00	04/23/2029	59.76	12.00	71.76	1.76
07/06/1969	55	04/03/2023	2.18	04/02/2032	62.74	10.00	72.74	04/03/2033	63.74	11.00	74.74	2.00
07/05/1969	55	02/08/2021	4.32	02/08/2030	60.60	10.00	70.60	02/08/2031	61.60	11.00	72.59	2.00
06/21/1969	55	01/27/2020	5.36	04/09/2029	59.80	10.20	70.00	01/27/2030	60.60	11.00	71.60	1.60
06/20/1969	55	10/02/2013	11.68	02/09/2026	56.64	13.36	70.00	10/02/2026	57.28	14.00	71.28	1.28
06/20/1969	55	01/14/2019	6.39	10/01/2028	59.28	10.72	70.00	06/20/2029	60.00	11.43	71.43	1.43
05/31/1969	55	05/11/2022	3.07	05/11/2031	61.94	10.00	71.94	05/11/2032	62.95	11.00	73.95	2.00
02/27/1969	55	08/09/2017	7.83	11/18/2027	58.72	11.28	70.00	08/09/2028	59.45	12.00	71.45	1.45
01/25/1969	55	02/06/2023	2.33	02/06/2032	63.03	10.00	73.03	02/06/2033	64.03	11.00	75.03	2.00
01/23/1969	55	04/01/2021	4.18	04/01/2030	61.19	10.00	71.19	04/01/2031	62.18	11.00	73.18	2.00
12/30/1968	55	06/06/2017	8.00	09/18/2027	58.72	11.28	70.00	06/06/2028	59.43	12.00	71.43	1.43
12/08/1968	55	08/23/2021	3.79	08/23/2030	61.71	10.00	71.71	08/23/2031	62.70	11.00	73.70	2.00
10/07/1968	56	07/07/2014	10.92	02/20/2026	57.37	12.63	70.00	10/07/2026	58.00	13.25	71.25	1.25
09/22/1968	56	02/03/2014	11.34	11/28/2025	57.18	12.82	70.00	09/22/2026	58.00	13.63	71.63	1.63
09/01/1968	56	05/24/2023	2.04	05/23/2032	63.72	10.00	73.72	05/24/2033	64.73	11.00	75.73	2.00
07/23/1968	56	09/20/2021	3.71	09/20/2030	62.16	10.00	72.16	09/20/2031	63.16	11.00	74.16	2.00
07/20/1968	56	02/12/2018	7.31	11/01/2027	59.28	10.72	70.00	07/20/2028	60.00	11.43	71.43	1.43
06/21/1968	56	05/13/2019	6.07	06/01/2028	59.95	10.05	70.00	05/13/2029	60.89	11.00	71.89	1.89
05/11/1968	56	08/28/2017	7.77	07/05/2027	59.15	10.85	70.00	05/11/2028	60.00	11.70	71.70	1.70
05/09/1968	56	01/16/2023	2.39	01/16/2032	63.69	10.00	73.69	01/16/2033	64.69	11.00	75.69	2.00

 

[Tetra201]

Oh, I see. Here is an updated formula that returns the expected Retirement Eligibility Dates #1 listed in Post #9 (DOB in column A, DOH in column C):

=LET(n,365.25,d,SEQUENCE(70*n,,C2),x,(d-A2)/n,y,(d-C2)/n+1,XLOOKUP(1,(x>=55)*(y>=10)*(x+y>=70),d,,1)-1)