RGBY Ball and Date Formula

Aimee Colton

Hello,

I am trying to write a formula that can work out the following

RED - if the start date has past and the complete % is 0

BLUE - if the start date is in the future and the complete % is 0

YELLOW - if complete % is 1-99

GREEN - if complete % is 100

Mike Wilday

IF formulas are the way to go. They always go from left to right and stop on the first true answer.

Try:

IF([Complete %]@row = 1, "Green", IF(AND([Start Date]@row < Today(), [Complete %]@row = 0), "Red", IF(AND([Start Date]@row >= Today(), [Complete %]@row = 0), "Blue", "Yellow")))

Let me know if you have any issues.

Aimee Colton

Hi Mike,

I receive #unparseable when I added the formula to a cell

Genevieve P.

Hi Aimee,

Mike will likely say the same thing... but check your column names to what's written in the formula - they will need to be exact matches:

• Complete %
• Start Date

If this doesn't help, could you post a screen capture of your sheet/column names? (Please remove any sensitive data) And the formula as you pasted it into your sheet?

Thanks!

Mike Wilday

Yep. Always use the exact column names as they are in your headers. Screenshots of your formula and the header rows will help.

Mike Wilday

What are your header names for the start date and complete % columns?