RYGG nesting functions-UPDATED

jinden@wchealth ✭✭✭
edited 12/09/19 in Formulas and Functions


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)


  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)


  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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!