Nested IF Then And Statment

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?

Find more posts tagged with

Construction

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…