Workday formula gives me #UNPARSEABLE

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?

Find more posts tagged with

Formulas

Services

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…