Need help on formula to select column based on dates

Question

I have an Excel file that I want to duplicate an Excel formula in Alteryx

PNSO1st GI2nd GI3/3 PlanCommitNW-LMHYBCH82HS1102576129 3/15/20236/26/20233/15/2023NW-LMHYBCH8BHS1102576129 ETA 4/156/26/20234/15/2023NW-LMHYBCH8AHS1102576129  6/26/20236/26/2023NW-FMSEALH01HS1102576129  6/26/20236/26/2023

Excel Formula :

=IF(D2="",E2,(IF(ISNUMBER(SEARCH("ETA",D2)),(VALUE(RIGHT(D2,LEN(D2)-(FIND("A",D2)+1)))),D2)))

What I want is to have the formula look at the column 2nd GI and if it is empty then use 3/3 Plan value, then if "2nd GI" column contains "ETA" then strip off the letters and just use Right function with 5 digits. I tried to write the formula below but it did not work and I am looking for help

IsEmpty([2nd GI]) then [3/3 Plan],IF([3/3 Plan] contains "ETA" Then Right"3/3 Plan",5)

Thanks,

Mike

MikeFrancis1959 · Answer

Thank you very much, I am still new and I have been working on this formula this morning. I modified it so many times but it still wasnt working and yours works perfectly!!!!

Thanks,

Mike Francis

FrederikE · Answer

Hey @MikeFrancis1959,

I think this should help:

IF IsNull([2nd GI])
THEN [3/3 Plan]
ELSEIF Contains([2nd GI], 'ETA')
THEN Right(Replace([2nd GI], "ETA ", ""),5)
ELSE [2nd GI]
ENDIF

New Workflow3.yxmd