RYGB automation with date and status

KGravey

Okay first off I did not write this syntax I copied and pasted from another post. It works to a point but I need it to be a little more detailed and I cannot figure it out. Here is the syntax:

=IF(Status1 = "Complete", "Blue", IF([Start Date]1 = TODAY(), "Green", IF(AND([Start Date]1 < TODAY(), Status1 = "Warning"), "Yellow", IF(AND([End Date]1 > TODAY(), OR(Status1 = "In Process")), "Green", IF(AND([End Date]1 < TODAY(), NOT(Status1 = "Critical")), "Red", IF(AND(TODAY() <= [End Date]1, TODAY() >= [Start Date]1), "Green", "Red"))))))

1. It doesn't seem like any start date info works. I change the start date and no ball or status changes.

2. I'd like to put a 3 to 5 day "cushion" before the ball and status would change from Green to Yellow and Yellow to Red.

Here is a screen shot of what I'm working with. This is just a blank sheet that I'm trying to work this out on:

Thank you very much in advance!!

 

Shaine Greenwood

Hello,

First off, I'd highly recommend that you review our help center documentation on creating formulas, this might give you a better understanding of how to troubleshoot formula logic. I copy and paste, and use other people's formulas all the time, but copying and pasting can only get you so far.

Secondly, I'm not sure exactly what you're looking for, and formulas must be specific (whatever cushion you're looking for must be 3 or 5 days, not 3 to 5), but regardless, I'm seeing a few issues with your formula:

1. Your section with IF(AND([End Date]1 > TODAY(), OR(Status1 = "In Process")), "Green" doesn't quite make sense, you'll likely just want to use AND or OR, not both. OR if you need only one condition to be met to turn the status green, AND if both conditions must be met. Don't use both. OR also needs two conditions between the parens, you only have one.
2. Looks like you have a few criteria for Green, I'd consider bundling those all into one OR function, e.g. =IF(OR([Start Date]1 = TODAY(), AND([End Date]1 > TODAY(), Status1 = "In Process"), AND(TODAY() <= [End Date]1, TODAY() >= [Start Date]1)), "Green"
3. Looks like you want to show Red when Status is NOT "Critical," was that intended? I'd think that you'd want to show Red when Status is "Critical," which you can accomplish by removing the NOT function and parens.

If the above doesn't help, can you possibly type out the exact conditions you're trying to use to trigger specific colors of the RYGB Harvey balls? I or another community member may be able to work out the formula you need that way.

KGravey

Thanks for the reply SHAINE!

I've watched the video and have read a ton of posts about this. What I copied I thought was pretty close to what I was looking for but messing with this for a couple of days has made me realize what I need and what I don't. Here you go:

I need the Status Column to change the colors of the balls from Blue=Not Started, Green=In Process and Blue=Complete. I have figured this one out.

Second I'd like:

If the Start Date is 3 days past- Change to a "Yellow" ball and change the Status to "Warning"

If the Start Date is 5 days past- Change to a "Red" ball and change the Status to "Critical"

If it is 3 days prior to End Date - Change to a "Yellow" ball and change the Status to "Warning"

If it is the End Date or after - Change to a "Red" ball and change the Status to "Warning"

Then when the task is complete I change the Status to "Blue" for it to be "Complete"

Is this something that can be done? I've been banging my head for a couple of days now and just can't figure it out. Thanks for any help possible!!!

CanadaJim

You seem to have some multi-layer and possibly circular logic going on.  First, you have logic to set status.  The status sets the RYBG to a certain color.

However, you also want relative dates to potentially change the color.

One key problem is that you want to automatically determine status if "Warning" or "Critical", yet also set automatically(?) to "In Process" or manually to "Complete" which will delete the formula.

What I would do in this case is set the "Status" formula to automatically set to "Complete" if the "% Complete" column (which you haven't created yet) is 100%.  Then you can include your other conditions to set the status to "Warning" or "Critical", and explicitly set the RYGB to the correct color based on the Status.

Start by dealing with "Not Started" and "Complete". Then check for Red conditions (most restrictive), then Yellow conditions, then everything else is Green.

So in loose logic for Status:

=IF(Today < Start Date,"Not Started", IF(Complete=100%, "Complete", IF(OR(End Date or later, 5 days after Start Date), "Critical", IF(OR(3 days before End Date, 3 days after Start Date), "Warning", "In Process"))))

Finally, just set your IF statements in the RYGB to look for the appropriate Status(es) for the colour.

Keep in mind, embedding your IF statements and OR statements requires the exact right amount of brackets in the right places.  If there's an extra or missing one it will make your formula "Unparseable".

If you need help with the specific IF statements here, let us know.

Jim

Shaine Greenwood

This can't be handled with a single formula, as you're wanting values in multiple cells to update based on your criteria. What you'll need to do is figure out what you want to drive the change. 

For example, if you want to manually set the Status and have the RYGB be automatic, you'll need to set a nested if formula in the RYGB column:

=IF(Status1 = "Warning", "Yellow", IF(...etc...etc...))

If you want both Status and RYGB be completely automated based off of your Start Date or End Date criteria, you'll need two formulas, one in every cell of your Status column and another in the RYGB column.

RYGB column formula:

=IF([Start Date] >= TODAY(3), "Yellow", IF(...etc...etc...))

Status formula:

=IF([Start Date] >= TODAY(3), "Warning", IF(...etc...etc.))

My examples above aren't complete formulas, but I hope they help guide you in the right direction. You'll need to add the additional conditions for your RYGB and Status columns.