Welcome!

RYG Balls Automation by date, display nothing when date column is empty

Hello!

I have set up the automated RYG balls based on this tutorial: https://www.smartsheet.com/blog/support-tip-automate-RYG-balls?_ga=2.231470687.1137853115.1530638305-632979920.1530208332

Green for more than 3 days away
Yellow for 3 days or less away
Red for overdue

Right now if there is no date in the Due Date cell, the program shows a "Yellow" ball. I'd like to change this so that it displays nothing at all if there is no date in the Due Date cell.

Here is the current code:

=IF(Done14 <> 1, IF(TODAY() - [Due Date]14 > 0, "Red", IF(TODAY() - [Due Date]14 > -3, "Yellow", "Green")))

Many thanks!

Find more posts tagged with

Comments

Paul Newcome

=IF(ISBLANK([Due Date]14, "", IF(Done14 <> 1, IF(TODAY() - [Due Date]14 > 0, "Red", IF(TODAY() - [Due Date]14 > -3, "Yellow", "Green"))))

There you be. The "" will denote a blank. You can also use " " to get the same effect. All you needed was an additional IF statement to tell it what to do if the [Due Date] "ISBLANK".

Out of curiosity... What is the purpose of the BOLD portion in your current formula? Is your current formula working the way you need it to?

Thank you for the reply! I tried the bit that you suggested and I am getting an "Incorrect Argument" Error.

The code that I pasted above works for the red, and green conditions - but if there is nothing in the Due Date cell it shows yellow.

Please let me know if I can provide you with any added info. Thank you again.

Paul Newcome

I think it has to do with the bold portion I was asking about. It doesn't fit in with the correct formatting. Maybe try taking that part out. Another suggestion is using @row in place of the row number.

Try this...

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row > Today(3), "Green", IF(AND([Due Date]@row > Today(), [Due Date]@row < Today(3)), "Yellow", IF([Due Date]@row <= Today()< "Red"))))

What this says is:

If the due date is blank then blank. If the due date is more than 3 days away then green. If the due date is less than 3 days away but is not today then yellow. Finally, if the due date is today or in the past the red.

Thank you again. I've noticed that when I input formulas with Today, they automatically default to TODAY.

Your suggested code works perfectly for:

- no date condition

- yellow condition

- green condition

But when I put a date that has happened in the past in the Due Date column to simulate an overdue task, I get the incorrect argument error again. Any thoughts?

Paul Newcome

Defaulting to all caps is standard. No worries there. And the past due issue was totally MY fault. See below:

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row > Today(3), "Green", IF(AND([Due Date]@row > Today(), [Due Date]@row < Today(3)), "Yellow", IF([Due Date]@row <= Today(), "Red"))))

My fat fingers were holding down the shift key when I went to put the comma in between Today() and "Red". I do apologize for that. The above formula should work.

Lesson learned... Don't just type away for an answer. At least copy/paste the formula to an actual sheet to make sure it works before posting.

Sign in to join the conversation!

Quick Links

Trending discussions

Can I view sheet-level comments on a report?
I have a sheet that already has a "Latest Comment" column, and I can easily pull the row-level comments onto a report using this column. I take this report and use it as a dashboard widget so people can see the latest comments from the sheet all together. However, this sheet will also have sheet-level comments. Is there a…
Why is the sheet name not in the browser tab when in Table view?
The sheet name is in the tab in Grid view, but not Table view, making it difficult to locate a particular sheet when multiple tabs are used. Is this a design oversight?
Which template to choose?
My small civil engineering company is brand new to Smartsheet. I will be overseeing about 70+ projects spread out over 8 engineers in the company. I want to track the status of each project, and add milestones/tasks to each one along the way. Each project also has its own budget. I want to be able to track each project &…