Can't get the "Ongoing" piece to work
I'm trying to make a formula, a typical status formula which I do often. But it was asked to include an "Ongoing" status when the % Complete column is at 99%, regardless of other conditions.
The problem I'm running into is that no matter how I input it, the "In Progress" piece always overrides it, and I can't quite get the ongoing piece to work. I've tried a couple of different strings for the formula, and they both had the same issue:
Attempt 1:
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", IF(AND([Due Date]@row < TODAY(), [% Complete]@row < 1), "Overdue", IF([% Complete]@row = 99, "Ongoing", IF(AND([Due Date]@row > TODAY(), [% Complete]@row > 0), "In Progress", "")))))
Attempt 2:
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", IF(AND([Due Date]@row < TODAY(), [% Complete]@row < 100), "Overdue", IF(AND([Due Date]@row > TODAY(), [% Complete]@row > 0, [% Complete]@row < 99), "In Progress", IF([% Complete]@row = 99, "Ongoing", "")))))
This was the original formula:
=IF([%Complete]@row = 1,"Complete", IF([% Complete]@row = 0, "Not Started", IF([DueDate]@row > TODAY(), "In Progress","Overdue")))
Best Answer
-
If you want a particular argument / status to take top priority, you would make that the first IF. So in this case you would use something along the lines of:
=IF([%Complete]@row = 0.99, "Ongoing", IF([%Complete]@row = 1,"Complete", IF([% Complete]@row = 0, "Not Started", IF([DueDate]@row > TODAY(), "In Progress","Overdue")))
Answers
-
If you want a particular argument / status to take top priority, you would make that the first IF. So in this case you would use something along the lines of:
=IF([%Complete]@row = 0.99, "Ongoing", IF([%Complete]@row = 1,"Complete", IF([% Complete]@row = 0, "Not Started", IF([DueDate]@row > TODAY(), "In Progress","Overdue")))
-
Ah that makes sense. Thanks for the quick solution Paul, you saved the day! :)
Help Article Resources
Categories
Check out the Formula Handbook template!