If(And(Or

I'm trying to create a formula that includes If, And, and Or in it and I'm having trouble figuring out the correct way to write it.

Written out what I need is:

If "restrictions in place through" is greater than 3/28/2020 OR equals UFN AND "max # of people.." is greater than 0 make "local ordinance status" yellow

and

If "restrictions in place through" is greater than 3/28/2020 OR equals UFN AND "max # of people.." is equal to 0 make "local ordinance status" red

I've been trying to write the just the first part of this first to make sure I have it correct and so far have:

=IF(AND([Restrictions In Place Through:]@row >= DATE(2020, 3, 28), OR([Restrictions In Place Through:]@row = "UFN"), [Max # of people allowed to gather]@row > 0), "Yellow", "Red")


I also tried it with the OR at the beginning as well and couldn't get it to work. Any help would be appreciated!




Best Answers

  • They were able to get it to work for me with this:

    =IF(AND([Max # of people allowed to gather]@row = 0, IFERROR([Restrictions In Place Through:]@row > DATE(2020, 3, 28), [Restrictions In Place Through:]@row = "UFN")), "Red", IF(AND([Max # of people allowed to gather]@row > 0, IFERROR([Restrictions In Place Through:]@row > DATE(2020, 3, 28), [Restrictions In Place Through:]@row = "UFN")), "Yellow", "Green"))

«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Lets give something like this a try...


    =IF(OR([Restrictions In Place Through:]@row >= DATE(2020, 3, 28), [Restrictions In Place Through:]@row = "UFN"), IF([Max # of people allowed to gather]@row > 0, "Yellow", "Red"), "Green")

  • I'm unfortunately still getting an #invalid operations error.

    I'm also not sure how to add in the second part to the formula: If "restrictions in place through" is greater than 3/28/2020 OR equals UFN AND "max # of people.." is equal to 0 make "local ordinance status" red

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What column type is [Restrictions In Place Through:]?


    The second part of the formula is built in already by setting that as the output if the number is not greater than zero but the OR is still true.

  • ohhh, I see!

    The Restrictions in place through column is a date column, but not restricted to dates only.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... Lets try breaking it down for some trouble shooting because it should be working. I even tested it in my own sheet...


    Try this first:

    =IF([Max # of people allowed to gather]@row > 0, "Yellow", "Red")


    Then try this:

    =IF(OR([Restrictions In Place Through:]@row >= DATE(2020, 3, 28), [Restrictions In Place Through:]@row = "UFN"), "Green")


    What are the results?

  • The first part worked! It's something with the second piece that is causing the error, but I can't seem to find what's causing it.

  • If I remove the UFN from the restrictions in place column, it removes the error message, but makes the cell blank, with no color icon at all. If I add a date in, then the formula works. For some reason putting UNF breaks the formula

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Well the good news is that we are narrowing down the issue.


    In the [Restrictions In Place Through:] column... How is that data entered?

  • I have multiple people working on the sheet and we are all just going in and manually typing in the date or UNF.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So leaving it as blank if the cell is blank is expected when using just the second test. Adding in UFN into the cell should not break the formula though. That is odd and most certainly NOT expected behavior.


    Log out. Clear your browser's cache and cookies. Then log back in and see if it is still doing it.

  • Still getting an error 😔I have messed with this so much that it feels like Smartsheets is doing this just to mess with me now. haha

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... Ok. I have been seeing formulas breaking lately that SHOULD be working all over the place here in the Community.


    Is "UFN" the ONLY text that would be entered into the date column, or is it possible that some other text could be entered? If "UFN" is the only thing other than a blank or a date that could be populated... Lets try this...


    =IF(OR([Restrictions In Place Through:]@row >= DATE(2020, 3, 28), ISTEXT([Restrictions In Place Through:]@row)), "Green")


    And also


    =IF([Restrictions In Place Through:]@row = "UFN", "Green")


    And also


    =IF(ISTEXT([Restrictions In Place Through:]@row), "Green")



    When you enter "UFN" into the column, all three SHOULD populate a "Green".

    NOTE: This is just for testing. We will adjust the colors appropriately once we get this part figured out. One thing at a time...

  • UFN, blank, or date will be the only things entered into the restrictions column.

    I tested all 3 and the only one that still gave me an #invalid error was the first one. The other 2 worked!

    Thank you so much for troubleshooting this with me, by the way!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Then we are getting closer yet...

    =IF([Restrictions In Place Through:]@row >= DATE(2020, 3, 28), "Green")


    What does that do when you put in a date that meets the criteria?

  • It turned Green when I changed the date to 3/29!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!