Formula error help: Not Started, Complete, In Progress, Overdue
Hello - Looking for assistance to resolve #INCORRECT ARGUMENT SET error.
I believe I am overlooking it, because I have been focused on it for too long.
If the due date is in the future, I want "Not Started"
Formula giving error:
=IF([% Completion]@row = 1, "Complete", IF(AND([% Completion]@row < 1, [Due Date]@row < TODAY(0)), "Overdue", IF(AND([% Completion]@row < 1, [Due Date]@row > TODAY(0)), "Not Started"), "In Progress"))
_______________________________________________________________________
The below formula makes everything "In Progress" if not complete or Overdue and I want to add argument for "Not Started"
=IF([% Completion]@row = 1, "Complete", IF(AND([% Completion]@row < 1, [Due Date]@row < TODAY(0)), "Overdue", "In Progress"))
Appreciate another set of eyes to help.
Best Answer
-
@CAPM_JBibb I think @Rodolfo Garcia is on the correct path and that does fix your current formula in terms of errors. I do think, however, that you have potentially missed two scenarios. The following is broken down by dates in the past, today and dates in the future. For "today" (the green section) if the % Completion is 0% it displays as in progress, I would think that would still be Not Started. And for tomorrow, if the % Completion is greater than 0% it still would show as Not Started even though it has been started.
Change the formula to this:
=IF([% Completion]@row >= 1, "Completed", IF(AND([Due Date]@row >= TODAY(), [% Completion]@row > 0), "In Progress", IF(AND([% Completion]@row < 1, [Due Date]@row < TODAY()), "Overdue", "Not Started")))
Now the results would reflect as follows:
Answers
-
=IF([% Completion]@row = 1, "Complete", IF(AND([% Completion]@row < 1, [Due Date]@row < TODAY(0)), "Overdue", IF(AND([% Completion]@row < 1, [Due Date]@row > TODAY(0)), "Not Started", "In Progress")))
I believe the only thing I corrected was removing the ) after the "Not Started"
-
Hello Bibb
I try to find this solution
I hope it helps
Regards
Rodolfo Garcia
-
@CAPM_JBibb I think @Rodolfo Garcia is on the correct path and that does fix your current formula in terms of errors. I do think, however, that you have potentially missed two scenarios. The following is broken down by dates in the past, today and dates in the future. For "today" (the green section) if the % Completion is 0% it displays as in progress, I would think that would still be Not Started. And for tomorrow, if the % Completion is greater than 0% it still would show as Not Started even though it has been started.
Change the formula to this:
=IF([% Completion]@row >= 1, "Completed", IF(AND([Due Date]@row >= TODAY(), [% Completion]@row > 0), "In Progress", IF(AND([% Completion]@row < 1, [Due Date]@row < TODAY()), "Overdue", "Not Started")))
Now the results would reflect as follows:
-
Thank you for the assistance all of you
@Dave Stanley you hit the nail on the head - that worked perfectly. I knew I was missing something and needed a fresh view. Thanks🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!