Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Automating Grey Ball

edited 12/09/19 in Archived 2016 Posts

I am having an issue getting the grey ball to work when a task has been marked done. The formula that i have is below. If will only work if the date is older than today. If will change the balls to other colors depending on the dates even if the task is marked done. Any suggestions?


=IF(AND([Due Date]1 < TODAY(), Done1 = 0), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green", IF(AND([Due Date]1 < TODAY(), Done1 = 1), "Gray"))))







  • Brett Evans
    Brett Evans ✭✭✭✭✭✭

    I would test the statement IF(AND([Due Date]1 < TODAY(), Done1 = 1), "Gray") in a separate column and make sure that works.  It looks fine but that is the one way to be sure.  If it doesn't work in isolation then you can debug it there.


    If  it works as is  in isolation, move it to the first conditional in your nested if statement and try again.  If it works and another conditional further down doesnt, you need to fix the nested if.



This discussion has been closed.