NEED HELP WITH FORMULA: Status Based on Target End Date, Actual End Date and "At Risk" flag

CrystalJ_Medtronic

So this is my current formula:

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(AND(ISDATE([Actual End Date]@row), [Actual End Date]@row <= [Target End Date]@row), "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late", IF(OR(AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), ([Target Start Date]@row > TODAY())), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Target End Date]@row < TODAY()), "Behind", IF(AND(ISBLANK([Actual End Date]@row), ([Target End Date]@row >= TODAY(8))), "On Track", "At Risk"))))))

This formula is working FANTASTIC, but a few of my team members have brought it to my attention that they need the flexibility to flag specific tasks as "At Risk". In other words they want to be able to override specific tasks that meet specific conditions.

The current parameters automatically updates the Status based on the dates in the Target End Date and Actual End Date columns as follows:

• If the Actual End Date is less than or equal to the Target End Date, Status = Completed On Time
• If the Actual End Date is greater than than the Target End Date, Status = Completed Late
• If there is no Target Start Date and no Actual End Date, Status = Not Started
• If the Target Start Date is greater than today's date, Status = Not Started
• If there is no Actual End Date, AND Target End Date is over 8 days away, Status = On Track
• If there is no Actual End Date, AND Target End Date is less than 8 days away, Status = At Risk
• If there is no Actual End Date, AND Target End Date is greater than today, Status = Behind

I need to add some additional parameters based on a new column called At Risk:

• If there is no Actual End Date, AND Target End Date is over 8 days away AND At Risk flag is checked, then Status is = At Risk
• If there is no Target Start Date and no Actual End Date AND At Risk flag is checked, then Status is = At Risk
• If the Target Start Date is greater than today's date AND At Risk flag is checked, then Status is = At Risk

I have no clue how to modify this formula to make this work.. I could use some smarter folks than me to help. 😂

Ryan Sides

Hi @CrystalJ_Medtronic

Use the words in your bottom bullets as hints to formulate the formula.

=IF([At Risk Flag Column]@row = 1, IF(OR(AND(bullet 1 criteria), AND(bullet 2 criteria), AND(bullet 3 criteria)), "At Risk", your original formula here )

Does that help?

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/353823#Comment_353823

No.. sorry.. I really am not good at these formulas.. Too much like coding, and when it gets complex like this, it's a LOT over my head.. but thanks for trying to help..

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/353823#Comment_353823

No it doesn't help.. sorry.. I hate to sound "speshul"😂 but I don't get it.. These formulas are not my strong suit. Too much like programming and that is totally not my jam. Thanks anyway for your attempt to help the clueless..😂

Ryan Sides

No worries! This is what community is for, to help each other out. 😃

Ok, give this one a try...

=IF([At Risk]@row = 1, IF(OR(AND([Actual End Date]@row > TODAY(8), [Target End Date]@row > TODAY(8)), AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), AND([Target End Date]@row > TODAY())), "At Risk", IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(AND(ISDATE([Actual End Date]@row), [Actual End Date]@row <= [Target End Date]@row), "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late", IF(OR(AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), ([Target Start Date]@row > TODAY())), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Target End Date]@row < TODAY()), "Behind", IF(AND(ISBLANK([Actual End Date]@row), ([Target End Date]@row >= TODAY(8))), "On Track", "At Risk"))))))))

CrystalJ_Medtronic

Hmmm well I

https://community.smartsheet.com/discussion/comment/353888#Comment_353888

You are a peach! So I tried this and got the following errors:

Ryan Sides

Can you post a screenshot of all your column headers please?

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/353955#Comment_353955

Okay.. So I got some sleep (was working on fumes! 😂) and carefully went through all of my column names and found the discrepancy! I modified the formula as follows:

=IF([At Risk]@row = 1, IF(OR(AND([Actual End Date]@row > TODAY(8), [Target End Date]@row > TODAY(8)), AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), AND([Target End Date]@row > TODAY())), "At Risk", IF(OR([Task Name]@row = "General Information", Site@row = "General Information"), "", IF(AND(ISDATE([Actual End Date]@row), [Actual End Date]@row <= [Target End Date]@row), "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late", IF(OR(AND(ISBLANK([Target End Date]@row), ISBLANK([Actual End Date]@row)), ([Start Date]@row > TODAY())), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Target End Date]@row < TODAY()), "Behind", IF(AND(ISBLANK([Actual End Date]@row), ([Target End Date]@row >= TODAY(8))), "On Track", "At Risk"))))))))

Now when I check the "At Risk" flag, the status shows "At Risk". However, if I uncheck the "At Risk" flag, there is no value in status. (see screenshots)

My expectation for my test tasks is that the status will show "On Track" or "Not Started" (based on the original formula) unless the "At Risk" flag is checked.

Basically the "At Risk" flag allows the PM to override the status for tasks where the calculated status is either "On Track" or "Not Started" and flag them as "At Risk". For any other calculated statuses (Completed on Time, Completed Late, At Risk, Behind) checking the "At Risk" flag will have no impact on the calculated status.