#### 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

Options
edited 12/09/19

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?

Tags:

• Options

Have you investigated the use of the ISBLANK formula?  What I've used is if the cell value = 0 "Yellow" (or similar color code)

• Options

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

• ✭✭✭✭✭✭
Options

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

• edited 12/12/16
Options

Craig

That works perfectly, thank you so much for taking the time to look at this for us.  Very much appreciated.

Regards

Chris

This discussion has been closed.