IF OR/AND FORMULA
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)
Answers
-
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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I'm still getting the Invalid Operation error. I literally copied/pasted both formulas you suggested.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Just FYI, I ran a very similar formula in a flag column in a test sheet of mine, and it works:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives