If and Or formula help

wonka1234

hi,

can anyone help me with this formula?

test field = IF [c_Action_Date] <= "2021-11-13" then [202110] Or [202111] or [202201] or [202202]

I am trying to do the above logic.

But Or doesnt really work here.

this is a record with action ddate < 2021-11-13.

so my test field is showing "0" for this formula I have. But I want it to show "Y" if one of the 4 fields have a "Y"

How can I do this?

Custom Tools

Accepted answers

All comments

Qiu

@wonka1234
Can you give a bigger dataset for the input and output?

wonka1234

@Qiu see attached.

max diamond date =

IF [c_Action_Date] <= "2021-11-13" then [202110] Or [202111] or [202201] or [202202]
elseif [c_Action_Date] <= "2021-12-13" then [202203]
elseif [c_Action_Date] <= "2022-01-13" then [202204]
elseif [c_Action_Date] <= "2022-02-13" then [202205]
elseif [c_Action_Date] <= "2022-03-13" then [202206]
else ""
ENDIF

min diamond date =

IF [c_Action_Date] <= "2021-11-12" then [202110]
elseif [c_Action_Date] <= "2021-12-12" then [202111]
elseif [c_Action_Date] <= "2022-01-12" then [202112]
elseif [c_Action_Date] <= "2022-02-12" then [202201]
elseif [c_Action_Date] <= "2022-03-12" then [202202]
elseif [c_Action_Date] <= "2022-04-12" then [202203]
elseif [c_Action_Date] <= "2022-05-12" then [202204]
elseif [c_Action_Date] <= "2022-06-12" then [202205]
elseif [c_Action_Date] <= "2022-07-12" then [202206]
else ""
ENDIF

all attached records have action date less then 2022-11-13

sample.xlsx

binu_acs

@wonka1234 I don't see the field [c_Action_Date] in the given dataset.

FilipR

IF [c_Action_Date] <= "2021-11-13" AND

([202110] = 'Y' OR [202111] = 'Y' OR [202201] = 'Y' OR [202202] = 'Y')

THEN 'Y' ELSE '' ENDIF

New Workflow2aa.yxmd

wonka1234

@FilipR would you be able to include my full formula with yours?

please see attached.

@wonka1234 Your formula doesn't make sense here:

You cannot have an OR statement in the result part (after THEN).

FilipR

@wonka1234 To get where you need you just add ELSEIFs, like this:

IF [c_Action_Date] <= "2021-11-13" AND ([202110] = 'Y' OR [202111] = 'Y' OR [202201] = 'Y' OR [202202] = 'Y')

ELSEIF [c_Action_Date] <= "2021-12-13" AND [202203] = 'Y'

THEN 'Y' ELSE '' ENDIF

Emmanuel_G

@wonka1234

I don't quite understand the purpose of saying you want 202110 or 202111 or 202201 or 202202.

For your logic to work, between each OR, you would have to do Boolean tests so [202110] = value OR [202111] = value OR [202201] = value and so on.

Do you want to have a result of all these concatenated columns? Because if you do so many "OR" it's because taking the result of one of the columns or another doesn't matter so it's better to choose only one at random among all these 4, that's it more sense no?

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177