RYGB

so_tx_anglerette

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?

 

Jgrinde27711

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

Paul Newcome

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.

so_tx_anglerette

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. 

I hope I understood your questions and answered them. 

Paul Newcome

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

so_tx_anglerette

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.  

Paul Newcome

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?

so_tx_anglerette

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

Paul Newcome

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

so_tx_anglerette

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

so_tx_anglerette

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

 

Paul Newcome

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

so_tx_anglerette

It worked! Thanks so much!