#UNPARSEABLE Formula

I am trying to get this formula to work correctly in final calculations of a project we are working on.

=IF([Column3]2="","",IF([Column17]2="",CONCATENATE("it's been ",TODAY()-[Column5]2," days"),IF([Column17]2-[Column16]2<0,CONCATENATE(-([Column17]2-[Column16]2)," days to spare"),CONCATENATE([Column17]2-[Column5]2," days overdue"))))

I am not sure what needs to be changed here.

Please help

Find more posts tagged with

Formulas

Comments

Mike Wilday

It looks like there is a floating dash before the column name in the days to spare concatenate section.

Chris McKay

Hi Justin,

Simple answer. The CONCATENATE function does not exist in Smartsheet. What you want is JOIN.

You also have a stray - in your second CONCATENATE function:

CONCATENATE(-([Column17]2

I'd also avoid using ="" where possible. Instead, consider using:

=IF(ISBLANK([Column3]2, ""

Hope this helps.

Kind regards,

Chris McKay

Ha, you beat me to it by 4 minutes

justin46491

I just tried that and it still is not working.

Is there anything else we can try

justin46491

I just updated everything as suggested and it is still not working. I am still getting the #UNPARSEABLE

=IF(ISBLANK([Column3]1="","",IF([Column17]1="",JOIN("it's been",TODAY()-[Column5]1,"days"),IF([Column17]2-[Column16]1<0,JOIN(-([Column17]2-[Column16]1),"days to spare"),JOIN([Column17]1-[Column5]1,"days overdue"))))

Chris McKay

Hi Justin,

It looks as though all you did was update CONCATENATE to JOIN and add in ISBLANK. There is still a stray dash in there and ISBLANK is not formatted in the way I posted.

You don't actually need to use JOIN to achieve what you want anyway. I think the formula you want is:

=IF(ISBLANK([Column3]1), "", IF(ISBLANK([Column17]1), "it's been " + NETWORKDAYS(TODAY(), [Column5]1) + " days", IF([Column17]2 - [Column16]1 < 0, NETWORKDAYS([Column17]2, [Column16]1) + " days to spare", NETWORKDAYS([Column17]1, [Column5]1) + " days overdue")))

Kind regards,

Chris McKay

justin46491

I did this also now and I am still getting the same error message

Mike Wilday

Do your columns have titles? Or are they titled Column5, Column17 etc. If you updated them to have a title, you will need to use your column titles.

justin46491

I did update the column names

Chris McKay

As much as it'll pain you, you're better off starting over. Start by typing out (not pasting) the formula I posted last.

justin46491

Now I am getting Incorrect Argument Set

Sign in to join the conversation!

Quick Links

Trending discussions

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…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…