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!!!
Best Answer
-
@Paul Newcome Awesome!! Thanks so much!!
Answers
-
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"))))
-
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!
-
@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")))))
-
@Paul Newcome Awesome!! Thanks so much!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!