Status RYGG Automated Formula

allisona

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

J. Craig Williams

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