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

[Kevin_Leahy]

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.

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!

[Justin_Barrett]

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

[Kevin_Leahy]

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

[Justin_Barrett]

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?