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

Mary Farmer

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!!!

Mary Farmer

@Paul Newcome Awesome!! Thanks so much!!

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

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

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

Mary Farmer

@Paul Newcome Awesome!! Thanks so much!!

Paul Newcome

Happy to help. 👍️