Workday formula gives me #UNPARSEABLE

Msamples76711 ✭✭✭
edited 12/09/19 in Formulas and Functions

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
    Mike Wilday ✭✭✭✭✭✭

    Remove the comma before the final Parenthesis. 

    Like so.

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

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


  • Mike Wilday
    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
    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.

  • 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
    Mike Wilday ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!