RYGG balls based off of the date

Leslye Jackson

Hi there! I am working to automate the RYGG balls and its like I can get parts of the formula to work separately but not all together. I added a Past Due column to use to try to refer to that indicator to get the formula to be a little less complicated. The goal is:

If Due Date is blank =then no balls or ""

If the Due Date is less than the Actual Complete Date =Green

If the Past Due flag is true or 1=Red

If the Estimated Complete Date is within 5 days of today's date Yellow

Gray will be for N/A tasks

This one worked for me at one point but I have added some columns, so it is no longer working correctly

=IF(ISBLANK([Due Date]@row), "", IF([Past Due]19 = 1, "Red", IF([Actual Complete Date]@row > [Due Date]@row, "Green", "Yellow")))

Paul Newcome

Try something along the lines of...

=IF([N/A Column]@row = "N/A", "Gray", IF([Due Date]@row = "", "", IF([Actual Complete Date]@row = "", IF([Estimated Complete Date]@row >= TODAY(-5), "Yellow"), IF([Due Date]@row>= [Actual Complete Date]@row, "Red", "Green"))))

Stefan

Hi @Leslye Jackson,

for my understanding, when do you want your RYGG balls to switch from looking at due date to estimated completion date?

Where does the date in estimated completion date come from?

Does the formula for the past due flag checks if due date > today() ? If yes, you could use this in your progress formula to eliminate the past due column.

Greetings

Stefan

Paul Newcome

In addition to @Stefan's questions, how do we determine what an "N/A task" is (your gray criteria)? Is there a column that will be populated with "N/A", or is there some other indicator?

Leslye Jackson

@Stefan So we want it to look at the due date initially if it is not filled we do not want any balls to show so I tried to use the ISBLANK at the beginning for that request. The due date will be filled in by the trainer to let the center know this task is expected due on this date however we know deadlines are often pushed so we then want the formula to look at the actual completion date. The estimated completion date column is the date the trainer expects the task to be completed. This date will be filled in before the client is given access. This column and the due date column will be locked and only the trainer will have access. The Actual Complete Date is the date the task is finished. This will be entered in by the client

If Due Date is blank no balls

If the Due Date < Actual Complete Date =Green

If the Past Due flag is true or 1=Red

If the Estimated Complete Date is within 5 days of today's date=Yellow

Gray will be for N/A tasks

@Paul Newcome We probably will use an N/A column to designate which task are not applicable

Does the formula for the past due flag checks if due date > today() ? If yes, you could use this in your progress formula to eliminate the past due column.

Below is the flag for the formula column. I thought it would be easier to right the formula if I could refer to an indicator within it because it became really complex with all the IF statements. The slight issue with the flag formula is that even when the date columns are blank the flag is red but if the dates are not past due it goes off. It may be confusing to clients but maybe it can be hid....

=IF([Actual Complete Date]20 < [Due Date]20 = 0, 1)

I keep thinking using the Today function is correct but its confusing to me, I cant tell if it works differently with a number within the parenthesis or not. Thanks for the feedback guys!!

Paul Newcome

Try something along the lines of...

=IF([N/A Column]@row = "N/A", "Gray", IF([Due Date]@row = "", "", IF([Actual Complete Date]@row = "", IF([Estimated Complete Date]@row >= TODAY(-5), "Yellow"), IF([Due Date]@row>= [Actual Complete Date]@row, "Red", "Green"))))