IF OR/AND FORMULA

Donni Matthew · 02/25/22

I'm trying to write a formula that does this in english:

If the Due Date is greater than the Quarter Helper Column OR if the Due Date is blank & today is within 15 days of the Quarter Helper Column, then flag Red, otherwise no flag.

I'm getting an invalid operation message with this formula: =IF(OR([Due Date]6 > [Quarter Helper Column]@row, AND([Due Date]6 = "", (TODAY(15) > [Quarter Helper Column]@row))), 1, 0)

Jeff Reisman · 02/25/22

It may just be a matter of embedding your OR inside your AND. Doing that tells the system "If this is true and one of these two other things are true, then 1, otherwise, 0". Probably need to change how you determine the 15 days criteria, too, by listing the cell to evaluate and then the date value. Try this:

=IF(AND([Quarter Helper Column]@row < TODAY(15), OR([Due Date]6 > [Quarter Helper Column]@row, ISBLANK([Due Date]6))), 1, 0)

Jeff Reisman · 02/25/22

https://community.smartsheet.com/discussion/comment/320842#Comment_320842

Of course if you want this to be flagged if Today is within 15 days before or after the Quarter Helper Column, just add another logical expression inside the AND:

=IF(AND([Quarter Helper Column]@row < TODAY(15), [Quarter Helper Column]@row > TODAY(-15), OR([Due Date]6 > [Quarter Helper Column]@row, ISBLANK([Due Date]6))), 1, 0)

Donni Matthew · 02/25/22

I'm still getting the Invalid Operation error. I literally copied/pasted both formulas you suggested.

Jeff Reisman · 02/25/22

Strange...

Try retyping your operators (>, <, -, etc) and make sure the color-coding on the parentheses matches up.

If all else fails, retype the whole formula from scratch in the cell. You could also stick each condition inside it's own IF statement to check if you get the expected result.

Formula Error Messages | Smartsheet Learning Center

#INVALID OPERATION

Cause

Operators (see Create and Edit Formulas for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...

=IF(Quantity1 =< 25, "Low Stock")

...has the "less than or equal" to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and Edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Jeff Reisman · 02/25/22

Just FYI, I ran a very similar formula in a flag column in a test sheet of mine, and it works:

IF OR/AND FORMULA

Answers

#INVALID OPERATION

Cause

Resolution

Categories