Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

How do I return "?" for a blank cell instead of "#ERROR!" when writing a concatenate formula?

Topic Labels: Formulas
529 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi all,
I have read through the other threads regarding this subject of blank cells in a formula but wasn’t able to successfully implement a solution.

In my primary field, I have a formula combining the Sponsor Name with the date the Ad Copy was submitted. The two are separated by a space.

image

I would like to change this so that if a Sponsor Name cell is empty, that part of the formula returns “?”. Same with the “Copy Ready Format.” If that is blank, I’d like it to return “?” too.

Can you help me write the formula the way?

Thank you in advance!

3 Replies 3
IF({Sponsor Name}, {Sponsor Name}, "?") & " " & IF({Copy Ready Format}, DATETIME_FORMAT({Copy Ready Format}, "L"), "?")

Hmm. I tried it and took a screenshot.

At the bottom of this screenshot I typed in my desired “Sponsor ID” result for Row 16. Any ideas as to where I went wrong? Thank you

Screen Shot 2020-06-18 at 11.16.38 AM

My bad. I assumed your {Copy Ready Format} field was a pure date field, but it looks like it may be a formatted date already. One way to address that is to pull the date from {Copy Ready} instead:

IF({Sponsor Name}, {Sponsor Name}, "?") & " " & IF({Copy Ready}, DATETIME_FORMAT({Copy Ready}, "L"), "?")

If you prefer the formatting you’ve already applied in the {Copy Ready} field, you could use this, change “L” to “LL” as the formatting specifier.

However, none of this addresses what might be an error in the {Copy Ready Format} field itself. What’s the formula used there?