Welcome!

Automate RYG ball with criteria criteria pivoting around due date.

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?

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Auto-populate an email into a form to reduce error
Is it possible to auto-populate an email domain in a field where people are asked to enter their email address? For example, I would like users to only have to type their name, without needing to enter anything after the “@” symbol, to reduce the chance of personal email addresses being entered.
Suddenly View Only on all Workspaces and Sheets I own
This discussion was created from comments split from: I am an editor on a smartsheet but can't edit the sheet.
Sharing with Editors outside our company
Good afternoon! My company works closely with another ambulance service, Company B. Both companies have a Smartsheet Enterprise plan. I have created a workspace that I need members of Company B to edit. Do I need to make them members on my account if they are already members on their account? Or do I just need to add their…