Formula Help

Neela_15

I am getting an #UNPARSEABLE error in the below formula. The formula is to return "See Total Time to Close Column" if the Total Time To Close column is not blank, otherwise, perform the calculations. The calculation formula works correctly without IF(NOT(ISBLANK . . . part of the formula. (i.e., IF(NETDAYS([FAA Received

Date]2, TODAY()) > 365, ROUNDDOWN(NETDAYS([FAA Received Date]2, TODAY()) / 365, 1) + " Years", NETDAYS([FAA Received Date]2, TODAY()) + " Days"). I have tripled checked all spellings. I can only assume it is a parenthesis issue, but they all seem to be correct.

=IF(NOT(ISBLANK([Total Time To Close]2)), "See Total Time to Close Column", IF(NETDAYS([FAA Received Date]2, TODAY()) > 365, ROUNDDOWN(NETDAYS([FAA Received Date]2, TODAY()) / 365, 1) + " Years", NETDAYS([FAA Received Date]2, TODAY()) + " Days"))

NOT-ISBLANK HELP.pdf

Kelly Moore

Hey @Neela_15

I checked your formula in my test sheet and it appeared to work for me. I did a simple copy paste from your post to my sheet (After verifying it worked, I changed to @row)

You'll note I changed your row numbers to @row rather than the specified row numbers. If you don't require a specific call to a row number, use @row for more robust formulas.

=IF(NOT(ISBLANK([Total Time To Close]@row)), "See Total Time to Close Column", IF(NETDAYS([FAA Received Date]@row, TODAY()) > 365, ROUNDDOWN(NETDAYS([FAA Received Date]@row, TODAY()) / 365, 1) + " Years", NETDAYS([FAA Received Date]@row, TODAY()) + " Days"))

If you copy paste the formula back into your sheet, do you still receive an UNPARSEABLE error? If yes, please provide a screenshot of the formula with the colored text.

Kelly Moore

Hey Neela

For the sake of trouble shooting, let's delete the working section of your IF. This is what I do when I can't get a formula to work - I add it back section by section.

=IF(NOT(ISBLANK([Total Time To Close]@row)), "See Total Time to Close Column", IF(NETDAYS([FAA Received Date]@row, TODAY()) > 365, "True", "False"))

Also, fyi, another way to write the Not IsBlank without all the parentheses to deal with is to use the <>

=IF([Total Time To Close]@row<>"", "See Total Time to Close Column", "False")

Kelly Moore

Hey @Neela_15

I checked your formula in my test sheet and it appeared to work for me. I did a simple copy paste from your post to my sheet (After verifying it worked, I changed to @row)

You'll note I changed your row numbers to @row rather than the specified row numbers. If you don't require a specific call to a row number, use @row for more robust formulas.

=IF(NOT(ISBLANK([Total Time To Close]@row)), "See Total Time to Close Column", IF(NETDAYS([FAA Received Date]@row, TODAY()) > 365, ROUNDDOWN(NETDAYS([FAA Received Date]@row, TODAY()) / 365, 1) + " Years", NETDAYS([FAA Received Date]@row, TODAY()) + " Days"))

If you copy paste the formula back into your sheet, do you still receive an UNPARSEABLE error? If yes, please provide a screenshot of the formula with the colored text.

Neela_15

Thank you Kelly. I copied your formula in the cell and am still getting the unparseable error, but I am confident, based on your testing, the formula is correct. Something I noticed is that in yours and in my original equation on the left in the below picture, the column names are color coded. But as you see, on the right, the column names don't have any color as if Smartsheet isn't recognizing them. I think I will let the formula sit for a day and see if anything changes. I may have to open a ticket with Smartsheets. A few months ago, I had a similar situation where I had an unparseable error using COUNTIFs, came in the next day, and the formula was working.

Kelly Moore

Hey Neela

For the sake of trouble shooting, let's delete the working section of your IF. This is what I do when I can't get a formula to work - I add it back section by section.

=IF(NOT(ISBLANK([Total Time To Close]@row)), "See Total Time to Close Column", IF(NETDAYS([FAA Received Date]@row, TODAY()) > 365, "True", "False"))

Also, fyi, another way to write the Not IsBlank without all the parentheses to deal with is to use the <>

=IF([Total Time To Close]@row<>"", "See Total Time to Close Column", "False")

Neela_15

Kelly,

Success! After trying your suggested approach, I still received the #unparseable error. This led me to think there was something wrong with the "Total Time To Close" column, so I was going to delete the column and recreate it. Before doing so, I checked its properties and behold, notice an extra space between "Time" and "To," which does not show (at least clearly to my eyes) in the column header in sheet view. The below formula now works. I guess my lesson learned is that one should look at the field properties and not rely on what you see in the column heading. Thank you for your assistance and patience in helping resolve this issue.

Kelly Moore

Great troubleshooting! Good for you! Yes, one of the tricks I have learned is to, one by one, delete all cell references and re-insert, making sure I re-insert by clicking into the desired cell. Glad you found it!

Kelly