If(And(Or

Paul Newcome

So the error is occurring when we specify to look for a date OR text.

What happens with this one?

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

If that doesn't work, try this one and let me know...

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

taylor.thompson89836

Ok, they both turn green when I put the date to 3/29, if I put UNF I still get the error!

Paul Newcome

Is it "UNF" or "UFN"?

taylor.thompson89836

Sorry, its UFN and that's what I've been typing in the sheet. Just typed too fast there.

Paul Newcome

Don't ask me WHY, but I have seen this work before... Lets flip the criteria around so specific text is first and date is second within the OR...

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

taylor.thompson89836

Haha, funny you say that, because I have been trying that with some of the formulas you have sent. It's still giving the same error when it has UFN.

Paul Newcome

Hmm... Well I am going to have to think on this some.

For now though need to start getting ready for my "daddy/daughter date night". I'll be sure to revisit first thing Monday morning though. Don't give up just yet.

taylor.thompson89836

Thank you so much for helping me and have so much fun with your daughter!

I'll continue to brainstorm this as well!

Paul Newcome

@taylor.thompson89836 I am thinking you may want to go ahead and reach out to SUPPORT on this one. It may very well be that there is some corrupt data somewhere in the back-end of things. Definitely include a link to this thread in your ticket. That way they can see what troubleshooting steps you have taken already.

Sorry I couldn't get it figured out for you, and definitely feel free to revisit with what they say.

taylor.thompson89836

Thank you so much for all you help! I'll reach out to them now.

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.

taylor.thompson89836

He pretty much said that because it was a date column and I was trying to put text into it, it was breaking it.

Thank you for all your help on this!

Paul Newcome

I've done it before and even tested on my own sheet when trying to help you troubleshoot (and it didn't break then), but I digress. I'm just glad you were able to get it working. 👍️

Don't forget to flag the most appropriate response(s) (those with working solutions) as "helpful" (even yours) so that others encountering the same issue can know that a possible solution may be found here.