Skip to main content
Solved

Problem with IF + BLANK() + DATETIME_FORMAT

  • July 1, 2021
  • 7 replies
  • 77 views
J

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!

Best answer by Kamille_Parks11

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") & ".")

7 replies

Kamille_Parks11
Forum|alt.badge.img+27

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.

C
J
  • Josh_Martin
  • Author
  • New Participant
  • July 1, 2021

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?

Kamille_Parks11
Forum|alt.badge.img+27

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 (").

J
  • Josh_Martin
  • Author
  • New Participant
  • July 1, 2021

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!

Kamille_Parks11
Forum|alt.badge.img+27

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") & ".")
J
  • Josh_Martin
  • Author
  • New Participant
  • July 1, 2021

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!

Kamille_Parks11
Forum|alt.badge.img+27

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


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

Terms & ConditionsAccessibility statement