#INCORRECT ARGUMENT

Options
Silvia Rangel
Silvia Rangel ✭✭
edited 07/27/22 in Formulas and Functions

Hello Community,

Having a hard time trying to figure out why my formula broke. Initially, the "As Built Accepted?" column was a checkbox and the formula was working correctly:

Column changed to date and the formula breaks. Changed the 1st condition to be a date, and added a couple of status additional to "Job Completed" and formula breaks (#UNPARSEABLE):

=IF(AND(ISDATE([As Built Accepted?]@row), "Job Completed", IF([Construction Status]@row = "On-Hold", "On-Hold", IF(ISDATE([Actual Construction Start]@row), "Construction in Process", IF([Construction Status]@row = Cancelled/Returned", "Job Cancelled/Returned", IF(ISDATE([Date sent to Construction]@row), "Sent to Construction", IF(ISDATE([Actual Design Completion Date]@row), "Design Completed", IF([Design Contractor]@row = "Asplundh Construction LLC", "Assigned to Designer")))))))

for managing this tracker, On-Hold and Cancelled/Returned can happen at any given time after the construction starts, so unsure where the error is.


Thanks!

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Silvia Rangel

    The syntax in the AND term is not what Smartsheet is expecting. It appears the original formula is missing some information?

    Is it possible your formula is this instead?

    =IF(ISDATE([As Built Accepted?]@row), "Job Completed", IF([Construction Status]@row = "On-Hold", "On-Hold", IF(ISDATE([Actual Construction Start]@row), "Construction in Process", IF([Construction Status]@row = "Cancelled/Returned", "Job Cancelled/Returned", IF(ISDATE([Date sent to Construction]@row), "Sent to Construction", IF(ISDATE([Actual Design Completion Date]@row), "Design Completed", IF([Design Contractor]@row = "Asplundh Construction LLC", "Assigned to Designer")))))))


    Kelly

  • Silvia Rangel
    Silvia Rangel ✭✭
    Answer ✓
    Options

    Hello Kelly,

    In the original formula, the "AND" works for the "As-Built Accepted?" column as a checkbox, maybe Smartsheet didn't like it in a date column? In any case, it worked, tried so many combos but didn't think to remove the "AND".


    Thanks!

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Silvia Rangel

    The syntax in the AND term is not what Smartsheet is expecting. It appears the original formula is missing some information?

    Is it possible your formula is this instead?

    =IF(ISDATE([As Built Accepted?]@row), "Job Completed", IF([Construction Status]@row = "On-Hold", "On-Hold", IF(ISDATE([Actual Construction Start]@row), "Construction in Process", IF([Construction Status]@row = "Cancelled/Returned", "Job Cancelled/Returned", IF(ISDATE([Date sent to Construction]@row), "Sent to Construction", IF(ISDATE([Actual Design Completion Date]@row), "Design Completed", IF([Design Contractor]@row = "Asplundh Construction LLC", "Assigned to Designer")))))))


    Kelly

  • Silvia Rangel
    Silvia Rangel ✭✭
    Answer ✓
    Options

    Hello Kelly,

    In the original formula, the "AND" works for the "As-Built Accepted?" column as a checkbox, maybe Smartsheet didn't like it in a date column? In any case, it worked, tried so many combos but didn't think to remove the "AND".


    Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!