Workday formula gives me #UNPARSEABLE

Msamples76711

Hi.  I'm trying to calculate a Deadline date based on varying lead time (calculated separately in another column).   I have a confirmed lead time column (can very between 4 types) and a deadline calculation column (IA Date Calculation) using "IF" formula (if 8 weeks lead time = 5 business days, if 16 weeks = 10 business days, etc).  I am trying to calculate the deadline date using the date submitted (auto calculated) and the IA Date Calculation column. 

My formula is =WORKDAY([Date of Submission]60,[IA Date Calculation]60,)

I keep getting #UNPARSEABLE... what am I doing wrong?

Mike Wilday

Remove the comma before the final Parenthesis. 

Like so.

=WORKDAY([Date of Submission]60,[IA Date Calculation]60)

Msamples76711

Hi.  I removed the coma and now I'm getting #INVALID DATA TYPE.  Not sure how to get this to work properly. 

Mike Wilday

Thanks for the screenshot. it looks like you're using a system column to get the date/time. To convert your formula try this... 

=WORKDAY(Dateonly([Date of Submission]60),[IA Date Calculation]60)

See if that works for you.

Check out my screenshot to see how I set it up. 

Mike Wilday

The reason it wasn't working is that the system time columns aren't "date" columns and they have to be converted with =Dateonly.

• https://help.smartsheet.com/function/dateonly

Msamples76711

Hi Mike, great news! I was able to get everything working! Thank you!!  I did find that I had to revise a few things for it to get it working.  First, my column, like you mentioned, had to be a "Date" column.   Then, my formula under the IA Date Calculation column had to be revised as follows:

Original: =IF([Confirmed Required Lead Time]60 = 8, "5", IF([Confirmed Required Lead Time]60 = 10, "10", IF([Confirmed Required Lead Time]60 = 16, "25", IF([Confirmed Required Lead Time]60 = 24, "40"))))

New: =IF([Confirmed Required Lead Time]60 = 8, 5, IF([Confirmed Required Lead Time]60 = 10, 10, IF([Confirmed Required Lead Time]60 = 16, 25, IF([Confirmed Required Lead Time]60 = 24, 40))))

Lastly, my final formula 

=WORKDAY([Date of Submission]60, [IA Date Calculation]60)

Apparently, removing the " " from my IA Date Calculation along with everything else you suggested made the formula work properly.

Thanks again!! 

 

Mike Wilday

Yep! That makes sense. Removing the Quotes makes those actual numbers. Glad we got it figured out!