Using the OR function to select a fixed Value in a cell that contains specific characters.

Rick Girard

I want to insert an OR statement to choose between 2 choices in a cell, have tried a few variations of the or but i receive an INVALID SYNTAX or UNPARSEABLE response:

I had some success with the logic using this string but it requires BOTH choices (Stage1 , Stage 2) to be in the cell, I want to create a flag condition if one or the other exists, not both....

=IF(CONTAINS("Stage 1", Function@row), IF(CONTAINS("Stage 2", Function@row), NETDAYS(Date@row, TODAY())))

I tried inserting the OR function into the string but I've not been successful in creating a correct formula;

=IF(OR(CONTAINS("Stage 1", Function@row), IF(CONTAINS("Stage 2", Function@row), NETDAYS(Date@row, TODAY())))

Appreciate any assistance on this, am sure its something simple I'm just not thinking thru..

Kelly Moore

Hey @Rick Girard

The IF(OR() function has the syntax IF(OR(criteria 1, criteria2), true, false)

Try this

=IF(OR(CONTAINS("Stage 1", Function@row), CONTAINS("Stage 2", Function@row)), NETDAYS(Date@row, TODAY()))

Does this work for you?

Kelly

Rick Girard

It work! thank you Kelly (I'm terrible with parens, never seem to quite know how to place them each time)..

Rick Girard

Hello Kelly,

in my test sheet this works fine, however, when i move it to my production sheet i get a '-' value in front of the number.

=IF(OR(CONTAINS("Stage 1", Stage@row), CONTAINS("Stage 2", Stage@row)), NETDAYS([Close Date]@row, TODAY()))

the only thing that changed were the COLUMN NAMES. The NETDAYS date is derived from a different sheet using an INDEX MATCH string and that column property is a Date field. I removed the INDEX string and manually entered in a date in that cell and got the same response in the sum (ex. -28 instead of 28). Is there a way for me to reverse this to a positive number?

Kelly Moore

Hey Rick

The syntax of the NetDays is NETDAYS( start_date, end_date). If your [Close Date] is in the future, you will need to swap the positions of the TODAY() and [Close Date] within your formula. If the timing can vary - that is sometimes it's in the future and sometimes in the past, we can account for that in the formula. Just let me know.

Here's the swapped date formula

=IF(OR(CONTAINS("Stage 1", Stage@row), CONTAINS("Stage 2", Stage@row)), NETDAYS(TODAY(),[Close Date]@row))

Kelly