RYGG nesting functions-UPDATED

Options
edited 12/09/19

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&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"))))

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?

Tags: