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

Genevieve P.

Hi Twyla, I'm so glad to hear that! And I'm happy to help 😊

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

Twyla Booker-Kincaid

Genevieve, you are the most amazing. Thank you so very much. I have been trying to figure this out so we can move forward in sheets... You are great. Just to let you know looking over your code makes me understand how to do others. Great explanation. Thank you again.

Twyla😀

Genevieve P.

Hi Twyla, I'm so glad to hear that! And I'm happy to help 😊