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

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.

Smartsheet SS.PNG

Find more posts tagged with

Human Resources

Formulas

Comments

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

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…