# RYGB

Options
edited 12/09/19

I am trying to develop a formula that will look at multiple columns to return a RYG status.

If "Sign work?" is box is checked

THEN

Look at "Sign projected ship date"  & "Sign actual ship date" compare to "Sign need date"

Here's where I'm not sure on the logic sequence:

If "Sign actual ship date" is null - Use projected to drive ball color

If the "Sign actual ship date" is later than the projected, use it to drive the color of the ball

If the "Sign actual ship date" is sooner than the projected, use it to drive the ball color

RYG symbol in column "Sign mfg status":

Red - Result is beyond or within 3 days of "Sign need" date

Yellow - Result date is within 7 days of "Sign need" date

Green - Result date is within 14+ days of "Sign need" date

If "Sign work?" box is not checked, then the result is null

Maybe I am over complicating?

Tags:

• edited 07/19/18
Options

I'm not sure if this will help you, but this is the formula that I use

=IF(ISBLANK([Target Date]1), "", IF([Check box1]1 = 1, "Gray", IF([Target Date]1 > TODAY() + 1, "Green", IF([Target Date]1 >= TODAY(), "Yellow", IF([Target Date]1 < TODAY(), "Red", "")))))

The color is case sensitive,

regards

• ✭✭✭✭✭✭
Options

This is actually a pretty straight forward nested IF statement. While it may be long, it won't have a lot of extras built in. For clarification though...

If the "Sign actual ship date" is later than the projected, use it to drive the color of the ball

If the "Sign actual ship date" is sooner than the projected, use it to drive the ball color

For each of these... what exactly do you want driving the formula?

If "Sign actual ship date" is equal to projected, what then?

Once we get this answered, we should be able to build you something relatively quickly.

• Options

If "Sign actual ship date" is null - Use projected ship date to determine the ball color

If the "Sign actual ship date" is later than the projected ship date, use it to drive the color of the ball

If the "Sign actual ship date" is sooner than the projected ship date, use it to drive the ball color

If "Sign actual ship date" is equal to "projected" then use "sign actual ship date" to drive color of the ball.

• ✭✭✭✭✭✭
Options

Give this a try. Tested it and it worked for me.

=IF(AND(ISDATE([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign actual ship date]@row < ([Sign need date]1 + 3), "Red", IF([Sign actual ship date]@row < ([Sign need date]1 + 7), "Yellow", "Green")), IF(AND(ISBLANK([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign projected ship date]@row < ([Sign need date]1 + 3), "Red", IF([Sign projected ship date]@row < ([Sign need date]1 + 7), "Yellow", "Green"))))

• Options

Hi Paul. It works however the colors are off.  In the snipet, Row 1 shows a sign need date of 9/11/18 and actual ship date of 9/27/18 which should display a red ball. Row 2 shows a sign need date of 9/19 and a projected ship date of 9/7 which should result in a green ball. Lastly, row 4 shows a need date of 8/30 and a projected ship date of  9/4; should be red too.

BTW . . .I was so excited that I didn't get an error when I used the formula.

• ✭✭✭✭✭✭
Options

Ok. So just to be clear... You want Yellow and Green to populate if projected or actual is BEFORE the need, and Red if it is 3 days before or later?

• Options

Hi Paul. Yeah; I guess the day criteria for the 3 colors is a little muddy. Will this work? I think this tightens it up.

Red - past due the need date

Yellow - equal to or within 3 days of the need date

Green - 4+ days prior to the need date

i.e. If need date is 10/5/18 then:

Ship date 10/6  or later - R

Ship date 10/3, 10/4, 10/5 - Y

Ship date 10/2 or sooner - G

• ✭✭✭✭✭✭
edited 07/31/18
Options

=IF(AND(ISDATE([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign actual ship date]@row > ([Sign need date]1), "Red", IF(OR([Sign actual ship date]@row > ([Sign need date]1 - 3), [Sign actual ship date]@row = ([Sign need date]1), "Yellow", "Green")), IF(AND(ISBLANK([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign projected ship date]@row > ([Sign need date]1), "Red", IF(OR([Sign projected ship date]@row < ([Sign need date]1 - 3), [Sign projected ship date]@row = ([Sign need date]1), "Yellow", "Green"))))

Give this a try and let me know how it works out for you. I haven't had a chance to actually test it, but it should work.

I also noticed that you don't have a designation for Blue...

• Options

Hi Paul. I'll give it a try and let you know. Thanks.

• Options

Hi Paul. I used the formula in several scenarios and my ball disappears in each scenario. I'm not sure why.

• ✭✭✭✭✭✭
Options

My apologies. That's what I get for not testing it. I did test this one with successful results.

=IF(AND(ISDATE([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign actual ship date]@row > [Sign need date]@row, "Red", IF(AND([Sign actual ship date]@row <= [Sign need date]@row, [Sign actual ship date]@row > [Sign need date]@row - 3), "Yellow", "Green")), IF(AND(ISBLANK([Sign actual ship date]@row), [Sign work?]@row = 1), IF([Sign projected ship date]@row > [Sign need date]@row, "Red", IF(AND([Sign projected ship date]@row <= [Sign need date]@row, [Sign projected ship date]@row > [Sign need date]@row - 3), "Yellow", "Green"))))

• Options

It worked! Thanks so much!