Automating RGYB Using Dates and % Complete

Daniel Melton

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

Mike Wilday

Give this a try.  

=IF(NOT(ISDATE(Finish@row)), "Green", 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")))))

Daniel Melton

That solved my problem. I appreciate the help. 

Mike Wilday

Awesome. I'm so glad I could help you out! 

Andrée Starå

Hi Daniel,

To add to Mike's excellent advice.

You can use @row in the formula instead of the row number if the formula is on the same row. Then you won't need to think about row numbers, and it's more efficient and takes less processing power.

Here's the formula with @row instead.

=IF(NOT(ISDATE(Finish@row)), "Green", IF([% Complete]@row = 1, "Blue", IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row = TODAY(), [% Complete]@row < 1), "Yellow", IF(AND(Finish@row > TODAY(), [% Complete]@row < 1), "Green", "Blue")))))

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD