Nested Formulas issue

Andres Pimentel

Hello all,

I am currently working on moving some trackers into sheets from excel and I have hit a roadblock with the following formula which works in excel. I have looked at the template formula sheet and I can get them individually to work however not together. Thank you in advance for any help.

 

=IF(J6>0,"Task Complete",IF(H6<=0,"",H6-TODAY()))

I forgot to mention that currently I can get them working separately but not together with the formulas below. I ended up using ISBLANK because was the only way I saw progress. The cell the result is expected is formatted to text/number and the other two are in date format.

=IF(ISBLANK([Date Completed]9), "", "Task Complete")

=IF(ISBLANK([Target Date]10), "", ([Target Date]10 - [Target Date]1))

 

Mike Wilday

I see a couple of issues with the first formula. First of all, you might not be calling the column or row correctly. It appears that you are looking for Column J row 6, but in Smartsheets, you have to call columns by their name. Take a look at your second examples. If your column name has a space in the title surround it by brackets [column name]. Then, add the row number you are referencing. i.e. [Column Name]6. Try replacing your formula with that. 

Also, because your output could be a date, you will need to make sure that the column containing the formula is in the date format as the output could be a date. 

Are you getting a particular error? I keep the following link bookmarked as a reference for cell errors to help troubleshoot! https://help.smartsheet.com/articles/2476176-formula-error-messages

Andres Pimentel

Hi Mike, 

Thanks for the response the example with column J was how it worked in Excel. In the end after lots of playing around and searching on google for how nested formulas could work. I was able to get it to work with the following formula.

=IF(AND(ISBLANK([Target Date]6), ISBLANK([Date Completed]6)), "", IF(ISBLANK([Date Completed]6), ([Target Date]6 - TODAY()), "Task Complete"))