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

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..

Answers

  • Kelly Moore
    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
    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
    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!