Automate RYG ball with criteria criteria pivoting around due date.

Jon Stikka

trying to figure out automation formula for the status balls based on the following...

• Done check box ticked - BLUE
• Open Tasks - GREEN
• Caution - YELLOW (week before and week after the due date)
• Overdue - RED (more than week after the Due date)

It's the yellow I am struggling with. Do I need to create an additional column to count an absolute value +/- away from the due date?

 

 

Mike Wilday

No you can just add + or - to the due date. =IF([Due Date]@row - 7 > Today(), "Red") 

Or you can use networkdays =IF(Networkdays([Due Date]@row, Today()) <= 7, "Red" )

Jon Stikka

 I'm close but I'm not getting the desired result. Something is wrong and I haven't figured it out....  There is no date I can select that returns YELLOW. It's all Reds, until I set a date 7 days in the future. Then everything is green.

 

=IF(Done@row = 1, "Blue", IF([Due Date]@row - 7 < TODAY(), "Red", IF([Due Date]@row + 7 > TODAY(), "Green", "Yellow")))

Mike Wilday

Hi Jon, Try this adjustment which adjusts TODAY rather than your due-date. With this formula and today's date in the due-date column I got Yellow. 

=IF(Done@row = 1, "Blue", IF([Due Date]@row < TODAY(-7), "Red", IF([Due Date]@row > TODAY(7), "Green", "Yellow")))

Mike Wilday

Here is a screenshot without the blue portion - showing its working. 

Jon Stikka

That worked! Thanks for the help though this, working with the Today() seems like a much better approach. I didn't realize that you could insert your +/- days within those brackets.  Awesome. Thanks again!

Mike Wilday

Awesome, glad I could help you out!