IF and AND Question

Rich G

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.

MariaCurtis

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!

MariaCurtis

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!

Rich G

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.