#### 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
edited 12/09/19

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.

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

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

• ✭✭✭✭✭✭
Options

Jennifer,

Can you clarify your last BUT?

Craig

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

• ✭✭✭✭✭✭
Options

check your "due 1" cell reference.

Craig

• Options

Sorry, but how do you mean?

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

• ✭✭✭✭✭✭
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.

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.