Rename values based on multiple criteria

[qhont]

In this data set there are 3 separate policies that are either have 6 month or 12 month terms. I want to define each policy as 'NEW' for the first 12 months - i.e. For ID '01' in E2:E4 it is labeled as 'New' only once and I need a formula to turn it into F2:F4. I only need to change New/Renew for 6 month policies, not 12 month.

 

[ExceLoki]

try this
-------------

Book1
	A	B	C	D	E	F	G
1	ID	start date	end date	term	formula	new/renew	expected
2	1	1/1/2021	6/1/2021	6 mo	new	new	new
3	1	6/3/2021	1/3/2022	6 mo	new	renewal	new
4	1	1/3/2022	6/3/2022	6 mo	renewal	renewal	renewal
5	2	2/1/2021	2/1/2022	12 mo	new	new	new
6	2	2/4/2022	2/4/2023	12 mo	renewal	renewal	renewal
7	3	1/1/2021	6/1/2021	6 mo	new	new	new
8	3	6/3/2021	1/3/2022	6 mo	new	renewal	new
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IFS(D2="6 mo",IF(COUNTIF($A$2:$A$8,A2)>2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>367,"renewal","new"),"new"),D2="12 mo",IF(COUNTIF($A$2:$A$8,A2)>=2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>365,"renewal","new"),"new"))

 

[qhont]

try this
-------------

Book1
	A	B	C	D	E	F	G
1	ID	start date	end date	term	formula	new/renew	expected
2	1	1/1/2021	6/1/2021	6 mo	new	new	new
3	1	6/3/2021	1/3/2022	6 mo	new	renewal	new
4	1	1/3/2022	6/3/2022	6 mo	renewal	renewal	renewal
5	2	2/1/2021	2/1/2022	12 mo	new	new	new
6	2	2/4/2022	2/4/2023	12 mo	renewal	renewal	renewal
7	3	1/1/2021	6/1/2021	6 mo	new	new	new
8	3	6/3/2021	1/3/2022	6 mo	new	renewal	new
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IFS(D2="6 mo",IF(COUNTIF($A$2:$A$8,A2)>2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>367,"renewal","new"),"new"),D2="12 mo",IF(COUNTIF($A$2:$A$8,A2)>=2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>365,"renewal","new"),"new"))

Thanks so much ExceLoki!

 

[ExceLoki]

happy to help, thanks for feedback