Help

Re: Help with Complex IF and ISERROR Formula

Solved
Jump to Solution
828 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Carlos-Werq
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello! I have a Base with several complex formulas that I’m trying to pair down to be a bit more streamlined. If been trying to get the following formula to work for days with no luck. If anyone can help, it would be super appreciated… Here’s the setup:

Title Category (Detail) / Single Select Field: Feature, Series (Comedy), Series (Documentary)… etc.

Title Category (Main) / Nested IF Formula Field: This formula looks and the Genre Single select field and determines whether the record falls under four more macro-categories: Series, Feature, Sizzle, or Other.

Title Release Date / Date Field: Date field that every record has formatted as YYYY-MM-DD

Season Number / Single Select Field: Only records for series’ have this selected. If the record is for a movie it is left blank

The formula field I need help with is titled Year/Season For Campaign Title. What I want this formula to do is the following:
IF Title Category (Main) is Series, then use Concentrate to put parenthesis around the Season # field.
IF Title Category (Main) is anything else, then use Concentrate to put parenthesis around the release date in YYYY format.
Lastly, if there is no release date and the Title Category (Main) field is anything but series than put (TBD).

The result of the Year/Season For Campaign Title field is used to make up the record field. I use a formula in the record field to combined the title of the show with whatever is the result of Year/Season For Campaign Title. So like Name of Series (S1), Name of Movie (2002), Name of Movie (TBD). Is an example of what is in the far let.

The formula I have is this:

IF({SeasonNumber}=BLANK(),CONCATENATE("(",{Title Release Date}!="",DATETIME_FORMAT({Title Release Date},‘YYYY’),")"),CONCATENATE("(",{SeasonNumber},")"))

The results are less than spectacular:
(S1)
(S1)
(S1)
(S2)
(TRUE2020)
#ERROR!
#ERROR!

I have to fix the the (YYYY) result because I don’t want the “TRUE” in there. And on top of that I have to fix the formula so that instead of #ERROR! I get (TBD). How do I accomplish this? Any thoughts?

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Does this do what you want?

"(" &
IF(
   {SeasonNumber},
   {SeasonNumber},
   IF(
      {Title Release Date},
      {Title Release Date},
      "TBD"
   )
) & ")"

See Solution in Thread

2 Replies 2
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Does this do what you want?

"(" &
IF(
   {SeasonNumber},
   {SeasonNumber},
   IF(
      {Title Release Date},
      {Title Release Date},
      "TBD"
   )
) & ")"

Thank you so much, Jeremy!