#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
Comments
-
=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.
-
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
-
I just tried that and it still is not working.
Is there anything else we can try
-
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"))))
-
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
-
I did this also now and I am still getting the same error message
-
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.
-
I did update the column names
-
As much as it'll pain you, you're better off starting over. Start by typing out (not pasting) the formula I posted last.
-
Now I am getting Incorrect Argument Set
Help Article Resources
Categories
Check out the Formula Handbook template!
