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.

 

 

Ben Donahue

Thank you, Paul. Newbie mistake, right? So, I understand the results of my testing now. However, I still can't seem to make this work. I feel, if I am unable to do two things in one IF statement, then I need some kind of order of operations; some kind of concept of "before and after" in the calculations done in the cells. 

Can you comment on the earlier question in my post regarding how to accomplish two things in one IF statement? Something like:

=IF([% Complete]@row = 1, [Actual Finish Helper]=1 ALSO Today(), ....)

In this example, I would want to put this formula in the Actual Finish column.

Paul Newcome

Ok. So you want the formula in the Actual Finish column.

 

I have gathered that you want % Complete to equal 100% and the Actual Finish Helper to equal 1, but I am not sure exactly what you are referring to with the "ALSO TODAY()" portion.

Ben Donahue

The "[Actual Finish Helper]=1 ALSO Today()" portion of that IF statement represents the two things I want to do. I want to set [Actual Finish Helper]@row to 1, and set the value of the cell containing the formula (in this case [Actual Finish]) to be TODAY(). I will use the [Actual Finish Helper] to check to see if the current cell should be updated to be TODAY(), or left along.

Does that make sense?

Paul Newcome

"I want to set [Actual Finish Helper]@row to 1, and set the value of the cell containing the formula (in this case [Actual Finish]) to be TODAY()"

.

This would require a formula in each cell. One in the Helper column to check or uncheck the box and a second formula in the Actual Finish to generate the date.

Andrée Starå

Hi Ben,

The third-party service, Zapier could be an excellent option for this scenario. Is that an option for you?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD