RGBY Ball and Date Formula

Aimee Colton · 03/11/20

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 · 03/11/20

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 · 03/12/20

Hi Mike,

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

Genevieve P. · 03/12/20

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 · 03/12/20

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 · 03/12/20

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

RGBY Ball and Date Formula

Answers

Help Article Resources

Categories