Automate RYG Balls based on Status and then date

edited 12/09/19 in Formulas and Functions

Does anyone have a formula that looks at a line status (Not Started, In Progress, Complete) and then determines whether it is after the due date (red), <7 days (yellow) , or >7 days (green)?

Complete would need to turn green regardless of the date while not started and in progress would look at the date parameters.

Thanks in advanced as I am new to Smartsheet and writing these formulas!



  • Dominik Weller
    edited 03/06/18

    Hi apmarcotte,

    you can use the following formula that should give you the result you need. For your sheet you need to rename the coloums ([Line Status], [Due Date]) in the formula:

    =IF([Line Status]1 = "Complete"; "green"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green")))

    Rows with empty dates will be shown as yellow, if you want them to be red, you should use:

    =IF([Line Status]1 = "Complete"; "green"; IF(ISBLANK([Due Date]1); "red"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green"))))


    PS: Maybe you have to exchange all semicolons ; with commas , ... Since I use german language settings, this may not work for you.


  • apmarcotte

    Thanks Dominik,

    This worked great. 

  • LaurieCass

    @Dominik Weller Thank You!! I was struggling to write this formula, so glad I came across this thread.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!