Nested IF Then And Statment

Brad Worth
edited 12/09/19 in Formulas and Functions

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
    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
    Paul Newcome ✭✭✭✭✭✭

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


    That will check to see if it is a date.

  • 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))) 

  • Boom!!  That worked.  Thank you Javed!!

  • Paul Newcome
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!