Unparseable Error

Rowenna Otazu
Rowenna Otazu ✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    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

    image.png


    cheers,

    Kelly

    · Share on FacebookShare on Twitter
  • Rowenna Otazu
    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

    · Share on FacebookShare on Twitter
  • Rowenna Otazu
    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

    · Share on FacebookShare on Twitter
  • Kelly Moore
    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 your row 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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!