How do I adjust formula to exclude Status Complete

NMillerTX

I am completely new to making formulas in Smartsheet. I searched the flag formulas in the forum and found this one to do what I need to do, except I need it to exclude flagging Status = Complete. I'm not sure how to adjust to omit.

Thanks in advance! I will start reading up on how to create formulas.

Kelly Moore

Hey @NMillerTX

Because a 'Complete' status trumps any condition, my preference is to separate it from other conditions and have it stand alone. Formulas execute from left to right until the first True condition is encountered, then the formula stops. Because we want the formula to stop when a Complete is reached, we will place that condition first in the IF statement.

=IF(Status@row="Complete", 0, IF(AND([End Date]@row<=TODAY(7), [End Date]@row>=TODAY()),1))

Kelly

Kelly Moore

Hey @NMillerTX

Because a 'Complete' status trumps any condition, my preference is to separate it from other conditions and have it stand alone. Formulas execute from left to right until the first True condition is encountered, then the formula stops. Because we want the formula to stop when a Complete is reached, we will place that condition first in the IF statement.

=IF(Status@row="Complete", 0, IF(AND([End Date]@row<=TODAY(7), [End Date]@row>=TODAY()),1))

Kelly

NMillerTX

Perfect. Thanks so much!!

Now time for me to study more formulas!

OnionsAndGarlic

Unfortunately, I got "#UNPARESABLE". Any thoughts?

Genevieve P.

Hi @OnionsAndGarlic

Your column names are different than the formula above. You'll need to update what's in [these] with what's in your sheet, for example:

=IF(Status@row="Complete", 0, IF(AND(Finish@row <=TODAY(7), Finish@row >=TODAY()), 1))

Cheers,

Genevieve