RYG formula based on completion time and status


Hi, I would like to have a formula that shows me RYG status based on below criteria:

-if status = completed and [Actual Finish] = [Baseline Finish Date] then green

-if status work in progress (wip) or not started and [Actual Finish] is less than 14 past today then also green

-if status wip or not started and [Actual Finish] is between 14 and 28 days past today then yellow

--if status wip or not started and [Actual Finish] is more than 28 days past today then red

I built the below formula to reflect that but it doesn't work:

=IF(OR(AND(Status585=”completed”, [Actual Finish]585=[Baseline Finish Date]585), OR(and(Status585="wip", TODAY()-[Actual Finish]<14), Status585="not started",TODAY()-[Actual Finish]585 < 14)), “Green”, IF(AND(TODAY()-[Actual Finish]585<28,TODAY()-[Actual Finish]585>14),"Yellow","Red"))

Could You give me an idea what's wrong with it?



Best Answer


  • Monika Tomczak

    Hi Genevieve,

    Thanks a lot for Your support, that resolution works perfectly.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Monika,

    That's great to hear! I'm glad it works for you 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!