RYGB Based on two date columns

Hi All,

I have a formula that I can't get right and looking for help with the following:

Green Status = If End Date is in the future and Status is "In Progress"
Yellow Status = If Start Date has begun and the Status is "To Do", Backlog", or "Planning"

Red Status = If End Date has passed and the status is "In Progress" or "On Hold"; If project has no Start Date OR End Date and status is "In Progress" or "On Hold"

Blue Status = If there is no start date and the status is in "To Do", or "Backlog"

Any help would be appreciated, thank you!

Best Answers

  • Gia Thinh
    Gia Thinh Community Champion
    Answer ✓

    Hi @MMorgan

    Try this formula:

    =IF(AND([End Date]@row > TODAY(), Status@row = "In Progress"), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))

    Hope it works for you.

    Gia Thinh Technology - Smartsheet Solution Partner.
    Email : thinh.huynh@giathinh.tech

  • MMorgan
    MMorgan
    Answer ✓

    @Gia Thinh

    This is great! It works! Thank you so much

Answers

  • Gia Thinh
    Gia Thinh Community Champion
    Answer ✓

    Hi @MMorgan

    Try this formula:

    =IF(AND([End Date]@row > TODAY(), Status@row = "In Progress"), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))

    Hope it works for you.

    Gia Thinh Technology - Smartsheet Solution Partner.
    Email : thinh.huynh@giathinh.tech

  • MMorgan
    MMorgan
    edited 02/04/25

    This is great! Thank you @Gia Thinh. I just ran a test and all items work, except for On Hold when the end date is in the future. Can you possibly add if it is on hold but with a future end date, the status is green. @https://giathinh.tech/wegrid/

    Again, i really appreciate this!

  • Gia Thinh
    Gia Thinh Community Champion

    @MMorgan

    Try this updated formula:

    =IF(AND([End Date]@row > TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))

    Gia Thinh Technology - Smartsheet Solution Partner.
    Email : thinh.huynh@giathinh.tech

  • MMorgan
    MMorgan
    Answer ✓

    @Gia Thinh

    This is great! It works! Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!