Doing two things in one IF statement

Ben Donahue

WHAT I'M TRYING TO DO:

I would like to accomplish two things in one IF statement. Something like:

=IF([This Column]@row = 1, [That Column]=true ALSO Today(), ....)

WHY I'M TRYING TO DO THIS:

I am trying to automate the Actual Finish column in my project management sheets such that, when % Complete reaches 100, then check a helper column AND set the date to Today(). There will be an initial IF to see if the helper column is checked before proceeding to avoid constantly overwriting the Actual Finish column. My instincts say that two things have to happen in one IF statement, but haven't been able to figure that part out, nor a workaround for that, as of yet.

SOME THINGS I'VE TRIED:

Separating the two things to do into two different columns. In a Text/Number column, I have added this formula:

=IF(AND([Actual Finish Helper]@row = 1, [Actual Finish]@row <> ""), TODAY(), "xxx")

I have set the value of [Actual Finish Helper]@row to be 1 and the value of [Actual Finish]@row to be the empty set, seeming to meet the requirements for a true return for the AND function, yet I get no TODAY() return and, instead, get xxx. Troubleshooting brought me to test the components of the AND function separately like so:

=IF([Actual Finish Helper]@row = 1, TODAY())

=IF([Actual Finish Helper]@row <> "", TODAY())

and both return today's date. This seems a very curious result.

Thank you for your help.

Paul Newcome

Your formula specifies to input TODAY() if the [Actual Finish Helper] is checked and the [Actual Finish] is not blank. So when you check the [Actual Finish Helper] and leave the [Actual Finish] blank, you are not meeting both requirements in your AND statement. That is why you are getting "xxx".

 

If you want to enter TODAY() based on the box being checked and the [Actual Finish] being blank, then you need to change 

 

[Actual Finish]@row <> ""

 

to

 

[Actual Finish]@row = ""

.

When you tested each of the AND statements separately, you actually tested the checkbox column twice. The first says "if it is checked" and the second says "if it is not blank". Checked would mean not blank, and that is why you produced a "true" result through that testing.