How to link the health column to show an accurate status of at risk tasks.

Becca Grunbaum

Hi All, 

I am looking to find a way that I can link (create a formula) so the health column shows a traffic light system for the status of the task. 

I want to aim for this to detect the percentage of completion of a task in relation to the finish date. For example, if the finish date is only a couple of days out and the task has not been started yet, the health would show as red. Alternatively if the task is showing as 100% complete and the finish date has not yet occurred the health would show as green. 

Does anyone have any knowledge of how to write a formula for this as i am at a complete loss. 

Help! 

Thanks 

Becca Grunbaum

This is the current formula I am working off but am stuck as to how to link it up to dates etc: 

 

IF([Status]2 = "Completed", "Green", IF(AND([Status]2= "In Progress", [Start Date]2 < TODAY(), [Finish Date]2 > TODAY()), "Yellow", IF(OR(AND([Status]2 = "Not Started"), AND([Start Date]2 < TODAY(), OR(AND([Status]2 = "In Progress", [Finish Date]2 < TODAY())))), "Red")))

The above is the formula I am using but keep getting "Unparesable". 

Paul Newcome

Can you list out details of each of the different criteria and their results in order of priority from most important to least?

 

You say "if the finish date is only a couple of days out"... Are we talking 2 days, 3 days, some other count?

 

"the task has not been started yet"... How do we know whether or not it has been started?

 

"task is showing as 100% complete and the finish date has not yet occurred the health would show as green"... Would you want this to be the top priority to show green at 100% REGARDLESS of the finish date?

.

Once you can lay it all out in detail, we can start working on a formula for you.

 

Becca Grunbaum

Hi Paul, 

The criteria can be dependent on % complete or Status, as these effectively do the same thing in the sheet. 

These are the current formulas I have been playing with but both show as Unparseable: 

=IF([% Complete]3 = 0, IF([Finish]3 > TODAY, "Red", IF([% Complete]3 <0.25, IF([Finish]3 > TODAY, "Red", IF([% Complete]3 <0.5, IF([Finish]3 > TODAY, "Red",IF([% Complete]3 <0.75, IF([Finish]3 > TODAY, "Yellow", IF([% Complete]3 <1, IF([Finish]3 > TODAY, "Green"))))))))))

=IF(Status2 = "Completed", "Green", IF(AND(Status2 = "In Progress", [Start Date]2 < TODAY(), [Finish Date]2 > TODAY()), "Yellow", IF(OR(AND(Status2 = "Not Started"), AND([Start Date]2 < TODAY(), OR(AND(Status2 = "In Progress", [Finish Date]2 < TODAY())))), "Red")))

 

 

Paul Newcome

Are you able to write out your criteria such as below?

.

"

If the Status is "Complete": Green

If the Status is not "Complete" and the Finish Date is in the past: Red

If the Status is not "Complete" and the Finish Date is in the next 3 days: Yellow

.....

.....

.....

.....

.....

"

.

Obviously the above is only an example, but that's the general idea. Use the actual column names and the actual text for statuses, colors, etc.

 

List them out in priority such that the most important is first. In the example above, it doesn't matter when the Finish Date is. If the Status is "Complete" then make it Green. So on and so forth.

 

Once you have your thoughts organized, it becomes MUCH easier to turn them into a working formula.

 

Your second one looks to be your closest attempt thus far, but there are still syntax issues that make it hard to understand EXACTLY what you are trying to accomplish.

Becca Grunbaum

Unfortunately its a little more complicated than just using the "Status" column, i used it initially, but what i actually need out of it is more detailed then that. That's why i started playing with the "% complete" column as this would provide a more accurate picture in the health of a task. 

"

If the % Complete is equal to "1": Green 

If the % Complete is less than "1" and the Finish Date is in the past: Red

If the % Complete is less than "0.25" and the Finish Date is Today: Red

If the % Complete is less than "0.75" and the Finish Date is Today: Yellow

If the % Complete is greater than "0.75" and the Finish Date is 14 days away: Green

If the % Complete is less than "0.50" and the Finish Date is 14 days away: Yellow 

If the % Complete is less than "0.25" and the Finish Date is 14 days away: Red 

If the % Complete is equal to "0" and the Start Date is in the future: Green 

"

Not sure if any of the above is of any help at all. 

Becca Grunbaum

=IF([% Complete]3 = 1, "Green",IF([% Complete]3, <1, IF([Finish]3 <TODAY, "Red", IF([% Complete]3,<0.25, IF([Finish]3 =TODAY, "Red",IF([% Complete]3, <0.75, IF([Finish]3 =TODAY, "Yellow",

IF([% Complete]3, >0.75, IF([Finish]3 >14, "Green", IF([% Complete]3, <0.50, IF([Finish]3>14, "Yellow", 

IF([% Complete]3, <0.25, IF([Finish]3>14, "Red", IF([% Complete]3 = 0, IF([Start]3 >TODAY, "Green")))))))))))))))

 

Paul Newcome

I am not sure if your above posted formula is what you got to work or not, so I will go ahead and throw out my suggestion based on your written out listing (which is VERY helpful).

We can start by writing out individual IF's for each of your criteria. Here is your above listing but numbered. We may be reorganizing things a little bit, so using numbered references with make that a little easier to read.

.

1. If the % Complete is equal to "1": Green 

=IF([% Complete]@row = 1, "Green"

2. If the % Complete is less than "1" and the Finish Date is in the past: Red

=IF(AND([% Complete]@row < .1, [Finish Date]@row < TODAY()), "Red")

3. If the % Complete is less than "0.25" and the Finish Date is Today: Red

=IF(AND([% Complete]@row < .25, [Finish Date]@row = TODAY()), "Red")

4. If the % Complete is less than "0.75" and the Finish Date is Today: Yellow

=IF(AND([% Complete]@row < .75, [Finish Date]@row = TODAY()), "Yellow")

5. If the % Complete is greater than "0.75" and the Finish Date is 14 days away: Green

=IF(AND([% Complete]@row > .75, [Finish Date]@row = TODAY(14)), "Green")

6. If the % Complete is less than "0.50" and the Finish Date is 14 days away: Yellow 

=IF(AND([% Complete]@row < .5, [Finish Date]@row = TODAY(14)), "Yellow")

7. If the % Complete is less than "0.25" and the Finish Date is 14 days away: Red 

=IF(AND([% Complete]@row < .25, [Finish Date]@row = TODAY(14)), "Red")

8. If the % Complete is equal to "0" and the Start Date is in the future: Green

=IF(AND([% Complete]@row = 0, [Start Date]@row > TODAY()), "Green")

.

Now we can work on some grouping for simplification.

Let's start with 5, 6, and 7. They all have the common criteria of the Finish Date being 14 days away.

So now we have :

1.

2.

3.

4.

5. 6. 7.

=IF([Finish Date]@row = TODAY(), IF([% Complete]@row > .75, "Green", IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .5, "Yellow"))))

8.

.

We can pull 3 and 4 together based on the Finish Date being today:

1.

2.

3. 4.

=IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")))

5. 6. 7.

8.

.

By placing 1 first in the order, we can simplify 2 to just say

"If the Finish Date is in the past: Red"

=IF([Finish Date]@row < TODAY(), "Red")

.

I am also going to assume that regardless of the % Complete, if the Start Date is in the future, we can make it Green (number 8).

=IF([Start Date]@row > TODAY(), "Green")

And we can go ahead and combine this with 1 using an OR statement, so now we have:

1. 8.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green")

2.

3. 4.

5. 6. 7.

.

So now let's rewrite your list in the new order ("else" wil be used as our place holder for the available 3rd portion of the IF statements):

 

1. 8.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", "else")

2.

=IF([Finish Date]@row < TODAY(), "Red", "else")

3. 4.

=IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), "else")

5. 6. 7.

=IF([Finish Date]@row = TODAY(), IF([% Complete]@row > .75, "Green", IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .5, "Yellow"))), "else")

.

Now that we have written out each individual statement and organized/simplified them, we can start nesting them together into a single formula paying attention to our "else" placeholders.

.

We will start with dropping 2. into the "else" portion of our 1. 8. statement.

1. 8. 

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", "else")

1. 8. + 2.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", 2)

1. 8. 2.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", "else"))

.

Now we drop 3. 4. into our open "else" portion.

1.8.2.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", "else"))

1.8.2. + 3.4.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", "3.4."))

1.8.2.3.4.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), "else")))

.

And finally 5.6.7. gets dropped in.

1.8.2.3.4.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), "else")))

1.8.2.3.4. + 5.6.7.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), "5.6.7.")))

1.8.2.3.4.5.6.7.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), IF([Finish Date]@row = TODAY(), IF([% Complete]@row > .75, "Green", IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .5, "Yellow"))), "else"))))

.

Since we have covered all of our criteria listed out, we can remove that final "else", and our formula is built.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), IF([Finish Date]@row = TODAY(), IF([% Complete]@row > .75, "Green", IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .5, "Yellow")))))))

Becca Grunbaum

Thanks Paul, I really appreciate ALL your assistance on this. Sadly the formula is still coming up as Unparseable.  

The formula I posted was just an example I was working on buy breaking down all the IF statements, but alas this did not work either. 

I'm at a complete loss.

Paul Newcome

Hmm... Try removing one of the parenthesis from the end of mine and see if that clears up the error.

Becca Grunbaum

I have tried breaking the formula down into sections and removed the parenthesis but still is not working. 

Andrée Starå

Hi Becca,

Can you share the formulas you're using and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Becca Grunbaum

Hi Andrée, 

I have created a draft plan which has is just a reduced version of my original plan for you to take a look at. 

I have been playing around with numerous formulas, the one i have in there currently works but doesn't provide an accurate view of the health of a task as doesn't take into consideration the dates. 

These are the fields I would need to look at with the specific outcomes: 

"

If the % Complete is equal to "1": Green 

If the % Complete is less than "1" and the Finish Date is in the past: Red

If the % Complete is less than "0.25" and the Finish Date is Today: Red

If the % Complete is less than "0.75" and the Finish Date is Today: Yellow

If the % Complete is greater than "0.75" and the Finish Date is 14 days away: Green

If the % Complete is less than "0.50" and the Finish Date is 14 days away: Yellow 

If the % Complete is less than "0.25" and the Finish Date is 14 days away: Red 

If the % Complete is equal to "0" and the Start Date is in the future: Green 

"

This is the most recent formula with the help of Paul i have been trying to get to work but still comes up at "Unparseable": 

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .75, "Yellow")), IF([Finish Date]@row = TODAY(), IF([% Complete]@row > .75, "Green", IF([% Complete]@row < .25, "Red", IF([% Complete]@row < .5, "Yellow")))))))

 

Thanks 

Andrée Starå

Happy to help!

I'll take a look and get back to you!

Paul Newcome

I do see part of my mistake in that when I started combining IF statements, I left out the TODAY(14) criteria. Here is another solution (that hopefully works this time) based on the criteria you have listed out. This does not throw any errors and follows your listed criteria.

 

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([Finish Date]@row = TODAY(14), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green"))))))

Becca Grunbaum

Hi Paul, 

I added the "14" when I was playing with the formula previously and still comes up "unparseable". I have also tried your formula above as well and unfortunately still doesnt work for some reason.