RYGG nesting functions-UPDATED

jinden@wchealth

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

1. =IF(OR(Status@row = "Barrier", AND([Due Date]@row < TODAY(), NOT(Status@row = "Completed"))), "Red")
2. =IF(AND([Due Date]@row > TODAY(+3), Status@row = "Not Started"), "Yellow")
3. =IF([Status]@row="completed","Green")
4. =IF([DueDate]@row=&lt;TODAY(+2), "Grey")

We then combined #2&4 into one and came up w/ the following revision (all tested properly on its own)

REVISION

1. IF([STATUS]@row="completed"), "Green"
2. IF(AND([Due Date]@row&lt;TODAY(+3),[STATUS]@row="Not Started"),"Yellow", "Gray")
3. 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?

Nic Larsen

Try this:

=IF(Status@row = "Completed", "Green", IF(AND([Due Date]@row > TODAY(+3), Status@row = "Not Started"), "Yellow", IF([Due Date]@row <= TODAY(+2), "Grey", "Red")

Andrée Starå

Hi,

Try something like this.

=IF(ISBLANK([Due Date]@row),"";IF(OR([STATUS]@row="Barrier",AND([Due Date]@row&lt;TODAY(),NOT([STATUS]@row="Completed"))), "Red",IF([STATUS]@row="completed", "Green",IF(AND([Due Date]@row&lt;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