Status for At Risk tasks due in 7 days

edited 12/09/19 in Formulas and Functions

I have 4 status categories for my project with respective RYGB balls:

  • Complete (blue)
  • At Risk (red)
  • Requires Attention (yellow)
  • On Track (green)


My current formula in the Health column automatically changes the RYGB balls when the status is changed manually:

=IF(Status2 = "On Track", "Green", IF(Status2 = "Complete", "Blue", IF(Status2 = "Requires Attention", "Yellow", IF(Status2 = "At Risk", "Red"))))


However, I would like to incorporate some automation in the Status column:

1) Status becomes "At Risk" when % Complete is less than 100% and Finish Date is within 7 days

2) Status becomes "On Track" with any other % Complete status less than 100% as long as Finish Date is outside 7 days


The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation.


I have created this formula based on my research but it is coming back as unparseable:

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))


Any help would be greatly appreciated!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!