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

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…