Formula for Schedule Health with multiple criteria

Vivien Chong
Vivien Chong ✭✭✭✭✭✭
edited 12/30/22 in Formulas and Functions

I am working on a formula for Schedule Health:

1. when Status is failed or on-hold or cancelled or when Schedule is delayed => RED

2. when Status is Pass or Complete or In-progress or when Schedule is on-track => GREEN

3. No information at STATUS column, no symbol

How should I write the formula at Schedule Health?

Any assistance is appreciated.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Vivien Chong

    IF(Status@row="", "", IF(OR(Status@row="failed", Status@row="on-hold", Status@row="cancelled", Schedule@row="delayed"), "Red", IF(OR(Status@row="Pass", Status@row="Complete", Status@row="In-progress", Schedule@row="on-track"), "Green")))

    Make sure the words in the quotes exactly match ,including the case, of your drop-down selections.

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Vivien Chong

    IF(Status@row="", "", IF(OR(Status@row="failed", Status@row="on-hold", Status@row="cancelled", Schedule@row="delayed"), "Red", IF(OR(Status@row="Pass", Status@row="Complete", Status@row="In-progress", Schedule@row="on-track"), "Green")))

    Make sure the words in the quotes exactly match ,including the case, of your drop-down selections.

    Does this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!