Re: Formula to determine blank due dates (across 3 fields)

Bianca_Bailey · ‎Aug 22, 2021

I am currently working on a formula which is able to detect which 1 out of 3 date fields are filled. I have done this with a similar table which detects which 1 of 2 date fields are filled but cannot seems to make the formula work with 3 options.

IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B})

I have tried the following 2 options, could someone help me identify where I am going wrong?

IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date C})=BLANK(){Date A},{Date B},{Date C})

IF(OR({Date A}=BLANK(),{Date B})=BLANK(),{Date A},{Date B}),IF{{Date C}, {Date C})

Justin_Barrett · ‎Aug 22, 2021

This formula has a closing parenthesis in the wrong place, after the first mention of {Date B}. This closes the OR() function prematurely and compares its output with BLANK(), which I’m surprised actually works.

That aside for a moment, one optimization you can use is to take advantage of how Airtable looks at fields simply in terms of filled or unfilled. An empty field equates to False, while a filled field equates to True (most of the time; numeric fields with a 0 are the exception because 0 also equates to False). Because of that, it’s not necessary to compare a field against BLANK().

With all of that considered, here’s how you can simplify the two-field version:

IF(OR({Date A}, {Date B}), IF({Date A}, {Date A}, {Date B}))

That formula first tests to see if a date is in either field. If so, and it’s in {Date A}, use that; otherwise use {Date B}.

For three fields, it’s only a little more complex.

IF(OR({Date A}, {Date B}, {Date C}), IF({Date A}, {Date A}, IF({Date B}, {Date B}, {Date C})))

Here’s the same thing split across several lines to make the logic a little more clear:

IF(
    OR({Date A}, {Date B}, {Date C}),
    IF(
        {Date A}, {Date A},
        IF(
            {Date B}, {Date B}, {Date C}
        )
    )
)