If criteria is met, add 2 years, if not, add 1

[FV_64]

Hello and Good Morning (or Good Afternoon )

I need help with inserting a formula in Column D (New Date), that adds 2 years to End date (Column C) when the type = Physician (column A) and adds 1 year to the End date (column C) for all other types in the new column.

Help would be appreciated.

 

[FormR]

Hi, here's one option you can try:

Book5
	A	B	C	D
1	Type	Eff	End	New Date
2	NON-EMERGENCY MEDICAL TRANSPORTATION	01/19/2022	01/19/2025	01/19/2026
3	NON-EMERGENCY MEDICAL TRANSPORTATION	05/05/2021	05/05/2024	05/05/2025
4	OPTOMETRIST	02/26/2021	02/26/2024	02/26/2025
5	PHYSICIAN	04/12/2021	04/12/2024	04/12/2026
6	PHYSICIAN	12/23/2020	12/23/2023	12/23/2025
7	PHYSICIAN	07/21/2020	07/21/2023	07/21/2025
8	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
9	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
10	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
11	URGENT CARE CENTER	07/03/2020	07/03/2023	07/03/2024
12	PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
13	PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
14	DENTIST	03/01/2021	03/01/2024	03/01/2025
15	OPTOMETRIST	09/25/2020	09/25/2023	09/25/2024
16	PSYCHOLOGIST	09/25/2020	09/25/2023	09/25/2024
17	MULTI-DISCIPLINARY GROUP	05/06/2021	05/06/2024	05/06/2025
18	PHYSICIAN	12/16/2022	12/16/2025	12/16/2027
19	PHYSICIAN GROUP	05/26/2020	05/26/2023	05/26/2024
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=IF(TRIM(A2)="PHYSICIAN",IF(EDATE(B2,60)=C2,C2,EDATE(C2,24)),EDATE(C2,12))

 

[FV_64]

Hi, here's one option you can try:

Book5
	A	B	C	D
1	Type	Eff	End	New Date
2	NON-EMERGENCY MEDICAL TRANSPORTATION	01/19/2022	01/19/2025	01/19/2026
3	NON-EMERGENCY MEDICAL TRANSPORTATION	05/05/2021	05/05/2024	05/05/2025
4	OPTOMETRIST	02/26/2021	02/26/2024	02/26/2025
5	PHYSICIAN	04/12/2021	04/12/2024	04/12/2026
6	PHYSICIAN	12/23/2020	12/23/2023	12/23/2025
7	PHYSICIAN	07/21/2020	07/21/2023	07/21/2025
8	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
9	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
10	PHYSICIAN	02/22/2023	02/22/2028	02/22/2028
11	URGENT CARE CENTER	07/03/2020	07/03/2023	07/03/2024
12	PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
13	PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
14	DENTIST	03/01/2021	03/01/2024	03/01/2025
15	OPTOMETRIST	09/25/2020	09/25/2023	09/25/2024
16	PSYCHOLOGIST	09/25/2020	09/25/2023	09/25/2024
17	MULTI-DISCIPLINARY GROUP	05/06/2021	05/06/2024	05/06/2025
18	PHYSICIAN	12/16/2022	12/16/2025	12/16/2027
19	PHYSICIAN GROUP	05/26/2020	05/26/2023	05/26/2024
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=IF(TRIM(A2)="PHYSICIAN",IF(EDATE(B2,60)=C2,C2,EDATE(C2,24)),EDATE(C2,12))

This worked. Thanks again so much.