Help combining Concatenate and IF

[Alan_Burdick]

I’m an Airtable noob with what I hope is an easy-to-answer questions for someone (unlike me) who knows what they’re doing.

I’m building a content scheduler for my editorial team; it includes a view of stories sorted by week. To do that, I created a field (WEEK) containing a concatenation formula built around the story’s expected publication date (PUB DATE):

CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({PUB DATE},-DATETIME_FORMAT({PUB DATE},‘e’),‘days’),‘M/D’))

That works great for those stories with an assigned PUB DATE, sorting them under “Week of 2/14” or “Week of 3/23” or whatever. But some stories don’t have a pub date assigned yet, and those date fields are blank. Here my formula fails, filing those stories under "Week of “#ERROR”

How do I get rid of the #ERROR ? Seems like I need to combine concatenate with some sort of IF statement, but that’s beyond my brainpower. Any ideas? No doubt there’s a much simpler way to do this. Thanks!

[augmented]

Hi Alan. It’s not at all beyond you. The way IF works is the first argument makes a boolean evaluation (true or false). It can be checking that something is =,<,>, etc or it can be checking for non-empty (and probably other things too). The second argument is what IF should return if the first argument is true. The third (optional) argument is what to return if the first is false. So, for you…

IF({PUB DATE}, your formula)

The first argument {PUB DATE} evaluates to true if it is not empty. The second is your CONCATENATE formula, and the third we optioned to not include so by default it returns nothing (but not ERROR).

Make sense?

[Alan_Burdick]

augmented:

The way IF works is the first argument makes a boolean evaluation (true or false). It can be checking that something is =,<,>, etc or it can be checking for non-empty (and probably other things too). The second argument is what IF should return if the first argument is true. The third (optional) argument is what to return if the first is false. So, for you…

IF({PUB DATE}, your formula)

The first argument {PUB DATE} evaluates to true if it is not empty. The second is your CONCATENATE formula, and the third we optioned to not include so by default it returns nothing (but not ERROR).

Make sense?

Thank you for your encouragement! So then it would look like … this?

IF({PUB DATE},
CONCATENATE(“Week of”," ",DATETIME_FORMAT(DATEADD({PUB DATE},-DATETIME_FORMAT({PUB DATE},‘e’),‘days’),‘M/D’)))

[augmented]

That looks right. Try it!

[Justin_Barrett · Accepted solution]

Alan_Burdick:

CONCATENATE(“Week of”," ", …

As a quick aside, you can combine these pieces. Inserting a separate string just to add a space after “Week of” isn’t necessary. This is cleaner:

CONCATENATE("Week of ", ...

[Alan_Burdick]

Many thanks to you both. However, I’m getting an “invalid formula” message when I enter the following:

IF({PUB DATE},CONCATENATE(“Week of ”,DATETIME_FORMAT(DATEADD({PUB DATE},-DATETIME_FORMAT({PUB DATE},‘e’),‘days’),‘M/D’)))

[Alan_Burdick]

Aha, now it works, for whatever reason! Thank you!