If(And(Or

taylor.thompson89836

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!

taylor.thompson89836

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"))

Paul Newcome

Ah. Ok. I see where they used the IFERROR statement, but it still doesn't explain why the OR statement wasn't working.

Did they give any indication as to why the original was not working (as it should have been)?

We can also shorten it up a bit like this as well...

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

Basically this takes the IFERROR piece that is required in both AND statements and says that if it is true, look at the IF based on the Max # to drive red or yellow, and if it is not true then go to green.

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")

taylor.thompson89836

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

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.

taylor.thompson89836

ohhh, I see!

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

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?

taylor.thompson89836

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.

taylor.thompson89836

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

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

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

taylor.thompson89836

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

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.

taylor.thompson89836

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

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

taylor.thompson89836

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

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?

taylor.thompson89836

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