I have multiple nested formulas but one doesn't return anything... Help

SSullivan

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?

Nic Larsen

Try this:

=IF([% Complete]@row = "X", "On Hold / Cancelled", IF([% Complete]@row = 1, "Complete", IF(AND(OR(ISBLANK([% Complete]@row), [% Complete]@row = 0), Start@row > TODAY()), "Not Started", IF(AND(OR([% Complete]@row > 0, [% Complete]@row < 1), Finish@row > TODAY()), "In Progress", IF(OR(ISBLANK([% Complete]@row), Start@row < TODAY()), "Behind", IF(AND(AND([% Complete]@row > 0, [% Complete]@row < 1), Finish@row < TODAY()), "Behind"))))))

SSullivan

THANK YOU! That worked.