#INCORRECT ARGUMENT

Question

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!

Kelly Moore · Accepted Answer

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 · Accepted Answer

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!