Multiple IF statements (or similar) to drive RYGB

I am trying to write a formula that returns a Red/Yellow/Green based on 4 part. I've searched the community but nothing has helped so far. I've tried IF AND and IF INDEX formulas but I'm clearly getting something wrong in the logic or expression usage. UNPARSEABLE is my constant friend...

The example is for a project and I want to return an RYGB based on the Status, the Start Date and/or the End Date.

In descriptive form I want the formula to,

Return Blue if the Status = Not Started and Start Date > TODAY(), return Yellow if the Status = Not Started and Start Date is < TODAY(), Return Green if Status = In Progress and End Date < TODAY or Status = Completed, Red when Status <> Complete and End Date <= TODAY(-1)

My sheet is more or less like this:

I've been messing around with a variety of formula so if here is a better way I am completely open to suggestions...


Cheers

Best Answers

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓
    =IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))
    

    ...

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Wade in NZ

    This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:

    IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",


    What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.

    This should be higher in the order of operations:

    IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))


    Try:


    =IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

    IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

    IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

    IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",

    IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

    IF(Status@row = "Completed", "Green"))))))


    Cheers,

    Genevieve

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this:


    IF(OR(Status@row = "Completed", "Green", IF([End Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", IF([Start Date]@row>= TODAY(), "Blue", "Yellow")), "Green"))

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓
    =IF(AND(Status@row = "NOT STARTED", [Start Date]@row > TODAY()), "BLUE", IF(AND(Status@row = "NOT STARTED", [Start Date]@row < TODAY()), "YELLOW", IF(AND(Status@row = "IN PROGRESS", [Start Date]@row < TODAY()), "GREEN", IF(Status@row = "COMPLETED", "GREEN", IF(AND(Status@row <> "IN PROGRESS", [End Date]@row < TODAY(-1)), "RED")))))
    

    ...

  • Run into a slight snag when I implemented this formula in a project we knew to be going a bit sideways. My final formula is below and unfortunately it returns a Green when the task in In Progress but the Target Start Date is < TODAY and therefore overdue. The Overdue report picked up the task but, with the Schedule Health still showing Green, it is causing confusion. Somewhere my logic hierarchy is falling over...

    =IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

    IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

    IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

    IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

    IF(Status@row = "Completed", "Green",

    IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Wade in NZ

    This is exactly what your "Green" rule is looking for... if the task is "in progress" and the Start date is in the past:

    IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",


    What I would suggest doing is move your end "Red" statement up earlier, so it first checks the end date of the In Progress tasks before checking the start date.

    This should be higher in the order of operations:

    IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red"))))))


    Try:


    =IF(AND(Status@row = "Not Started", [Target Start Date]@row > TODAY()), "Gray",

    IF(AND(Status@row = "Not Started", [Target End Date]@row < TODAY()), "Red",

    IF(AND(Status@row = "Not Started", [Target Start Date]@row < TODAY()), "Yellow",

    IF(AND(Status@row = "In Progress", [Target End Date]@row < TODAY(-1)), "Red",

    IF(AND(Status@row = "In Progress", [Target Start Date]@row < TODAY()), "Green",

    IF(Status@row = "Completed", "Green"))))))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!