Automating Grey Ball

djm714

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"))))

 

 

Thanks,

 

Dan

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.