RAG Formula with Reference to Start, end date and Status

Hi, I am aiming to create a RAG status referencing to three columns in a sheets. Wondering if anyone would be able to help me creating a formula that covers the following scenarios:

By the Work Start Date:

Not Started = Red

In Design = Amber

In Development / In Testing / Delivered = Green

By the Work End Date:

Not Started / In Design = Red

In Development / In Testing = Amber

Delivered = Green.

Many many thanks!!!


  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭
    edited 05/18/22

    Hello Yaya,

    Something like this ?

    =IF(TODAY() = [Work Start Date]@row; IF(Status@row = "Not Started"; "Red"; IF(Status@row = "In Design"; "Yellow"; IF(OR(Status@row = "In Development"; Status@row = "In Testing"; Status@row = "Delivered"); "Green"))); IF(TODAY() = [Work End Date]@row; IF(OR(Status@row = "Not Started"; Status@row = "In Design"); "Red"; IF(Status@row = "Delivered"; "Green"; IF(OR(Status@row = "In Development"; Status@row = "In Testing"); "Yellow")))))

    Of course you can/must adjust the TODAY in comparison with the StartDate and EndDate

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!