Adding formula to existing formula for status change

Nathan Nguyen

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.

1. If Finish Date is in the past, Status icon will become Yellow (Y)
2. 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.

MCorbin

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

MCorbin

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

Nathan Nguyen

https://community.smartsheet.com/discussion/comment/258332#Comment_258332

That worked wonderfully, thank you so much. I really appreciate your help.