RYG Status Formula

Florence Nickerson

We have two set of data that are required.

Count column that is 0 until there is entry into one or all of the previous columns.  This formula provides a count (this formula works fine)

=IF(Series5 = 0, IF(COUNTIF([SKU 1]5:[SKU 15]5, "") = 0, 1, 0), 1)

In the RYG column I have this Formula.   

=IF(Count5 = 0, "Green", IF(Count5 = 1, "Blue", IF(TODAY() - Due5 > -7, "Red", IF(TODAY() - Due5 > -14, "Yellow"))))

The first two work just fine but I need the RED and YELLOW Balls to reference the DUE date to provide a status if these dates are pending or missed. 

 

What am I missing?

Shaine Greenwood

Hi Florence,

I think you're wanting to change to Yellow if the current date is within 14 days of the Due date, change to Red if it's within 7 days of Due.

With this type of function, you'll also want to account for the fact that you're checking multiple disparate criteria. Nested IF formulas run from left to right and will stop running at the first instance of true in the formula and return the corresponding value.

Here's how I'd structure the formula to achieve that:

 =IF(AND(TODAY() - Due5 <= 15, Count5 = 0), "Green", IF(AND(TODAY() - Due5 <= 15,Count5 = 1), "Blue", IF(TODAY() - Due5 <= 7, "Red", IF(TODAY() - Due5 <= 14, "Yellow"))))

Let me know if that isn't returning the results that you expect.

Florence Nickerson

Hi Shaine,

Thanks for the help!

When I enter that formula I get a an Invalid Operation?

Florence

Florence Nickerson

Hi Shaine,

I have worked on this a bit more.  The formula you provided:

 

=IF(AND(TODAY() - Due5 <= 15, Count5 = 0), "Green", IF(AND(TODAY() - Due5 <= 15,Count5 = 1), "Blue", IF(TODAY() - Due5 <= 7, "Red", IF(TODAY() - Due5 <= 14, "Yellow"))))

• If the date has not past I get Green
• If the date has not past and I enter data and count turns to 1, I get Blue
• If The date is 15 days past, I get Green
• If the date is 17 days past, I get a blank
• If the date is 30 days past and I ender data and count turns to 1, I get a blank

In no instance do I get Red or Yellow

See attached.

Any help you can provide would be appreciated.

Florence Nickerson