Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Using RAGB plus adding Blank Cell action

I am trying to use the RAGB indicators to look at if a Due date is set and then setting the Indicator to Red if in the past, Yellow if in the next 3 days and Green if in the future beyond 3 days. Β We are also using the Blue to indicate if the task is complete. Β This works fine with the following formula, however for blank Due Dates it shows YellowΒ
Β
=IF([Task Completed]1 = "Yes", "Blue", IF(TODAY() - [Due Date]1 > 0, "Red", IF([Due Date]1 - TODAY() < 3, "Yellow", "Green")))
Β
What I would like to also do is look to see if the Due Date is not Set and Return a Value of Not Set if the cell is blank and keep the above indicators when they meet the criteria.
Β
Can anyone advise as how to do this?
Comments
-
Have you investigated the use of the ISBLANK formula?Β What I've used is if the cell value = 0 "Yellow" (or similar color code)Β
-
I have tried adding the ISBLANK to the formula however I cannot get it to work as it does not seem to like the 5th Element in the formula. Β The first 4 being the RAGB indicators which I need to show. Β I think it maybe just my lack of knowledge of the formulas
-
Chris,
Β
Try this:
Β
=IF([Task Completed]1 = "Yes", "Blue", IF(ISBLANK([Due Date]1), "Not Set", IF(TODAY() > [Due Date]1, "Red", IF([Due Date]1 - TODAY() < 3, "Yellow", "Green"))))
Β
Note that your use of "< 3" for yellow results in a yellow for 12/11, 12/12/, 12/13 but not not 12/14.
For some people, 12/14 is within 3 days.
(Not implying this is wrong, just a statement)
Β
Hope this helps.
Β
Craig
-
Craig
Β
That works perfectly, thank you so much for taking the time to look at this for us. Β Very much appreciated.
Β
Regards
Chris
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives