Automating RYG Status Balls Based Off Two Date Columns

I have two columns "Actual Start Date" and "Actual End Date", I want to have a formula for the status column using the RYG Status balls that will:

- Red when nothing is in either of the two cells

- Yellow when there is a date in the "Actual Start Date" Column 

- Green when a date has been entered into both

Please help



  • Hello,

    You'll want to create a nested IF statement, for example:

    =IF(AND(ISDATE([Actual Start Date]1), ISDATE([Actual End Date]1)), "Green", IF(AND(ISDATE([Actual Start Date]1), ISBLANK([Actual End Date]1)), "Yellow", IF(AND(ISBLANK([Actual Start Date]1), ISBLANK([Actual End Date]1)), "Red"))) 

    The above formula will set the RYG to green if there's a date in both cells, yellow if there's a date in Actual Start Date AND the Actual End Date is blank, red if both are blank.

    More information on the IF statement can be found in the help center:

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!