Nested and/or formula

I am trying to set an at risk flag when a date is in the past and the status is not cancelled or complete. I have used the following formula in Excel and it works, however, I cannot make it work in Smartsheet. Any advice?
=IF(AND([Due Date]@row < TODAY(), OR(Status@row="Cancelled", Status@row="Complete")), 0, 1))
I did get the following formula to work for just one variable for the status, but I am still unable to get it to work when I try to make it flag if the date is in the past and if the status is anything other than Complete or Cancelled.
=IF(AND([Due Date]@row < TODAY(), Status@row<>"Complete"), 1, 0)
Best Answer
-
That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.
=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)
Answers
-
The below says, If the actual ship date is blank, the cell equals "in-progress", or if the actual ship date is before today, the cell equals "complete".
=IF(ISBLANK([Actual Ship Date]@row), "in-progress", IF([Actual Ship Date]@row < TODAY(), "complete", "in-progress"))
You can also set this to be within 10 days of TODAY to flip a flag risk.
-
Hi @JHone
It looks like you're just missing a closing parentheses in the right place. You need to close off the AND before stating the 0
Try this:
=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Cancelled",Β Status@row = "Complete"))), 0, 1)
Cheers,
Genevieve
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
-
That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.
=IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 206 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!