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!
Comments
-
=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.
-
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?
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives