Status for At Risk tasks due in 7 days

brenttopa
brenttopa
edited 12/09/19 in Formulas and Functions

I have 4 status categories for my project with respective RYGB balls:

  • Complete (blue)
  • At Risk (red)
  • Requires Attention (yellow)
  • On Track (green)

 

My current formula in the Health column automatically changes the RYGB balls when the status is changed manually:

=IF(Status2 = "On Track", "Green", IF(Status2 = "Complete", "Blue", IF(Status2 = "Requires Attention", "Yellow", IF(Status2 = "At Risk", "Red"))))

 

However, I would like to incorporate some automation in the Status column:

1) Status becomes "At Risk" when % Complete is less than 100% and Finish Date is within 7 days

2) Status becomes "On Track" with any other % Complete status less than 100% as long as Finish Date is outside 7 days

 

The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation.

 

I have created this formula based on my research but it is coming back as unparseable:

=IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))

 

Any help would be greatly appreciated!

 

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    =IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", "On Track", IF([Finish]1 >= TODAY(7), AND ([% Complete]1 < "1", "At Risk", ["On Track"]))))

    The bold and underlined part of your formula is causing that issue. 

    Try this modification: 

    =IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 >= Today(7), [% Complete]1 < 1), "At Risk", "On Track")))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    An Explanation:

    1. The "On Track" that appears after the "", is creating a non argument. I removed it because you added to the end of the third if statement. 
    2. And statements need to be comma-separated right after the IF like this. =IF(AND(This, That), "THEN DO THIS"

    That should do the trick for you. 

  • Thanks so much Mike!  Your formula worked correctly once I changed ">= Today(7)" to "<= Today(7)" to find tasks due in the next 7 days.  My mistake for not being clear on the requirement.  Appreciate your help,

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. I am so glad I could help you out. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Just a heads up...

     

    "The "Requires Attention" status is manually set by workstream leaders as discussion points for weekly meetings, so it won't require automation."

     

    Once the status is manually changed, it essentially deletes the formula from the cell. Any updates made after that would have to be done manually unless the formula is re-entered back into the cell after the manual change.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Yes, Paul is accurate in what he is saying. It is my recommendation to automate EVERY status in the status dropdown and to lock the column because any manual changes will overwrite formulas in the cell for good. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You might consider adding a checkbox column for "Requires Attention" and then automating the status based on the checkbox.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thanks, Mike. I guess a recommended solution would be a good idea when pointing out an issue instead of just saying "this is a problem". Haha. That's one of my "pet peeves" and I just did it. Oops!

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    laugh @Paul - no worries. I'm glad you caught that. That was an important breakdown in the process. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    yesyes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Mike,

     

    That's a great point.  I didn't realize that the formula gets deleted when the status is manually changed.  I've set up a new checkbox column titled "Requires Attention" and added to my formula in the "Status" column (underlined section below is the added portion):

     

    =IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", "On Track", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))

     

    However, I'm getting an INCOMPLETE ARGUMENT SET error.  Checking the box under "Requires Attention" does nothing.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a whirl and see how it does for you...

     

    =IF([Requires Attention]@row= 1, "Requires Attention", IF([% Complete]@row= 1, "Complete", IF([% Complete]@row= "", "", IF(AND(Finish@row <= TODAY(7), [% Complete]@row< 1), "At Risk", "On Track"))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try this one... There was an extra "on Track" after "At Risk". 

    =IF([% Complete]1 = "1", "Complete", IF([% Complete]1 = "", "", IF(AND(Finish1 <= TODAY(7), [% Complete]1 < 1), "At Risk", IF([Requires Attention]1 = 1, "Requires Attention", "On Track"))))

  • That did it!  Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excellent! Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!