Forum Discussion

rocky09's avatar
rocky09
Solution Sage
9 years ago
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  Stat...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    9 years ago

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