RYG Status Balls Formula Help!!

Alice

I am looking for help developing a formula for the following RYG Status Balls Conditions:

1. If status is "not started", grey
2. If it is 1 day before "due date" and status is NOT "complete", yellow
3. If it is the day of the "due date" or past "due date", red
4. If status is "complete", or "in progress" and it is 2 days or more away from "due date", green

Can someone help me?! I am pretty novice with formulas and am having issues editing some of the ones I found on the forums to reflect my specific conditions. Any help would be amazing. 🙏

Andrée Starå

Hi @Alice

I hope you're well and safe!

Try something like this. I think you've left some scenarios out, but not knowing your exact process, I can't be sure.

=IF(Status@row = "Not Started"; "Gray"; IF(AND([Due Date]@row = TODAY(1); Status@row <> "Complete"); "Yellow"; IF(AND(OR(Status@row = "Complete"; Status@row = "In Progress"); [Due Date]@row >= TODAY(2)); "Green"; IF([Due Date]@row <= TODAY(); "Red"))))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alice

Hello,

Thank you; however, it's not quite working for the following reasons:

1. My completed tasks are now turning red rather than green.
2. All tasks that are not started, regardless of when they were due (even if they are overdue), are turning grey.

I'm trying to use the colored status to warn/inform my team about the following:

1. which due dates are quickly approaching (less than 2 days to complete) for tasks "in progress" or "not started" (by turning yellow)
2. which ones are "completed" OR still "in progress" but you have plenty of time (2 or more days) to work on it (by turning green)
3. which tasks are NOT "complete" and the due date is "today" or "in the past" (by turning red)
4. Which tasks have not been started and you have more than 2 days to work on it (by turning grey)

I hope the scenarios above make much more sense!

Again, trying to use the color coding to show team which tasks are coming up quickly, which ones they have time work on, and which one urgently need doing based on due dates and completion status. I hope that makes sense! By the way, I did have to replace with the semi-colon with a comma, thanks for that headsup.

Thanks so much for the help!