Multiple IF statements (or similar) to drive RYGB

Wade in NZ
Wade in NZ ✭✭
in Formulas and Functions

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:

image.png

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


Cheers

· Share on FacebookShare on Twitter

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")))))

    ...

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

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"))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • 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")))))

    ...

    · Share on FacebookShare on Twitter
  • Wade in NZ
    Wade in NZ ✭✭
    https://community.smartsheet.com/discussion/comment/328924#Comment_328924

    Awesome! Many thanks! Worked a treat!

    · Share on FacebookShare on Twitter
  • Wade in NZ
    Wade in NZ ✭✭

    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"))))))

    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!