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.

RYGB Status

Jennifer Dewhurst
Jennifer Dewhurst ✭✭✭
edited 12/09/19 in Archived 2017 Posts

I've read all of the other posts about this formula, including the Blog, but i can't seem to get this formula right.  Here's what I'm trying to do:


Due Date TODAY(-5) = Green

Due Date TODAY <4>0 = Yellow

Due Date TODAY+1 = Red

Date Completed = Blue


I can get the RYG balls but I can't figure out how to ADD the criteria for the Blue ball.  

RYG  =IF(TODAY() - Due2 > 0, "Red", IF(TODAY() - Due2 > -3, "Yellow", "Green"))


I can get the formula for JUST the blue ball:   =IF([Date Completed]1 >= TODAY(), "Blue") but I can't figure out how to incorporate this into the RYG formula. What operator should I be using? What am I doing wrong?  Any help is greatly appreciated. 


  • Jason
    Jason Employee

    Hi Jennifer


    Give this a try. 


    =IF(AND(NOT(ISBLANK([Date Completed]1)), [Date Completed]1 >= TODAY()), "Blue", IF(TODAY() - due1 > 0, "Red", IF(TODAY() - due1 > -3, "Yellow", "Green")))


  • HOLY SMOKES! It worked! Thanks Jason. you're a genius.


    BUT, it only seems to work int he current year. It doesn't work if the dates are between two years.  But I'm a lot closer.


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



    Can you clarify your last BUT?



  • Hello, I am not sure what I am doing wrong but I copied the same formula to my sheet and it gives me "UNPARSEABLE" error. 

    =IF(AND(NOT(ISBLANK([finish]22)),[finish]22>=TODAY()),"Blue", IF(TODAY() - due 1>0, "Red", IF(TODAY() - due1 > - 3, "Yellow", "Green")))

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

    check your "due 1" cell reference.


  • Sorry, but how do you mean?


  • I get it now..sorry. What if I only want to use the Start and End date as a trigger for the BRYG balls. How do I modify my formula? 

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

    Each cell reference should be ColumnRow. If the column has spaces or special characters, use [] around the column name

    [Start Date]23 

    for row 23, for example.

    See this article on functions. 


    You'll like want to use the following:


    and maybe







    should get you started.

    This formula from a previous answer above

    =IF(AND(NOT(ISBLANK([Date Completed]1)), [Date Completed]1 >= TODAY()), "Blue", IF(TODAY() - due1 > 0, "Red", IF(TODAY() - due1 > -3, "Yellow", "Green")))


    if the date completed is not blank and is more than today, then the color of the ball is blue

    otherwise if today is greater than the due date (ie due date is in the past), then the color is red

    if neither of those are true, then if the due date is less than 3 days in the future, then the color is yellow

    and if all those are not true, the color is green.

    Swap your cell references for the ones in the formula.


This discussion has been closed.