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

Options
Becca Grunbaum
Becca Grunbaum ✭
edited 12/09/19 in Formulas and Functions

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 

Smartsheet.PNG

Tags:
· Share on FacebookShare on Twitter
«1

Comments

  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

     

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

    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")))

     

     

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    edited 08/18/19
    Options

    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. 

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

    =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")))))))))))))))

     

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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")))))))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

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

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

    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 

    · Share on FacebookShare on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help!

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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"))))))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Becca Grunbaum
    Becca Grunbaum ✭
    Options

    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. 

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!