Formula Help for IF/And/Or
I'm trying to write a formula which sets the At Risk Flag.
If the Status="At Risk" the flag should be set
Or
If the End Date is Greater than Today AND the Status is not equal to "Complete" the Flag should be set.
Otherwise, the flag should not be set.
Here's what I came up with. It works correctly when the date is after today and the status is not equal to complete, but the flag does not set if the status is changed to At Risk.
=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), OR(Status@row = "At Risk", 1, 0))
Any help is appreciated!
Ed
Answers
-
This is a common error when combining ORs and ANDs. You need your AND statement to be one of the options inside the OR statement:
=IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)
In English: If the status is "At Risk," OR if both the end date is before today AND the status is not "Complete," set the flag, otherwise do not set the flag.
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!
-
Hi Jeff,
Thanks for the reply. I copied the formula and am getting the message #incorrect argument set. Any idea's what might cause that?
Thanks,
Ed
-
*Edited because I missed an end parentheses.
It always helps to share a screenshot of your formula as it appears while editing it in Smartsheet, with the color-coding.
But let's try this first, and if this doesn't work, share the screenshot:
=IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), Status@row <> "Complete")), 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!
-
That worked PERFECTLY! Thank you very much.
Ed
Help Article Resources
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
Check out the Formula Handbook template!