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

  • Dave Stanley
    Dave Stanley Employee
    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @CAPM_JBibb

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

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭

    Hello Bibb


    I try to find this solution


    I hope it helps


    Regards

    Rodolfo Garcia

  • Dave Stanley
    Dave Stanley Employee
    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:


  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!