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 help? 👀 | 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
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!