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

Tags:

Comments

  • Mike Zeinstra
    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) 

  • Chris Winward
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • Chris Winward
    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.