Help

Re: Help combining Concatenate and IF

Solved
Jump to Solution
897 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Alan_Burdick
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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 ", ...

See Solution in Thread

6 Replies 6
augmented
10 - Mercury
10 - Mercury

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

That looks right. Try it!

Justin_Barrett
18 - Pluto
18 - Pluto

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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!