17

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?

Comments

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!

Try this...

 

=IF(AND([email protected] = "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.

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

In reply to by Paul Newcome

=IF(AND([email protected] = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF([email protected] = "Complete", "Green", IF([email protected] = "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([email protected] = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(AND([email protected] = "Not Started", [Start Date]@row < TODAY()), "Blue", IF([email protected] = "In Progress", "Blue",IF([email protected] = "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.

 

 

 

=IF(AND([email protected] = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF([email protected] = "Complete", "Green", IF([email protected] = "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([email protected] = "Not Started", [Start Date]@row > TODAY()), "Yellow",IF(AND([email protected] = "Not Started", [Start Date]@row < TODAY()), "Blue", IF([email protected] = "In Progress", "Blue",IF([email protected] = "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.

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?

In reply to by Paul Newcome

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

In reply to by 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

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([email protected] = "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([email protected] = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", 

.

Next I would think the Yellow would come into play.

=IF([email protected] = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND([email protected] = "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([email protected] = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND([email protected] = "Not Started", [Start Date]@row < TODAY(), "Yellow", IF(OR([email protected] = "In Progress", AND([email protected] = "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([email protected] = "Complete", "Green", IF(OR([At Risk]@row = 1, [End Date]@row < TODAY()), "Red", IF(AND([email protected] = "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.

In reply to by Paul Newcome

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?

In reply to by 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.

In reply to by RC-MTAS

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.