Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Status RYGG Automated Formula
Hello,
I am trying to create a status column which updates automatically based on the following criteria:
- Green (Task is Complete): “% Complete” column = 100%
- Yellow (Task is In Progress): Today’s Date is less than “Planned End Date”, and “% Complete” is less than 100%
- Red (Task has not Started on Schedule): Today’s Date is past “Planned Start Date”, and “% Complete” is 0% or Blank
- Red (Task has not Finished on Schedule): Today’s Date is past “Planned End Date”, and “% Complete” is less than 100%
- Gray (Task has not started): Today’s Date is less than “Planned Start Date”, and “% Complete” is 0% or Blank
My formula for this is the following:
=IF(AND(OR(ISBLANK([% Complete]10), [% Complete]10 = 0), TODAY() >= [Start Date]10), "Red", IF(AND([% Complete]10 < 1, TODAY() > [End Date]10), "Red", IF(AND(AND([% Complete]10 > 0, [% Complete]10 < 1), TODAY() <= [End Date]10), "Yellow", IF(AND(OR(ISBLANK([% Complete]10), [% Complete]10 = 0), TODAY() < [Start Date]10), "Gray", "Green"))))
However, when I use this formula, all tasks that should be "Not Started"/"Gray" show up as red, and I can't figure out where in the formula I've gone wrong, can anyone help?
Thanks!
Andrew
Comments
-
Order is important.
I think this will do what you want:
=IF([% Complete]23 = 1, "Green", IF(AND(TODAY() < [Planned Start Date]23, [% Complete]23 = 0), "Gray", IF(AND([Planned End Date]23 >= TODAY(), [% Complete]23 > 0), "Yellow", "Red")))
for row 23
See this (available this week only)
https://app.smartsheet.com/b/publish?EQBCT=06236177d2c14c0e9bb2fbb77739863b
Craig
