Hi All,
I'm trying to automate the status of a project plan. We have 4 statuses, Not started, In Progress, Behind, and On hold / cancelled.
Here's what I want to happen:
If [% Complete] is blank or = 0, and [Start] is in the future, then "Not started".
If [% Complete] is >0 and <1, and [Finish] is in the future, the "In progress".
If [% Complete] is blank and [Start] is in the past or if [% Complete] is <1 and [Finish] is in the past or if then "Behind".
If [% Complete] = 1, then "Complete"
If [% Complete] ="x", then "On Hold / Cancelled".
If % is >0 and less than 1, and [Finish]
is in the past, then "Behind".
The problem is no matter how I put it, the second Behind criteria does not work, I've even had Smartsheet generate the formula for me, and it still just returns a blank value for the second 'Behind' scenario.
Here is my most recent formula.
=IF(OR(ISBLANK([% Complete]@row), [% Complete]@row = 0), IF([Start]@row > TODAY(), "Not started", IF([Start]@row < TODAY(), "Behind", "")), IF(AND([% Complete]@row > 0, [% Complete]@row < 1), IF([Finish]@row > TODAY(), "In progress", IF([Finish]@row < TODAY(), "Behind", "")), IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = "x", "On Hold / Cancelled", ""))))
Everything works perfect except when [% Complete] is less than 1 and [Finish] is in the past.
I'm very frustrated…Any thoughts or even better, a solution?
