# Automating RGYB Using Dates and % Complete

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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

• Options

That solved my problem. I appreciate the help.

• ✭✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

Hi Daniel,

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.