RYGB Status

Jennifer Dewhurst

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

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

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

Jennifer,

 

Can you clarify your last BUT?

 

Craig

Blessing Ugbo

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

check your "due 1" cell reference.

Craig

Blessing Ugbo

Sorry, but how do you mean?

 

Blessing Ugbo

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

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