Forum Discussion
Solved
Stuck with Multiple If Conditions
can any one help me on this situation.
I have below table. I need to get "OnTime" and "OverDue" Status based on the following criteria.
| PART NO. | SERVICE | Exception | LASTDATE | Days | Status |
| UY4352 | JAQ | No | 29-05-2017 09:01 | 4 | |
| UZ0014 | Ret | No | 26-05-2017 09:53 | 20 | |
| UZ0UY7 | Ret | No | 26-05-2017 09:53 | 20 | |
| UZ0201 | Ret | No | 26-05-2017 08:52 | 20 | |
| UZ0281 | Ret | No | 26-05-2017 13:03 | 20 | |
| UZ224 | Ret | No | 27-05-2017 03:38 | 21 | |
| UZ282 | Ret | No | 27-05-2017 17:28 | 21 | |
| UZ418 | Ret | No | 27-05-2017 20:59 | 21 | |
| AC0024 | JAQ | No | 29-05-2017 00:37 | 8 | |
| AW0075 | JAQ | No | 29-05-2017 06:28 | 8 | |
| AW0081 | JAQ | No | 29-05-2017 06:26 | 8 | |
| AW0090 | JAQ | No | 29-05-2017 06:24 | 8 | |
| BC0218 | JAQ | No | 29-05-2017 03:25 | 8 | |
| BJ0554 | JAQ | No | 29-05-2017 04:12 | 7 | |
| ND0105 | Fly | No | 28-05-2017 22:04 | 20 | |
| ND0106 | Fly | No | 28-05-2017 21:50 | 20 | |
| ND0107 | Fly | No | 28-05-2017 21:50 | 20 | |
| OP0245 | Fly | No | 28-05-2017 20:30 | 20 | |
| OP0254 | Fly | No | 28-05-2017 07:17 | 20 | |
| OP0354 | Fly | Yes | 28-05-2017 20:30 | 20 | |
| OP0380 | Fly | No | 28-05-2017 22:19 | 20 | |
| CF0622 | Ret | No | 27-05-2017 23:16 | 21 | |
| CG0006 | JAQ | No | 28-05-2017 12:53 | 4 | |
| CG0341 | JAQ | No | 28-05-2017 20:41 | 8 | |
| CN0092 | JAQ | No | 29-05-2017 09:14 | 8 | |
| CN0UZ1 | JAQ | No | 29-05-2017 02:44 | 7 | |
| CN0UZ2 | JAQ | No | 29-05-2017 02:44 | 7 | |
| CN0137 | JAQ | No | 26-05-2017 17:43 | 8 | |
| CN0138 | JAQ | No | 26-05-2017 17:43 | 8 | |
| CN0147 | JAQ | yes | 29-05-2017 02:19 | 4 | |
| CN0148 | JAQ | No | 29-05-2017 02:19 | 4 | |
| CN0156 | JAQ | No | 28-05-2017 20:23 | 8 | |
| DB0635 | Fly | No | 29-05-2017 02:41 | 8 | |
| DB0652 | Fly | No | 27-05-2017 00:51 | 21 | |
| DB0690 | JAQ | Yes | 29-05-2017 03:20 | 7 | |
| DB0691 | Fly | No | 27-05-2017 00:51 | 21 | |
| DB0693 | JAQ | No | 29-05-2017 03:20 | 7 | |
| DC0009 | Fly | Yes | 29-05-2017 09:41 | 8 | |
| DD0235 | JAQ | No | 29-05-2017 02:21 | 8 | |
| DD0240 | JAQ | No | 29-05-2017 02:21 | 8 | |
| DD0490 | Fly | No | 29-05-2017 03:48 | 8 | |
| DG0371 | Fly | No | 29-05-2017 10:38 | 8 | |
| DG0649 | Fly | No | 29-05-2017 11:15 | 8 | |
| DG0799 | JAQ | No | 29-05-2017 11:17 | 4 | |
| PO0149 | Ret | No | 27-05-2017 00:26 | 21 | |
| PO0236 | Ret | No | 27-05-2017 23:46 | 21 | |
| JH0291 | JAQ | No | 29-05-2017 04:47 | 7 | |
| JH0322 | JAQ | No | 29-05-2017 04:48 | 7 | |
| JH0652 | JAQ | No | 23-05-2017 07:56 | 7 | |
| JH0653 | JAQ | No | 23-05-2017 07:56 | 7 | |
| LJ0240 | Fly | No | 29-05-2017 08:19 | 8 | |
| LB0795 | Fly | No | 29-05-2017 09:56 | 8 |
Criteria :
if [LastDate] is blank then [Status] = "OverDue" if [Service] = "JAQ" and [Exception] = "Yes" and [Days] < 8 then "OnTime(E)" if [Service] = "JAQ" and [Exception] = "Yes" and [Days] > 8 then "OverDue(E)" if [Service] = "JAQ" and [Exception] = "NO"and [Days] < 15 then "OnTime" and [Days] > 15 then "OverDue" if [Service] <> "JAQ" and [Days] < 20 then "OnTime" and [Days] > 20 then "OverDue"
any help is much appreciated. :)
Thank you in advance,
This should do the trick, however you need to reconsider your logic for whenever [Days] = your criteria. All of your statements deal with Above and Below, but never on the same day. For the below i've had to assume what you wanted.
Status = IF( ISBLANK([LastDate]), "OverDue", IF( [Service] = "JAQ", IF( [Exception] = "Yes", IF( [Days] < 8, "OnTime(E)", "OverDue(E)" ), IF( [Days] < 15, "OnTime", "OverDue" ) ), IF( [Days] < 20, "OnTime", "OverDue" ) ) )
Could you try something like....
Status = SWITCH (TRUE() ,
[LastDate] = blank() , "OverDue" [Service] = "JAQ" && [Exception] = "Yes" && [Days] < 8 , "OnTime(E)" [Service] = "JAQ" && [Exception] = "Yes" && [Days] > 8 , "OverDue(E)" [Service] = "JAQ" && [Exception] = "NO" && [Days] < 15 , "OnTime"
[Service] = "JAQ" && [Exception] = "NO" && [Days] > 15 , "OverDue" [Service] <> "JAQ" && [Days] < 20 , "OnTime"
[Service] <> "JAQ" && [Days] > 20 , "OverDue"
-----ELSE------
"Other")
3 Replies
This should do the trick, however you need to reconsider your logic for whenever [Days] = your criteria. All of your statements deal with Above and Below, but never on the same day. For the below i've had to assume what you wanted.
Status = IF( ISBLANK([LastDate]), "OverDue", IF( [Service] = "JAQ", IF( [Exception] = "Yes", IF( [Days] < 8, "OnTime(E)", "OverDue(E)" ), IF( [Days] < 15, "OnTime", "OverDue" ) ), IF( [Days] < 20, "OnTime", "OverDue" ) ) )
Could you try something like....
Status = SWITCH (TRUE() ,
[LastDate] = blank() , "OverDue" [Service] = "JAQ" && [Exception] = "Yes" && [Days] < 8 , "OnTime(E)" [Service] = "JAQ" && [Exception] = "Yes" && [Days] > 8 , "OverDue(E)" [Service] = "JAQ" && [Exception] = "NO" && [Days] < 15 , "OnTime"
[Service] = "JAQ" && [Exception] = "NO" && [Days] > 15 , "OverDue" [Service] <> "JAQ" && [Days] < 20 , "OnTime"
[Service] <> "JAQ" && [Days] > 20 , "OverDue"
-----ELSE------
"Other")
