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

Chris Winward
edited 12/09/19 in Archived 2016 Posts

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?



  • Mike Zeinstra

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

  • Chris Winward

    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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.



  • Chris Winward
    edited 12/12/16



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




This discussion has been closed.