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

BM

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!

 

 

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?

BM

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.

BM

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.