If Statement with multiple AND Statement

[ZoeM]

Good morning Community.

I am fighting with establishing an If Statement combined with 'AND' or 'OR' statements to get desired results.

In the sample data attached, the key metric is Item Type (Shirt, Pants). 

The column 'Item Type Status to Date Required By' (column F) should be populated based on whether the associated Item type was able to be manufactured by the 'Date Required By' or sooner.

Key scenarios:

If the Planned Manufactured Date falls after the Required By Date, then it is late. 

If the Actual Manufactured Date falls after the Required By Date, then it is late. 

If the Actual Manufactured Date is Blank and the Date Required By is past then it is late. 

I Hope this is sufficient explanation?

Thanks in advance. 

[ChrisTX]

Use a Formula tool with logic like this:

If [Planned Manufactured Date] > [Required By Date] THEN "Late". 

ELSEIF [Actual Manufactured Date] > [Required By Date\ THEN "Late". 

ELSEIF IsNull([Actual Manufactured Date]) and [Date Required By] < DateTimeNow() THEN "Late"

ELSE "Not Late"

ENDIF 

Chris

[afv2688]

hello @ZoeM ,

Does this work for you?:

Regards

[MarqueeCrew · Accepted solution]

@ZoeM ,

Sorry to hear that you're fighting.  Be Happy!

Here is an alternative expression to what @ChrisTX solved your challenge with:

IF 
   (
   [Planned Manufactured Date] > [Required By Date]
   OR
   [Actual Manufactured Date]  > [Required By Date]
   )
   OR 
   (
   IsNull([Actual Manufactured Date]) 
   and 
   [Date Required By] < DateTimeNow()
   ) THEN "Late"

ELSE "Not Late"

ENDIF 

Cheers,

Mark 

[ZoeM]

As they say there is more than one way to slice a pie...

I used a variation of your formula, had to duplicate it because there was criteria for the Shirts and criteria for the Pants. But worked like a charm.

Thanks for your time and consideration. 

IF
(
[<AR> Approval Date] > [PSC]
OR
[AR Completed Date] > [PSC]
)
OR
(
IsNull([AR Completed Date])
and
[PSC] < DateTimeNow()
) THEN "Late"

ELSE

IF
(
[<AR> Approval Date] > [TSC]
OR
[AR Completed Date] > [TSC]
)
OR
(
IsNull([AR Completed Date])
and
[TSC] < DateTimeNow()
) THEN "Late"

ELSE

"Not Late"

ENDIF ENDIF