Please help me correct this formula!

sasss

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

Colleen Patterson

@sasss

=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.

Colleen Patterson

@sasss

=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.

sasss

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 re-order the formula?

Colleen Patterson

@sasss

=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.

sasss

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

sasss

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.