Nested IF Then And Statment

Brad Worth

I am trying to finalize a Nested If Then /And Statement.  In one of my logical functions, I am trying move to the next condition if a cell has a date input into.  When I try to use the logic if cell > 0, it returns an error.  What would be the best method for moving the formula to the next logical statement?

Javed Hassan

Date values in Smartsheet are different than Excel in that they don't have an integer value. So you can't use > [integer] you would have to use > [date]. Instead, try using <>"". This logic checks to see if a cell is not blank.

Good luck! If you're still getting an error, it's likely elsewhere in the formula. Feel free to share the rest of the formula for more assistance.

Paul Newcome

You could also use IF(ISDATE([Column Name]@row)......

 

That will check to see if it is a date.

Lupe Rincon

Due to the high volume of work, we have a need to automate the Actual Start/Actual End dates on children tasks based on % Complete. I have not been able to find logic to solve our problem. 

I'm stuck on   =IF(([% Complete]7, "100%", "TODAY()"), ([% Complete]7, < "100%", ([Planned End]7))) 

Brad Worth

Boom!!  That worked.  Thank you Javed!!

Paul Newcome

Smartsheet reads percentages as 100% = 1, 50% = .5, etc. You also do not need any of the quotation marks, and you have extra, unnecessary parenthesis and commas tucked in. Try this...

 

=IF([% Complete]7 = 1, TODAY(), IF([% Complete]7 < 1, [Planned End]7))

 

I also recommend starting a new discussion when you have a new question. That way it doesn't get buried in an old thread or anything like that as most people look at most recent and/or unanswered questions first. 

Javed Hassan

Paul's formula is right, but keep in mind that using your formula, the date in the column will move. It will not just stay as the date the % complete went to 100%. I'm not sure if you're wanting that, but figured I would mention it.