Multiple IF AND OR in same formula
Hello,
I`m trying to do something like this :
7 Status
IF(Action@row = "", "",
IF([% Complete]@row = 1, "Complete",
IF(Owner@row = "", "Not assigned",
IF(AND([End Date]@row = "", [% Complete]@row = 0), "Cancelled",
IF(AND([% Complete]@row < 100, TODAY() > [End Date]@row), "Overdue",
IF(OR([% Complete]@row <> 0, [End Date]@row >= TODAY()), "In Progress",
IF(AND([% Complete]@row = 0, [End Date]@row >= TODAY()), "Not Started",
The rules i need are:
- blank( "" ) if [Action]@row is blank
- "Complete" if [% Complete]@row is 100%
- "Not assigned" if the [Owner]@row is blank
- "Cancelled" if [End Date]@row = "" AND [% Complete]@row = 0%
- "Overdue" if [% Complete]@row <100% AND Today()>[End Date]@row
- "In Progress" if [% Complete]@row <> 0% OR [End Date]@row>= Today()
- "Not Started" if [% Complete]@row =0% AND current date is between start date and end date
Please help me understand what i`m doing wrong here ...
Thanks in advance for any help.
Regards. L
Best Answer
-
I think i`ve got it myself :
=IF(Action@row = "", "", IF([% Complete]@row = 1, "Complete", IF(Owner@row = "", "Not assigned", IF(AND([% Complete]@row = "0", [End Date]@row = ""), "Cancelled", IF(AND([% Complete]@row = 0, [End Date]@row >= TODAY()), "Not Started", IF(AND([% Complete]@row < 100, TODAY() >= [End Date]@row), "In Progress", "Overdue"))))))
This is giving me the results i was expecting.
Please let me know if there is another way to do it .
Thanks.
Answers
-
Hi L,
What error message do you get?
Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The multiple cases do not function correctly , In progress and Overdue does not function ok. The Cancelled rule doesn`t seem to apply at all. Sorry i can`t share the data.
-
Ok. Are they in the right order? It goes from the left and stops as soon something is true.
Make sense?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Please find a print screen
I hope this helps.
Thanks.
-
I think i`ve got it myself :
=IF(Action@row = "", "", IF([% Complete]@row = 1, "Complete", IF(Owner@row = "", "Not assigned", IF(AND([% Complete]@row = "0", [End Date]@row = ""), "Cancelled", IF(AND([% Complete]@row = 0, [End Date]@row >= TODAY()), "Not Started", IF(AND([% Complete]@row < 100, TODAY() >= [End Date]@row), "In Progress", "Overdue"))))))
This is giving me the results i was expecting.
Please let me know if there is another way to do it .
Thanks.
-
Excellent!
Glad you got it working!
✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!