Nested IF Then And Statment

08/11/18 Edited 12/09/19

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?

Comments

  • Javed HassanJaved 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 NewcomePaul Newcome ✭✭✭✭✭

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

     

    That will check to see if it is a date.

    thinkspi.com

  • 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 NewcomePaul 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. 

    thinkspi.com

  • Javed HassanJaved 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.

Sign In or Register to comment.