#UNPARSEABLE Formula

justin46491

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 

Mike Wilday

=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"))))

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

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