Stuck with Multiple If Conditions

[rocky09]

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,

[Anonymous · Accepted solution]

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"
		)
	)
)	

[Phil_Seamark · Accepted solution]

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")

[Anonymous · Accepted solution]

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"
		)
	)
)	

[rocky09]

thank you so much @Anonymous @Phil_Seamark

Both are working fine.