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

Options
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. 

Comments

  • Jason
    Jason Employee
    Options

    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")))

     

  • Jennifer Dewhurst
    Options

    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 ✭✭✭✭✭✭
    Options

    Jennifer,

     

    Can you clarify your last BUT?

     

    Craig

  • Blessing Ugbo
    Options

    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 ✭✭✭✭✭✭
    Options

    check your "due 1" cell reference.

    Craig

  • Blessing Ugbo
    Options

    Sorry, but how do you mean?

     

  • Blessing Ugbo
    Options

    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 ✭✭✭✭✭✭
    Options

    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. 

    https://help.smartsheet.com/functions

    You'll like want to use the following:

    IF

    and maybe

    AND

    OR

    NOT

    ISBLANK

    but 

    IF 

    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")))

    says 

    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.

    Craig

This discussion has been closed.