Formula is not producing results I thought they would, please help!

[MikeFrancis1959]

I am having some difficulty trying to get my formula correct, here is the formula below but if there is  “ESD” or “ETA” in “1st GI” or “ 2nd GI” then I want to say “ In Transit” but when I run the workflow it shows “Delivered”, can someone tell me what is wrong with my formula so I can change it?

IF [1st GI]>"2023-01-01" AND [2nd GI]>"2023-01-01"

THEN "Delivered"

ELSEIF

Contains ([1st GI],'ESD') OR >"2023-01-01" AND (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))

THEN "In Transit"

ELSE "Planning TBD"

ENDIF

PN   Qty    1st GI                    2nd GI

A      1       1/25/2023            1/28/2023

B     1        ESD 05/17          ETA 05/19 (was expecting "in transit")

C     72     1/30/2023           ETA 02/03  (was expecting "in transit")

[ChrisTX · Accepted solution]

Your requirement:   if there is  “ESD” or “ETA” in “1st GI” or “ 2nd GI” then I want to say “ In Transit” 

Your code:   Contains ([1st GI],'ESD') OR >"2023-01-01" AND (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))

You can't have code like       OR >"2023-01-01"

You always need a field name before the OR

and the "AND" before (ISEMPTY([2nd GI])     should be "OR"

It also looks like you have an issue with the data type for field [1st Gl].   I think this field must be a String data type, but in your code you're trying to compare the field value to a static date value "2023-01-01".

You can try this option: use a Formula tool to create a new field like "1st Gl Date values", and convert the String values to Date format, then try code like this:

Contains ([1st GI],'ESD') OR [1st Gl Date values] >"2023-01-01" OR (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))

Chris

[Christina_H]

I can see a couple of immediate problems with this.

1. You're treating [1st GI] and [2nd GI] as dates in the formula, but they are not date fields

2. Your formula is incorrect at ELSEIF.

      Current: Contains ([1st GI],'ESD') OR >"2023-01-01" AND (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))

      Corrected: Contains ([1st GI],'ESD') OR [1st GI]>"2023-01-01" AND (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))

[MikeFrancis1959]

Thanks, I will change the formula and change to a date and see.

Thanks

Mike