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
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives