Adding formula to existing formula for status change

Nathan Nguyen
Nathan Nguyen ✭✭
in Formulas and Functions

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.


image.png


Tags:
· Share on FacebookShare on Twitter

Best Answer

  • MCorbin
    MCorbin Overachievers Alumni
    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")))))

    · Share on FacebookShare on Twitter

Answers

  • MCorbin
    MCorbin Overachievers Alumni
    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")))))

    · Share on FacebookShare on Twitter
  • Nathan Nguyen
    Nathan Nguyen ✭✭
    https://community.smartsheet.com/discussion/comment/258332#Comment_258332

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!