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

Workflow

Accepted answers

binu_acs

@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

All comments

binu_acs

@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

binu_acs

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

MikeFrancis1959

They are VW strings

binu_acs

@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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703