Adding formula to existing formula for status change
Hi everyone,
I currently have a formula in my status column that autoupdate depends on the percentage completion. Anything that is 0 is Not Started, and greater than 0% and less than 100% is Green (G), and anything that is 100% is Complete (blue).
I want to add 2 more actions in the formula.
 If Finish Date is in the past, Status icon will become Yellow (Y)
 If At Risk flag is checked, Status icon will become Red (R)
Which formula do you suggest I can add to my current formula? Thanks.
Best Answer

Assuming this hierarchy:
*Percent Complete = 100% Blue
*Percent Complete >0%
AND... Finish Date is <= Today Yellow
*Percent Complete >0
AND... At Risk Flag is Checked.... Red
*Percent Complete >0%
AND... Finish Date is > Today Green
Otherwise, Not Started
=IF(ISBLANK([Task Name]@row), "", IF([% Complete]@row = 1, "B", IF(AND([% Complete]@row > 0, [Finish Date]@row < TODAY()), "Y", IF(AND([% Complete]@row > 0, [Finish Date]@row >= TODAY(), [At Risk]@row = 0), "G", IF(AND([% Complete]@row > 0, [At Risk]@row = 1), "R", "Not Started")))))
Looking at data though, I think we'd want to boost the priority of the Risk Flag  because I'm guessing if that's checked, regardless of anything else (other than 100% complete), it should show red, right? If so, you'd switch the order a little bit:
=IF(ISBLANK([Task Name]@row), "", IF([% Complete]@row = 1, "B", IF([At Risk]@row = 1, "R", IF(AND([% Complete]@row > 0, [Finish Date]@row < TODAY()), "Y", IF(AND([% Complete]@row > 0, [Finish Date]@row >= TODAY()), "G", "Not Started")))))
Answers

Assuming this hierarchy:
*Percent Complete = 100% Blue
*Percent Complete >0%
AND... Finish Date is <= Today Yellow
*Percent Complete >0
AND... At Risk Flag is Checked.... Red
*Percent Complete >0%
AND... Finish Date is > Today Green
Otherwise, Not Started
=IF(ISBLANK([Task Name]@row), "", IF([% Complete]@row = 1, "B", IF(AND([% Complete]@row > 0, [Finish Date]@row < TODAY()), "Y", IF(AND([% Complete]@row > 0, [Finish Date]@row >= TODAY(), [At Risk]@row = 0), "G", IF(AND([% Complete]@row > 0, [At Risk]@row = 1), "R", "Not Started")))))
Looking at data though, I think we'd want to boost the priority of the Risk Flag  because I'm guessing if that's checked, regardless of anything else (other than 100% complete), it should show red, right? If so, you'd switch the order a little bit:
=IF(ISBLANK([Task Name]@row), "", IF([% Complete]@row = 1, "B", IF([At Risk]@row = 1, "R", IF(AND([% Complete]@row > 0, [Finish Date]@row < TODAY()), "Y", IF(AND([% Complete]@row > 0, [Finish Date]@row >= TODAY()), "G", "Not Started")))))

That worked wonderfully, thank you so much. I really appreciate your help.
Help Article Resources
Categories
Check out the Formula Handbook template!