Formula to calculate 'status' based upon % Complete and a Checkbox

On a project plan, we have a column for “Task Not Applicable”, and we have an automation rule that when this box is checked, the “% Complete” field changes to “100%”.

When the box is checked, the below formula changes the “Status” field to “Complete”, instead of “Not Applicable”. Can you help me write the formula so that:

“% Complete” = 1, and “Box” is not checked, then make the “Status” equal “Complete”, and if

“% Complete” = 1, and “Box” is checked, then make the “Status” equal “Not Applicable”?

Existing Formula...: =IF([% Complete]@row = 1, "Complete", IF([Task Not Applicable]@row = 1, "Not Applicable", IF(AND(OR([% Complete]@row = 0, [% Complete]@row = ""), {Capital Women's Care - Metadata Range 1} < Finish@row), "Not Started", IF(AND([% Complete]@row <> 1, {Capital Women's Care - Metadata Range 1} > Finish@row), "Overdue", "In Progress"))))

… Additionally, I no longer recall what the “ {Capital Women's Care - Metadata Range 1} “ is referencing and it doesn’t make sense to me. I’m guessing it’s supposed to be “today”. Can you help me edit that as well?

Thank you!!!

Tags:

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can take care of the checked box portion by simply moving that particular IF statement to the front of the formula.

    =IF([Task Not Applicable]@row = 1, "Not Applicable", IF([% Complete]@row = 1, "Complete", IF(AND(OR([% Complete]@row = 0, [% Complete]@row = ""), {Capital Women's Care - Metadata Range 1} < Finish@row), "Not Started", IF(AND([% Complete]@row <> 1, {Capital Women's Care - Metadata Range 1} > Finish@row), "Overdue", "In Progress"))))


    As for the range, it probably is TODAY(), but I also see a number of other ways this formula can be simplified. Give this a shot and see if it is working as needed...

    =IF([Task Not Applicable]@row = 1, "Not Applicable", IF([% Complete]@row = 1, "Complete", IF(TODAY() < Start@row, "Not Started", IF(TODAY() > Finish@row, "Overdue", "In Progress"))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Farmer
    Mary Farmer ✭✭✭✭✭

    Hi @Paul Newcome . The formula works great - except if I enter a percent other than zero or 100, the Status remains "Not Started". Can you help? Thanks!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Mary Farmer My apologies. I didn't see anything in the previous formula that would do that, so I didn't work it in. Give this a try...

    =IF([Task Not Applicable]@row = 1, "Not Applicable", IF([% Complete]@row = 1, "Complete", IF(TODAY() > Finish@row, "Overdue", IF([% Complete]@row> 0, "In Progress", IF(TODAY() < Start@row, "Not Started", "In Progress")))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Farmer
    Mary Farmer ✭✭✭✭✭
    Answer ✓

    @Paul Newcome Awesome!! Thanks so much!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!