RYGG balls based off of the date
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")))
Best Answer
-
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"))))
Answers
-
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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?
-
@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!!
-
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"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!