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, one option you could try:

=EDATE(C2,IF(A2="PHYSICIAN",24,12))

 

[Legacy 143009]

=IF(A2="Physician",EDATE(C2,24),EDATE(C2,12))

 

[Georgiboy]

Another option:

=LET(e,--C2:C12,p,IF(A2:A12="PHYSICIAN",1,2),EDATE(e,p*12))

 

[FV_64]

I believe it was my mistake in not communicating my need, I apologize. With the answers provided, I am seeing that only one year was added.

My expectation was that in Column D, for Physicians it would show 2 years added to the End Date. Ex. D5 should be 4/12/2026, D6 would be 9/10/2026, etc.

 

[FormR]

I believe it was my mistake in not communicating my need

No, I think something else is going on:

Book3
	A	B	C	D
1
2	PHYSICIAN		12/04/2024	12/04/2026
3	PHYSICIAN		09/10/2023	09/10/2025
4	DENTIST		07/03/2024	07/03/2025
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=EDATE(C2,IF(A2="PHYSICIAN",24,12))

You need to check your cells that contain "PHYSICIAN" to make sure that there aren't any errant spaces or other non printable characters? You can do some testing by using the LEN() formula to check the length of the text in those cells is as you would expect.

If you need more help diagnosing, try to download the XL2BB add-in and post your example data to the forum in a way it can be copied.

 

[Legacy 143009]

Please try:

=IF(TRIM(A2)="PHYSICIAN",EDATE(C2,24),EDATE(C2,12))

 

[snjpverma]

Or this:

=EDATE(C2,12+12*(trim(A2)="PHYSICIAN"))

 

[FV_64]

No, I think something else is going on:

Book3
	A	B	C	D
1
2	PHYSICIAN		12/04/2024	12/04/2026
3	PHYSICIAN		09/10/2023	09/10/2025
4	DENTIST		07/03/2024	07/03/2025
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=EDATE(C2,IF(A2="PHYSICIAN",24,12))

You need to check your cells that contain "PHYSICIAN" to make sure that there aren't any errant spaces or other non printable characters? You can do some testing by using the LEN() formula to check the length of the text in those cells is as you would expect.

If you need more help diagnosing, try to download the XL2BB add-in and post your example data to the forum in a way it can be copied.

Yeah, that was the issue. I totally missed I had a bunch of blank spaces. Thanks a lot!

 

[FV_64]

Hello, I got a follow up question. I read the forum rules and I didn't want to create another post as it may seem as duplicate. Apologies if I do need to create a new one. Please advise.

In the original criteria, I missed an important condition. To recap, we added 2 years to End date (Column C) when the type = Physician (column A) and added 1 year to the End date (column C) for all other types in the new column. The formula I'm using is

=IF(TRIM(A2)="PHYSICIAN",EDATE(C2,24),EDATE(C2,12))

The detail I missed: There may be several Physician (Column A) that their Eff (column B) and End Date (column C) have a span of 5 years (see highlighted in yellow as an example). For those (and only those) Physicians that have a span of 5 years between eff date (column B) and end date (column C), I need the date to display in New Date (column D) to be the same as their End date (Column C). Meaning, I don't need it to add the 2 years for only those. Those 3 examples in the image below that are in red, my expectation is to see 02/22/2028, because their eff and end date have a 5 year span. Those other Physicians that have a 3 year date span and that we added 2 years, are fine.

Help is much appreciated.

PS: Yes, I'm in the process of adding the XL2BB add in.

Type	Eff	End	New Date
NON-EMERGENCY MEDICAL TRANSPORTATION	01/19/2022	01/19/2025	01/19/2026
NON-EMERGENCY MEDICAL TRANSPORTATION	05/05/2021	05/05/2024	05/05/2025
OPTOMETRIST	02/26/2021	02/26/2024	02/26/2025
PHYSICIAN	04/12/2021	04/12/2024	04/12/2026
PHYSICIAN	12/23/2020	12/23/2023	12/23/2025
PHYSICIAN	07/21/2020	07/21/2023	07/21/2025
PHYSICIAN	02/22/2023	02/22/2028	02/22/2030
PHYSICIAN	02/22/2023	02/22/2028	02/22/2030
PHYSICIAN	02/22/2023	02/22/2028	02/22/2030
URGENT CARE CENTER	07/03/2020	07/03/2023	07/03/2024
PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
PHYSICIAN	12/28/2021	12/28/2024	12/28/2026
DENTIST	03/01/2021	03/01/2024	03/01/2025
OPTOMETRIST	09/25/2020	09/25/2023	09/25/2024
PSYCHOLOGIST	09/25/2020	09/25/2023	09/25/2024
MULTI-DISCIPLINARY GROUP	05/06/2021	05/06/2024	05/06/2025
PHYSICIAN	12/16/2022	12/16/2025	12/16/2027
PHYSICIAN GROUP	05/26/2020	05/26/2023	05/26/2024