Automate RYG balls using two colums with IF/OR & IF/AND

HROptions

I have successfully automated the RYG balls to show a different color as we get closer to a due date, and then show "OVERDUE" when the due date has passed (Urgency). (Days1 is a hidden column that counts the difference between today's date and the due date)  Here is the successful formula:

=IF(Days1 >= 7, "Green", IF(AND(Days1 > 3, Days1 < 7), "Yellow", IF(AND(Days1 > 0, Days1 < 4), "Red", "OVERDUE")))

However, I need it to display nothing ("") if a 2nd column named STATUS also shows "Completed". In reading other threads, I need an IF/OR command with a IF/AND.  If I put the IF/OR beginning, I can't seem to get it to work.  Here is what I CAN'T get to work:

=IF(OR(STATUS1 = “Completed”, ""), IF(AND(Days1 >= 7), "Green", IF(AND(Days1 > 3, Days1 < 7), "Yellow", IF(AND(Days1 > 0, Days1 < 4), "Red", "OVERDUE"))))

The thread I used as my guide made sense to me, and here was a the example I based it off:

=IF(OR($[% Complete]2 = 1,$[End Date]2 > TODAY()), "Green", 



IF(AND($[% Complete]2 < 1,$[End Date]2 <= TODAY()), "Red", "Yellow"))

Forgive me if this is an easy fix and I am just too daft to figure it out.  Thanks for the help.

Paul Newcome

Try this one... (It updates appropriately in drag-fill too)

 

=IF(Status13 = "Completed", " ", IF(Days13 >= 7, "Green", IF(Status13 = "Completed", " ", IF(AND(Days13 > 3, Days13 < 7), "Yellow", IF(AND(Days13 > 0, Days13 < 4), "Red", "OVERDUE")))))

 

Just needed to throw    IF(Status1 = "Completed", " ",    in there with an extra ) at the end to close it out. Make sure it is first in your order though so it checks you Status column before even considering a RYG ball... You don't even need the OR function. 

 

Oh! And no worries about not being able to figure it out. You should see some of the super simple questions I've asked because I was over thinking it, or just couldn't think that particular day. Lol

HROptions

Paul - Thank you! It worked!  So I better understand, and so I can keep learning, why is the =IF(Status13 = "Completed", " ", included 2 times in the formula?  Is it to catch a COMPLETED status outside of any date range, in case there is no request and due date?

Again, than you so much.

Paul Newcome

That is my fault. Sorry about that. I had initially put it there when testing it, then realized it needed to be in the first position and just forgot to delete it out of there. The second one is completely useless. Here's the updated formula:

 

=IF(Status1 = "Completed", " ", IF(Days1 >= 7, "Green", IF(AND(Days1 > 3, Days1 < 7), "Yellow", IF(AND(Days1 > 0, Days1 < 4), "Red", "OVERDUE"))))

 

It works exactly the same, and it is a little shorter. Obviously it's a different row reference in this one, but the meat and potatoes of it is still the same (minus the excess). Simply Drag-fill and have fun. 

 

As it is, if the Days column is blank it will show as "OVERDUE". If you would like the urgency column to show as blank if the Days column is blank, you can use the following:

 

=IF(ISBLANK(Days14), " ", IF(Status14 = "Completed", " ", IF(Days14 >= 7, "Green", IF(AND(Days14 > 3, Days14 < 7), "Yellow", IF(AND(Days14 > 0, Days14 < 4), "Red", "OVERDUE")))))

 

If you REALLY want to have fun, the following formula does all of the above AND lets you know that the date is missing if there is a task name, but the Days column is blank (see picture below):

 

=IF(Status18 = "Completed", " ", IF(AND(ISTEXT([Task Name]18), ISBLANK(Days18)), "Date Missing", IF(ISBLANK(Days18), " ", IF(Days18 >= 7, "Green", IF(AND(Days18 > 3, Days18 < 7), "Yellow", IF(AND(Days18 > 0, Days18 < 4), "Red", "OVERDUE"))))))

 

Then you can use conditional formatting to make OVERDUE and DATE MISSING really pop out at you...