IF AND Statement for Color Statuses

RC-MTAS

I am trying to do something that I thought was fairly simple, but I keep running into an issue and receive an "#Unparceable" with my current formula.



I am trying to build a Task Status based on Red, Yellow, Blue, and Green Circles.  My challenge is getting the proper formula/syntax for my Yellow Status.  



 

=IF([Status]1 = ???, "Yellow", IF([Status]1 = "Complete", "Green", IF([Satus]1 = "In Progress", "Blue", IF([At Risk]1 = 1, "Red"))))

Status is simply the drop down column that comes in the project (Not Started, In Progress, and Complete).

At Risk is simply a check box column.

For the Yellow I would like for it to be IF Status = "Not Started" and the "Start Date" is > Today THEN Show Yellow.

 

Any ideas?

lmiddleton

Couple of quick tips:

1. It looks like there's a typo error in your formula above. i'm guessing it should read "Green", IF ([Status]1]...

2. as a general hint, try using @row instead of the row number in formulas. it increases the speed and can avoid some of the issues that come up when adding/changing rows. 

Hopefully that works for you!

Good luck!

RC-MTAS

I drag and drop the formula down so it works out, but I need to get the IF THEN portion for the Yellow to work properly.

Paul Newcome

Try this...

 

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Yellow", ..............

.

Also, the suggestion of the additional square bracket is incorrect. This will give you an Unpareseable error because the Formula will read the column name as "Status]1" and will not have a row reference.

.

Additionally... To save yourself a little headache, column names such as "Status" that do not have any spaces, special characters, or numbers in them, do not need the square brackets around them. Here's an example of a column called "Column Name] and using row 1 as the reference:

 

[Column Name]1

ColumnName1

 

Since there are no spaces in the second column name, it does not need to be wrapped in square brackets before adding the row reference.

Mike Wilday

@Paul there was, however, a typo IF([Satus]1. 

Paul is correct. You only need brackets if your column title ends in a number or has spaces. 

I also recommend using @row (see Paul's example) instead of referencing row numbers when you are referencing data on the same row as your formula. It will save a lot of headaches when copying/pasting etc. And you will guarantee your formula will work for the specific row you have it in. 

Paul Newcome

Mike,

 

You would think that I'd have caught that since I make that same exact typo ALL THE TIME... Lol.

RC-MTAS

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Blue", IF([At Risk]@row = 1, "Red"))))

Paul, YOU are the best!  That seemed to have got it working, but I realized there is a flaw in my reporting. All of my "Status" of "Not Started" show to be yellow even though the "Start Date" is in the future.  So, I was thinking I needed two additional statements.  One would need to be for "Blue" where the "Status" = "Not Started" but the "Start Date" is less than Today.  So, I tried this:

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Blue", IF(Status@row = "In Progress", "Blue",IF(Status@row = "Complete", "Green",IF([At Risk]@row = 1, "Red")))))

The formula works, but the results are not accurate.  Some rows show as Yellow and some rows show as Blue.  At this point, based on the formula, everything should show as Blue.  Is it simply because the IF statement order? Screenshot attached.

 

 

 

RC-MTAS

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Blue", IF([At Risk]@row = 1, "Red"))))

Paul, YOU are the best!  That seemed to have got it working, but I realized there is a flaw in my reporting. All of my "Status" of "Not Started" show to be yellow even though the "Start Date" is in the future.  So, I was thinking I needed two additional statements.  One would need to be for "Blue" where the "Status" = "Not Started" but the "Start Date" is less than Today.  So, I tried this:

=IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Blue", IF(Status@row = "In Progress", "Blue",IF(Status@row = "Complete", "Green",IF([At Risk]@row = 1, "Red")))))

The formula works, but the results are not accurate.  Some rows show as Yellow and some rows show as Blue.  At this point, based on the formula, everything should show as Blue.  Is it simply because the IF statement order? Screenshot attached.

Paul Newcome

Based on your formula above, everything should actually be yellow.

.

So let's break this down by color...

.

Red:       If At Risk is Flagged

Yellow:   If Status is "Not Started" and the Start Date is in the Future

Green:   If Status is "Complete"

Blue:      If Status is "In Progress" and Start Date is in the Past

or.....      If the Status is "Not Started" and Start Date is in the Past

.

Is this the correct criteria for each color?

.

Once we establish the criteria, we need to establish the hierarchy. What overrides what?

RC-MTAS

Thanks for the extra help.  Spelling it out helps. 

 

Red: If At Risk is Flagged

Yellow: If Status "Not Started" and the Start Date has passed (In the Past)

Blue: If Status is "Not Started" and the Start Date is in the future OR If Status is "In Progress"

Green: If Status is Complete

RC-MTAS

An extra level of excellence for RED would be

 

Red: If At Risk is Flagged OR If Status is not "Complete" and the "End Date" has passed

Paul Newcome

Ok. So here is what we've got thus far...

.

Red:     If At Risk is Flagged

or         If Status is not "Complete" and the End Date is in the past.

.

Yellow: If Status "Not Started" and the Start Date is in the past

.

Blue:    If Status is "Not Started" and the Start Date is in the future

or         If Status is "In Progress"

.

Green: If Status is Complete

.

The way nested IF formulas work is that they stop as soon as they hit a TRUE value. So now we need to establish which order we want them in.

.

I would suggest that the first one be Green. If the Status is "Complete" then it doesn't matter what dates are in the past or in the future.

=IF(Status@row = "Complete", "Green", 

.

If it moves past the Green criteria, then it must mean the Status is not "Complete". This means we can actual simplify the Red criteria some (every little bit helps).

.

Red:     If At Risk is Flagged

or         If the End Date is in the past.

.

Which I would imagine would be the next priority.

=IF(Status@row = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", 

.

Next I would think the Yellow would come into play.

=IF(Status@row = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), "Yellow", 

.

After that, the only other color would be Blue. You have two options for this. You can either specify your Blue criteria in an additional IF statement which would make it so that anything that does not specifically fall within any of the criteria remains blank:

=IF(Status@row = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), "Yellow", IF(OR(Status@row = "In Progress", AND(Status@row = "Not Started", [Start Date]@row > TODAY())), "Blue"))))

.

Or alternatively, you can just say that anything that doesn't fall within the specified criteria for the first 3 colors will automatically default to Blue.

=IF(Status@row = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), "Yellow", "Blue")))

.

One thing to keep in mind that you have not yet specified...

In your date criteria, you have stated if that particular date is either in the past or in the future. You don't have anything specified if the date should happen to be today.

RC-MTAS

That explanation actually helped me fix another column I am creating a Harvey Ball on.  Thank you for that.  I also appreciate the breakdown.

However, I am still getting an error with the formula(s) you provided at the end.  Initially it looked like it just need an additional closed parenthesis at the end, but then it gives "Unparceable".  I don'e see any spelling or wrong names.

Is there a syntax issue?

RC-MTAS

As for your last point about Today being the same as a Start/End Date.....

 

I am open to suggestions, but the only time I feel I would be at risk with that concern is if I am presenting a status update on the same day of one of the dates.  So, if I had to update a blank cell for the day I could do so.

Paul Newcome

I missed the closing parenthesis before "Yellow".

 

.................TODAY()), "Yellow", ....................

Paul Newcome

How you handle what happens if a date is today is entirely up to your preference.

 

The catch with a manual input: It will delete the formula out of the cell, so you will need to remember to go in and put the formula back to maintain the automation.

 

I personally use the end of today for my automations, but I also don't use durations.