Unparseable Error

Rowenna Otazu

Dear Smartsheet Community,

Very new to Smartsheet and having trouble with 2 formulas that work in Excel.

=IF([Date Approved] = 0, [Date Calculation],"Completed")

=NOW() - [Date of Submission]

Could you kindly let me know if the formula structure is incorrectly done? Would appreciate your input. Thank you.

Best,

~ Rowenna

Kelly Moore

Hi@Rowenna Otazu

You need the row designation. This can be accomplished in two ways. The first is the preferred way, which uses the @row designation. Use this always unless you absolutely have to reference a specific cell. In you needed the specific row/cell you would use a number.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#:~:text=0)%20%3D%202016)-,Use%20%40row%20to%20Improve%20Formula%20Performance,a%20large%20number%20of%20rows.&text=The%20above%20formula%20contains%20multiple%20references%20to%20cells%20on%20row%203.

=IF([Date Approved]@row= 0, [Date Calculation]@row,"Completed")

Smartsheet currently doesn't have a NOW() function. For a complete list of functions, if your subscription supports this, go to the Solution Center and download the Formula Sheet. You'll have to continue to check back as smartsheet updates this sheet on occasion

cheers,

Kelly

Rowenna Otazu

Hi, Kelly.

Thanks so much for your help! So much information but eager to learn. For the first formula, I received this error message: #Blocked. Here's how I set it up: =IF([Date Approved]@row = 0, [Date Calculation]@row, "Completed").

For the second formula, I do have access to the formula sheet and I think I can use Today to replace Now in Smartsheet. This is how I set it up: =TODAY()-[Date of Submission]. Still got #Unparseable. Kindly advise what I need to edit. Thank you!

Best,

~ Rowenna

Rowenna Otazu

Hi, Kelly.

I adjusted the 2nd formula by adding @row as you suggested and it seemed to work.

=TODAY() - [Date of Submission]@row

I want to test it by adding a couple more data. Appreciate the help.

Best,

~ Rowenna

Kelly Moore

I didn't pay attention to what data you were collecting - I was only looking at syntax. Assuming [Date Approved] is formatted as date column, it cannot equal zero. Are you looking for it to be blank? If yes, you have the option of ISBLANK() or ="" . If using ISBLANK the referenced cell goes between the parentheses.

=IF(ISBLANK([Date Approved]@row), [Date Calculation]@row, "Completed")

For the second formula you need yourrow reference.

=TODAY()-[Date of Submission]@row

The easiest way to get the correct cell reference is to click in the cell as you're completing the formula. You will know you have valid cell references when all the cell references in the formula become colored. If any are wrong they will all remain black. You will know when you have the correct number of parentheses when the final one is blue. This only indicates correct number, not correct placement.

We'll keep tweaking this until you're all set. If you continue to get 'Blocked', if possible please provide a screenshot of columns and your formula. Also ensure that your date columns are formatted as Date columns in the Column Properties.

Kelly