RYG Status Formula

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?

Find more posts tagged with

Sheets

Formulas

Support

Community Platform

Project Management

Comments

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

CAS RYG Formual.png

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.