IF and AND Question
I think this is fairly simple but I cannot seem to get it worked out.
I have a Finding Status column. If "Closed", then populate a checkbox column with an unfilled flag. If the Finding Status column is "Open," then look to see if the deadline column is less than Today(), if yes, then the checkbox should be true (solid).
I tried:
=IF([Finding Status]@row = "Finding is Closed", false, true), IF(AND(Finding Status]@row = “Finding is Open”, [Deadline]@row < Today()),true, false)
This returns unparseable. Any assistance would be welcome.
Best Answer

Hi! I think I'm understanding what you're trying to do here. Please take a look at the formula below and resulting screenshot on a sample sheet. You were almost there! Your if statement at the beginning just closes too soon. You need to do what's called a nested if statement.
=IF([Finding Status]@row = "Finding is Closed", false, IF(AND([Finding Status]@row = "Finding is Open", Deadline@row <= TODAY()), true, false))
Note that I did do less than OR EQUAL to today, because I wasn't sure how you wanted to handle if it was same day, but that's an easy change if you want a true less than. I performed this test on 2/18, so the last row is checked because of my "less than or equal."
Hope this helps!
Answers

Hi! I think I'm understanding what you're trying to do here. Please take a look at the formula below and resulting screenshot on a sample sheet. You were almost there! Your if statement at the beginning just closes too soon. You need to do what's called a nested if statement.
=IF([Finding Status]@row = "Finding is Closed", false, IF(AND([Finding Status]@row = "Finding is Open", Deadline@row <= TODAY()), true, false))
Note that I did do less than OR EQUAL to today, because I wasn't sure how you wanted to handle if it was same day, but that's an easy change if you want a true less than. I performed this test on 2/18, so the last row is checked because of my "less than or equal."
Hope this helps!

Outstanding, and I appreciate your fixing my mistake on the Today() less than. I did not consider the same date as Today. Your solution worked perfectly.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!