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

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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

This discussion has been closed.