RYGG nesting functions-UPDATED
EDITED TO ADD:
I was able to figure out this morening w/ fresh eyes/brain and tested all scenarios which tested true:
=IF(OR([STATUS]@row="Barrier",AND([Due Date]@row<TODAY(),NOT([STATUS]@row="Completed"))), "Red",IF([STATUS]@row="completed", "Green",IF(AND([Due Date]@row<TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray"))))
BUT then i figured out i want task health to be blank if no due date is entered (i.e: no task essentially)
worked out and tested true: =IF([Due Date]@row = "empty", "blank")
Now i cant figure out how to add it into the above nesting...ugh
we're having trouble w/ the nesting of our functions
in working w/ my data analyst coworkers we wrote these original 4 functions (each tested properly on its own)
ORIG
- =IF(OR(Status@row = "Barrier", AND([Due Date]@row < TODAY(), NOT(Status@row = "Completed"))), "Red")
- =IF(AND([Due Date]@row > TODAY(+3), Status@row = "Not Started"), "Yellow")
- =IF([Status]@row="completed","Green")
- =IF([DueDate]@row=<TODAY(+2), "Grey")
We then combined #2&4 into one and came up w/ the following revision (all tested properly on its own)
REVISION
- IF([STATUS]@row="completed"), "Green"
- IF(AND([Due Date]@row<TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray")
- IF(OR([STATUS]="Barrier",AND([Due Date]<TODAY(),NOT([STATUS]="Completed"))), "Red"
BUT we're going crazy trying to figure out how Smartsheets wants it...anything nesting we do comes back Unparseable or Invalid Data Type any advice?
Comments
-
Hi,
Try something like this.
=IF(ISBLANK([Due Date]@row),"";IF(OR([STATUS]@row="Barrier",AND([Due Date]@row<TODAY(),NOT([STATUS]@row="Completed"))), "Red",IF([STATUS]@row="completed", "Green",IF(AND([Due Date]@row<TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray")))))
Did it work?
Hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!