How to write nested IF AND statements to find the criteria

[Adit]

Hello, I am trying to categorize data based on the days output, please see below. The required Output col should update based on the days value and criteria.

I have tried using nested IF AND however, it looks like I cannot resolve it. Any help will be highly appreciated.

My Try which I know is wrong :- =IF(AND(G5>35,G5<=21),"1 WEEK LATE",IF(AND(G5>42,G5<=14),"2 WEEKS LATE",IF(G5<=0,"Drop1 Date Wrong",IF(G5=28,"On Time","Too Late"))))

Days	Required Output	Criteria
-21​	Wrong Date	If days <= 0 then Wrong Date
-14​	Wrong Date	if days = 28 then On time
-7​	Wrong Date	If days >=21 and days <= 35 then 1 week late
-6​	Wrong Date	if days >= 14 and days <=42 then 2 weeks late
0​	Wrong Date	If days >0 and days >42 then Too Late
7​	Too Late
13​	Too Late
14​	2 Weeks Late
15​	2 Weeks Late
20​	2 Weeks Late
21​	1 week late
22​	1 week late
24​	1 week late
28​	On Time
31​	1 week late
34​	1 week late
35​	1 week late
42​	2 weeks late
49​	Too Late
56​	Too Late
63​	Too Late
70​	Too Late
77​	Too Late

 

[iggydarsa]

AND(G5>35,G5<=21) means if the value in G5 both greater than 35 and less than or equal to 21 which does NOT make sense. I think you meant to say AND(G5<35,G5>=21).
You have the similar issue with AND(G5>42,G5<=14) as well.

 

[Skybluekid]

Try this:

Book1.xlsm
	B	C
2	Days
3	-21	Wrong Date
4	-14	Wrong Date
5	-7	Wrong Date
6	-6	Wrong Date
7	0	Wrong Date
8	7	Too Late
9	13	Too Late
10	14	2 Weeks Late
11	15	2 Weeks Late
12	20	2 Weeks Late
13	21	1 Week Late
14	22	1 Week Late
15	24	1 Week Late
16	28	On Time
17	31	1 Week Late
18	34	1 Week Late
19	35	1 Week Late
20	42	2 Weeks Late
21	49	Too Late
22	56	Too Late
23	63	Too Late
24	70	Too Late
25	77	Too Late
Sheet7

Cell Formulas
Range	Formula
C3:C25	C3	=IFS(B3<=0,"Wrong Date", B3=28,"On Time",AND(B3>=21,B3<=35),"1 Week Late",AND(B3>=14,B3<=42),"2 Weeks Late",TRUE,"Too Late")

 

[Adit]

Try this:

Book1.xlsm
	B	C
2	Days
3	-21	Wrong Date
4	-14	Wrong Date
5	-7	Wrong Date
6	-6	Wrong Date
7	0	Wrong Date
8	7	Too Late
9	13	Too Late
10	14	2 Weeks Late
11	15	2 Weeks Late
12	20	2 Weeks Late
13	21	1 Week Late
14	22	1 Week Late
15	24	1 Week Late
16	28	On Time
17	31	1 Week Late
18	34	1 Week Late
19	35	1 Week Late
20	42	2 Weeks Late
21	49	Too Late
22	56	Too Late
23	63	Too Late
24	70	Too Late
25	77	Too Late
Sheet7

Cell Formulas
Range	Formula
C3:C25	C3	=IFS(B3<=0,"Wrong Date", B3=28,"On Time",AND(B3>=21,B3<=35),"1 Week Late",AND(B3>=14,B3<=42),"2 Weeks Late",TRUE,"Too Late")

Many thanks for your help

 

[Skybluekid]

@Adit You are very welcome