# Help combining Concatenate and IF

Topic Labels: Formulas
Solved
1441 6
cancel
Showing results for
Did you mean:
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):

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
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 ", ...
``````
6 Replies 6
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…

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?

5 - Automation Enthusiast

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

IF({PUB DATE},

10 - Mercury

That looks right. Try it!

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 ", ...
``````
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’)))

5 - Automation Enthusiast

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