RYG Balls Need a Little Help Please

Twyla Booker-Kincaid

I have contacted the help desk and they suggested for me to post a question on the forum....

1. Get help with RYG Balls....

If start date is a date with no due date and at any % completed then yellow...

If due date is 3 days in the future then Yellow....

If due date is today Red......

if due date is tomorrow yellow .....

If due date is 4 days in the future then green or blue......

if no start or due date then blank......

If 100% complete with any or no start or due date then blank.....

with any due date in the FUTURE if % Completion is 50% - 99% then yellow

if 0% - 49% what ever it was originally.......

if due date in past at whatever % it is red....

Here is my code that I am using now but I am missing a few things.... Can you please look over my code and fix, change , help. Please.

PS. this is for the child rows if that info is needed.

IF(ISBLANK([Start Date]2), "Blue", IF([% Completion]2 <> 1, IF(TODAY() - [Due Date]2 > -1, "Red", IF(TODAY() - [Start Date]2 > -1, "Yellow", IF(TODAY() - [Due Date]2 > -3, "Yellow", "Green")))))

Thank you in advanced for all the help:)

Twyla

Genevieve P.

Hi Twyla,

The first thing I did was group all the instructions for each colour together, then sorted out the order I wanted them to be read. (Logic formulas read left-to-right and stop as soon as their criteria has been met). 

Let’s start with your blank rules:

if no start or due date then blank......

If 100% complete with any or no start or due date then blank.....

I presume you mean if there is both no Start and no Due date (since some of your other rules specify a blank due date).

=IF(OR([% Completion]@row = 1, AND([Start Date]@row = "", [Due Date]@row = "")), ""

All of the Yellow rules:

If start date is a date with no due date and at any % completed then yellow...

If due date is 3 days in the future then Yellow....

if due date is tomorrow yellow .....

with any due date in the FUTURE if % Completion is 50% - 99% then yellow

IF(OR(AND(ISDATE([Start Date]@row), [Due Date]@row = “”), AND([Due Date]@row >= TODAY(1), [Due Date]@row <= TODAY(3)), AND([Due Date]@row >= TODAY(), [% Completion]@row >= 0.5, [% Completion]@row <= 0.99)), “Yellow”

Then the Red Rules:

If due date is today Red...... 

if due date in past at whatever % it is red....

IF(AND([% Completion]@row <>1, [Due Date]@row <= TODAY()), “Red”

And finally your Blue rule (because your Yellow rule also talks about future dates):

If due date is 4 days in the future then blue......

IF([Due Date]@row >= TODAY(4), “Blue”

Put them all together:

=IF(OR([% Completion]@row = 1, AND([Start Date]@row = "", [Due Date]@row = "")), "", IF(OR(AND(ISDATE([Start Date]@row), [Due Date]@row = ""), AND([Due Date]@row >= TODAY(1), [Due Date]@row <= TODAY(3)), AND([Due Date]@row >= TODAY(), [% Completion]@row >= 0.5, [% Completion]@row <= 0.99)), "Yellow", IF(AND([% Completion]@row <> 1, [Due Date]@row <= TODAY()), "Red", IF([Due Date]@row >= TODAY(4), "Blue"))))

Keep in mind that in this instance, you have no rule for when the due date is in the future but the percent completed is below 50%. Those tasks, even if they are only 5 days away, will return BLUE since that's your last rule for future dates.

Here are some Help Center articles I used to create this:

• @row Function
• IF Function
• TODAY Function
• Formula Operators

Let me know if this works for you, or if you need to add more criteria!

Cheers,

Genevieve