formula for status RYG change


trying to write a formula for the RYG dot to change in Status column:

IF OpenDeliverables is <=0, Green.. & IF OpenDeliverables is > 0 AND DATE is within 30 days, then yellow, within 14 days or paste DATE, then RED

Best Answer


  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hi @JVL ,

    I have partially tested your formula and it works this far -

    No, to add the date condition in the same, try this -

    Hope this helps,



    Ipshita Mukherjee

  • JVL
    JVL ✭✭

    Thank You Ipshita.

    I amusing the Open Deliverable status formula already until get a more complex formula working.

    Date formula: to get a status Yellow if Today is within 30 days of Date, Red if within 14 days or past Date. Using this formula for now now: =IF(Date@row > Today(-14), "Yellow", IF(Date@row <= Today(-14), "Red", "Green"))

    Do you think I can possibly combine the two formulas using an AND or an OR formula?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @JVL Try this out. It incorporates the open deliverables criteria and the date criteria.

    =IF([Open Deliverables]@row <= 0, "Green", IF(AND([Open Deliverables]@row > 0, Date@row > Today(-14)), "Yellow", IF(AND([Open Deliverables]@row > 0, Date@row < Today(-14)), "Red", "")))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • JVL
    JVL ✭✭

    Thank you Jeff!!! this works!!

    Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!