Need assistance on how to write formula

[MikeFrancis1959]

I need help with adding another piece to this formula because the excel file was changed.

The part of the formula in italics is working but now someone added “ ETA 04/13” or whatever date is needed preceded by “ETA” and I want to have the formula so if the 2ng GI is empty or it has “ETA” in it, it will say “ In Transit” but I have tried but to no avail, can someone help me?

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

THEN "Delivered"

ELSEIF

[1st GI]>"2023-01-01" AND ISEMPTY([2nd GI])

THEN "In Transit"

ELSE "Planning TBD"

ENDIF

Thanks,

Mike

[binuacs]

@MikeFrancis1959 Can you try the below formula

 

IF [1st GI]>"2023-01-01" AND [2nd GI]>"2023-01-01"
THEN "Delivered"
ELSEIF
[1st GI]>"2023-01-01" AND (ISEMPTY([2nd GI] OR Contains([2nd GI],'ETA'))
THEN "In Transit"
ELSE "Planning TBD"
ENDIF

[cjaneczko]

Your best bet is to insert a formula with the following on 1st and 2nd GI: Your original formula should start working again after that. 

Replace([1st GI], 'ETA', '')

[MikeFrancis1959]

I get a parse error at char 151 (Unmatched Expression #1)

Mike

[binuacs]

@MikeFrancis1959 Can you check the data types of the fields [1st GI] and [2nd GI]

[MikeFrancis1959]

They are VW strings

[binuacs · Accepted solution]

@MikeFrancis1959 I missed to put a closing bracket in the isEmpty() function

IF [1st GI]>"2023-01-01" AND [2nd GI]>"2023-01-01"
THEN "Delivered"
ELSEIF
[1st GI]>"2023-01-01" AND (ISEMPTY([2nd GI]) OR Contains([2nd GI],'ETA'))
THEN "In Transit"
ELSE "Planning TBD"
ENDIF

[MikeFrancis1959]

Thank you very much, it works great!