Automating RGYB Using Dates and % Complete

Daniel Melton
Daniel Melton ✭✭
edited 12/09/19 in Smartsheet Basics

I am currently creating a project template that I wanted to add some automation into. The first piece of that automation is to change the RGYB using the finish date and % complete. The formula that I currently have works great when you have dates in the cells, but that won't always is the case when a task is being added. 

 

What am I missing to have the ball green when there is no date?

 

=IF([% Complete]4 = 1, "Blue", IF(AND(Finish4 < TODAY(), [% Complete]4 < 1), "Red", IF(AND(Finish4 = TODAY(), [% Complete]4 < 1), "Yellow", IF(AND(Finish4 > TODAY(), [% Complete]4 < 1), "Green", "Blue"))))

 

Thanks,

 

Dan

Comments