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"))))
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 |
Last edited: