Formulas will be the death of me-

Firstly, formulas will be the death of me. My coworker wants to to do this-


If the overall plan is 25% complete and 25% through the duration = green

If the overall plan is 50% complete and 50% through the duration = green

If the overall plan is 75% complete and 75% through the duration = green

If the overall plan is 100% complete and 100% through the duration = green

If under the above bench mark but not 0% = yellow

If not at the above bench mark and 0% complete= red


I started this- but I think I am failing. The random 56 is 25% of the duration. I am not sure I like this guy enough to help any more (kidding)- thoughts?

=IF(AND(% Complete@row >24, [Due Date]@row - TODAY() > 56), "Green", IF(AND(% Complete@row >50, [Due Date]@row - TODAY() > 112), "Green", IF(AND(% Complete@row >75, [Due Date]@row - TODAY() > 168), "Green", IF(AND(% Complete@row <24, [Due Date]@row - TODAY() > 56), "Red", IF(AND(% Complete@row <50, [Due Date]@row - TODAY() > 112), "Red", IF(AND(% Complete@row <75, [Due Date]@row - TODAY() > 168), "Red",)))))))

Best Answer

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hi @Bridget Clawson Braaten

    Formulas are fun for some. But this is a beast. What are the challenges this formula is presenting you? What questions do you have about it? Is it failing somewhere specific? Or are you just venting -- which is TOTALLY understandable. haha.

  • Thank you for saying this! Generally I use this go to formula - =IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND(Status@row = "In Progress", [End Date]@row - TODAY() < 10), "Red", IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND(Status@row = "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "", ""))))))))

    But this coworker wants something different where based on the duration of the entire project, the health changes at certain percentages and stages of time left to complete. My formula only got as far as the green health piece. He would like red health if outside certain parameters. So part of me is venting and part of me would like some superhero that has made formulas based on percentages + durations to give me a few tips. I am used to status/end date combos for health (see above).

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hahaha. Glad I could be of help. That was a fun puzzle to solve. :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!