IF Statements with OR

Yes! :winking_face:

If you haven’t discovered this already, the construction you want is

IF(OR(ColumnC=X,ColumnD=X),This,That)

Essentially, you wrap all your ‘or’ conditions — and you’re not limited to two — within the call to OR(). That call is evaluated as a piece and returns TRUE is any of its component tests proves true. That call in turn becomes the test used to determine how the call to IF() branches. There are comparable AND() and XOR() functions, as well — and, of course, can be nested recursively to such a depth as to make your Airtable code as unreadable as mine…

I’m struggling with something similar here and can’t get IF(OR()) as outlined above to work for me. But I’m using a series of rollup and formula fields and I wonder if the error lies in there somewhere. Can anyone help?

In my Transactions table, each transaction has two fields that link to the People table – call these “primary” and “guest.” If there’s an updated email address for anyone in the party, I add today’s date into a date field in the Transactions table after updating the address.

In the People table, I use rollups to get the latest date an email has been changed – “primary email change” and “guest email change.” These output as a date.

Then I use IS_AFTER() in two formula fields to determine if the person’s email has changed since their most recent transaction date, whether they were a primary or a guest, like this:

IS_AFTER({Primary Email Change}, {Transaction Date})

IS_AFTER({Guest Email Change}, {Transaction Date})

Finally, and here’s the trouble: I want a single field that will tell me if either the primary OR the guest email are later than the transaction date. I try the following, but all I get is #ERROR!

IF(OR({Primary Email Changed}=1, {Guest Email Changed}=1), "New Email", "No Change")

Are the IS_AFTER() fields calculating properly, and outputting either 1 or 0?

If so, there doesn’t appear to be any reason that your IF(OR()) statement shouldn’t work.

Not exactly – they are outputting either 1 or #ERROR!

Why might I get the error there? Both fields used in the IS_AFTER() calculation are formatted as dates. One is a rollup and the other is a formula that calculates properly.

Thank you

If the rollup is pulling more than 1 date, the field will not be formatted as a date - instead, it will be formatted as an array.

The “rollup” field is pulling a field that only contains a single date right? And presumably, you want this only pulling the date from a single linked record (not multiple linked records), so that you only get one date to make the comparison with.

That being the case, it might be safer to just use “Lookup” instead of “Rollup”.

Thanks so much for your time @Jeremy_Oglesby. I’m a little ashamed this morning, as I’ve figured out that my problem was rather simple. Since the email change date was only present if an email had actually changed, I was sometimes rolling up blanks to be used in the calculation – i.e. I was using IS_AFTER() to compare a date to an empty value, hence the error.

(Incidentally, this is all part of a kind of elaborate workaround for the lack of a “last modified” function.)

To fix, I’ve made a new field in the Transactions table and am rolling up from there:

IF({Email Change Date}>"", {Email Change Date}, DATETIME_PARSE(01/01/1900))

Now I can successfully use IS_AFTER() to tell me whether the email has been recently updated for either the primary or the guest. But I also find an interesting quirk – Airtable outputs my date as 1/1/1970. Any date before 1970 that I put through ‘DATETIME_PARSE()’ returns 1/1/1970. (This is partly discussed here) This is hardly a dealbreaker, but is it possible to get this to return the year 1900? I use that year elsewhere in the base to denote “historic” data from our pre-Airtable system, so this would just help us maintain some internal consistency.

Thank you

Yes! :winking_face:

If you haven’t discovered this already, the construction you want is

IF(OR(ColumnC=X,ColumnD=X),This,That)

Essentially, you wrap all your ‘or’ conditions — and you’re not limited to two — within the call to OR(). That call is evaluated as a piece and returns TRUE is any of its component tests proves true. That call in turn becomes the test used to determine how the call to IF() branches. There are comparable AND() and XOR() functions, as well — and, of course, can be nested recursively to such a depth as to make your Airtable code as unreadable as mine…