Skip to main content
Solved

Help combining Concatenate and IF

A

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!

Best answer by Justin_Barrett

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 ", ...
View original
Did this topic help you find an answer to your question?

6 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • March 10, 2021

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?

A
  • Alan_Burdick
  • Author
  • New Participant
  • 4 replies
  • March 10, 2021
augmented wrote:

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?


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’)))

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • 326 replies
  • March 10, 2021
Alan_Burdick wrote:

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’)))


That looks right. Try it!

Justin_Barrett
Forum|alt.badge.img+20

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 ", ...
A
  • Alan_Burdick
  • Author
  • New Participant
  • 4 replies
  • March 10, 2021

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’)))

A
  • Alan_Burdick
  • Author
  • New Participant
  • 4 replies
  • March 10, 2021
Alan_Burdick wrote:

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’)))


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

Justin_Barrett
1 person likes this
  • Justin_Barrett
    Justin_Barrett

Reply


Most helpful members this week

Terms & Conditions