Formula to change status symbol if a task is not completed before due date

edited 12/09/19 in Formulas and Functions

Thanks guys, new to Smartsheet and need some help... Having issues with changing the status symbol to Red, Yellow, Green, or Gray based on project completion percentage column (%Complete) and start/due date columns ("Start Date" / "End Date"). I would like the following:

Gray - If %Complete equals to 0 and start date has not begun

Green - If %Complete is less than 1 and end date is more than 5 working days away

Yellow - If %Complete is less than 1 and end date is within 5 working days 

Red - If %Complete is less than 1 and end date has passed


Current formula (not working): 

=IF(AND([% Complete]2 = 0,[Start Date]2 < TODAY, "Gray"), IF(AND[% Complete]2 < 1,[End Date]2 < Today (5)), "Green", IF(And([% Complete]2 = 1, [End Date]2>Today(5), "Yellow", If(And[%Complete]2<Today(2), "Red")))


Thanks in advance!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!