Please help me correct this formula!
=IF(AND([% Complete]@row < 0, [Start date]@row < TODAY(), "Not started", IF(AND([% Complete]@row > 0, [Start date]@row > TODAY(), "In progress", IF(AND([% Complete]@row < 100, [End date]@row < TODAY(), "Overdue", "Complete"))))))
Hi  I've created this formula but getting the "INCORRECT ARGUMENT SET" error message. I think I've made a mistake in the brackets  ( ) etc.
For context, I'm using task completion percentages, task start/end dates, and today's date, to show me if tasks are Not started, In progress, Overdue or Complete.
Many thanks!
Best Answer

=IF(AND([% Complete]@row < 0, [Start Date]@row < TODAY()), "Not started", IF(AND([% Complete]@row > 0, [Start Date]@row > TODAY()), "In progress", IF(AND([% Complete]@row < 100, [End Date]@row < TODAY()), "Overdue", "Complete")))
You need to close the and statements in order to do the Value if true, Value if False statements in the IF. So, you need a close bracket after the Today statements, and to reduce the brackets at the end.
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
Answers

=IF(AND([% Complete]@row < 0, [Start Date]@row < TODAY()), "Not started", IF(AND([% Complete]@row > 0, [Start Date]@row > TODAY()), "In progress", IF(AND([% Complete]@row < 100, [End Date]@row < TODAY()), "Overdue", "Complete")))
You need to close the and statements in order to do the Value if true, Value if False statements in the IF. So, you need a close bracket after the Today statements, and to reduce the brackets at the end.
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Thank you, that has given me a viable formula.
However, now I've applied it to the column, I've realised there's a mistake in my logic to how I set up the formula, as the tasks that are 100% are calculated to be "Overdue" and all other tasks (whether not started or in progress) are "Complete".
Can you please advise how I reorder the formula?

=IF([% Complete]@row = 100, "Complete", IF(AND([% Complete]@row < 0, [Start Date]@row < TODAY()), "Not started", IF(AND([% Complete]@row > 0, [Start Date]@row > TODAY()), "In progress", IF(AND([% Complete]@row < 100, [End Date]@row < TODAY()), "Overdue", "Complete")))
You would be able to add the % Complete =100% as the first parameter, and then run through your options from there.
Can you send me a screenshot of your columns and formula, as when I entered that into my test environment, I am getting the correct values returning.
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

This is the formula that's in there:
=IF(AND([% Complete]@row < 0, [Start date]@row < TODAY()), "Not started", IF(AND([% Complete]@row > 0, [Start date]@row > TODAY()), "In progress", IF(AND([% Complete]@row < 99, [End date]@row < TODAY()), "Overdue", "Complete")))

I've swapped "Overdue" and "Complete" in the formula, and that's fixed the results for these two options. But there's something not right as the other two options aren't being calculated.
Help Article Resources
Categories
Check out the Formula Handbook template!