Skip to main content

Hey all,


I’m trying to build a formula right now that generates copy based on various fields, specifically a date field. Some of these values have a date and others do not. Whenever I try to build the formula using


DATETIME_FORMAT({Presale Start}, "ddd, M/D")

values that don’t have a date show as #ERROR!.


That being said, I tried using an IF statement but it won’t even save the formula. Can’t tell what I’m going wrong but this is the latest version I’ve tried to use:


IF({Presale Start}=BLANK(), “0“, IF{Presale Start}!=BLANK(), DATETIME_FORMAT({Presale Start}, "ddd, M/D")

Any help would be much appreciated!

Simplify your formula. Your particular usecase is asking a question with a binary answer: is this field blank? Since the answer can only be true or false, you don’t need a nested IF() because IF() already asks what to do if the “question” is true or false. The simplest formula you could write is:


IF(

   {Presale Start},

   DATETIME_FORMAT({Presale Start}, "ddd, M/D")

)


The reason your formula wouldn’t save is probably because of your curly quotes around the zero.

Simplify your formula. Your particular usecase is asking a question with a binary answer: is this field blank? Since the answer can only be true or false, you don’t need a nested IF() because IF() already asks what to do if the “question” is true or false. The simplest formula you could write is:


IF(

   {Presale Start},

   DATETIME_FORMAT({Presale Start}, "ddd, M/D")

)


The reason your formula wouldn’t save is probably because of your curly quotes around the zero.


Thanks for the quick reply!


Here’s the full original formula I built:


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & DATETIME_FORMAT({Date}, "dddd, MMMM D") & "!" & " Don’t miss them live in “ & {Market} & “ with special guests " & {Support} & "!" & " Presale starts " & DATETIME_FORMAT({Presale Start}, "ddd, M/D”) & " at " & DATETIME_FORMAT({Presale Start}, "h:mmA”).

but it was showing #ERROR for values that don’t have a {Presale Start} date entered in that field yet working for values that do have a {Presale Start} date associated with that field.


I just tried replacing


DATETIME_FORMAT({Presale Start}, "ddd, M/D”)

with the formula you suggested and it still shows #ERROR for values that don’t have a {Presale Start} date entered in that field.


Thoughts?

Thanks for the quick reply!


Here’s the full original formula I built:


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & DATETIME_FORMAT({Date}, "dddd, MMMM D") & "!" & " Don’t miss them live in “ & {Market} & “ with special guests " & {Support} & "!" & " Presale starts " & DATETIME_FORMAT({Presale Start}, "ddd, M/D”) & " at " & DATETIME_FORMAT({Presale Start}, "h:mmA”).

but it was showing #ERROR for values that don’t have a {Presale Start} date entered in that field yet working for values that do have a {Presale Start} date associated with that field.


I just tried replacing


DATETIME_FORMAT({Presale Start}, "ddd, M/D”)

with the formula you suggested and it still shows #ERROR for values that don’t have a {Presale Start} date entered in that field.


Thoughts?


Your formula referenced {Presale Start} twice, did you replace it in both places?


Also again, be sure to correct curly quotes (“) with straight ones (").

Your formula referenced {Presale Start} twice, did you replace it in both places?


Also again, be sure to correct curly quotes (“) with straight ones (").


I think I misunderstood - I replaced:


IF({Presale Start} = BLANK(), "0", IF{Presale Start} != BLANK(), DATETIME_FORMAT({Presale Start}, "ddd, M/D")

with the formula you provided:



should I have done that differently? Also noted on the quotations!

I think I misunderstood - I replaced:


IF({Presale Start} = BLANK(), "0", IF{Presale Start} != BLANK(), DATETIME_FORMAT({Presale Start}, "ddd, M/D")

with the formula you provided:



should I have done that differently? Also noted on the quotations!


Your whole formula should look like this.


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & IF({Date}, DATETIME_FORMAT({Date}, "dddd, MMMM D")) & "!" & 

" Don’t miss them live in " & {Market} & " with special guests " & {Support} & "!" & 

" Presale starts " & IF({Presale Start}, DATETIME_FORMAT({Presale Start}, "ddd, M/D")) &  " at " &IF({Presale Start}, DATETIME_FORMAT({Presale Start}, "h:mmA")) & "."

Or this, which seems more logical:


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & IF({Date}, DATETIME_FORMAT({Date}, "dddd, MMMM D")) ​& "!" & 

" Don’t miss them live in " & {Market} & " with special guests " & {Support} & "!" & 

IF({Presale Start}, " Presale starts " & DATETIME_FORMAT({Presale Start}, "ddd, M/D") &  " at " & DATETIME_FORMAT({Presale Start}, "h:mmA") & ".")

Your whole formula should look like this.


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & IF({Date}, DATETIME_FORMAT({Date}, "dddd, MMMM D")) & "!" & 

" Don’t miss them live in " & {Market} & " with special guests " & {Support} & "!" & 

" Presale starts " & IF({Presale Start}, DATETIME_FORMAT({Presale Start}, "ddd, M/D")) &  " at " &IF({Presale Start}, DATETIME_FORMAT({Presale Start}, "h:mmA")) & "."

Or this, which seems more logical:


"JUST ANNOUNCED – " & {Headliner(s)} & " comes to " & {Venue (simplified)} & " on " & IF({Date}, DATETIME_FORMAT({Date}, "dddd, MMMM D")) ​& "!" & 

" Don’t miss them live in " & {Market} & " with special guests " & {Support} & "!" & 

IF({Presale Start}, " Presale starts " & DATETIME_FORMAT({Presale Start}, "ddd, M/D") &  " at " & DATETIME_FORMAT({Presale Start}, "h:mmA") & ".")

That worked perfectly - thanks so much for all the help!

That worked perfectly - thanks so much for all the help!


Great, go ahead and mark that as the solution to close out this thread.

Reply


Terms & Conditions