Adding formula to existing formula for status change
Hi everyone,
I currently have a formula in my status column that auto-update 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!