Unexpected results with nested IF statement


#1

Hello,

I have a sheet that looks for an entry in a field and if it exists then it looks to see if there is an override number to count out the number of days before the next customer ‘touch point’, else it defaults to 90 days.

If I break them down, the IF statements work perfectly independently. So just adding 90 days to the field yields the correct results here:

IF({Initial Follow-up with} = “”, BLANK(), DATETIME_FORMAT(DATEADD({Initial Contact Date}, 90, ‘days’),‘MM/DD/YYYY’))

OR, just adding the override field (which can contain any number yields the correct results here:

IF({Initial Follow-up with} = “”, “”, DATETIME_FORMAT(DATEADD({Initial Contact Date}, {Expected Follow-up (in days)}, ‘days’),‘MM/DD/YYYY’))

But when I put them together, if the {Expected Follow-up (in days)} is empty, I get the 90 days appended to the next touch point (where this formula lives); however, if there is any number, except 0, in that field, the calculation yields a blank field:

IF({Initial Follow-up with} = BLANK(), BLANK(), IF({Expected Follow-up (in days)} = BLANK(), DATETIME_FORMAT(DATEADD({Initial Contact Date}, 90, ‘days’), ‘MM/DD/YYYY’, DATETIME_FORMAT(DATEADD({Initial Contact Date}, {Expected Follow-up (in days)}, ‘days’), ’MM/DD/YYYY’)))))

What am I missing? Thanks in advance.
Best,
Charlie-


#2

Try this (but fix anything that gets posed below with curly ’ - make them straight '). I think you may have had a misplaced close paran or two.:

IF({Initial Follow-up with} = BLANK(), BLANK(), IF({Expected Follow-up (in days)} = BLANK(), DATETIME_FORMAT(DATEADD({Initial Contact Date}, 90, ‘days’), ‘MM/DD/YYYY’), DATETIME_FORMAT(DATEADD({Initial Contact Date}, {Expected Follow-up (in days)}, ‘days’), ’MM/DD/YYYY’)))


#3

Here’s @Kevin_Conklin’s formula, indented for clarity, and safe to copy/paste:

IF(
    {Initial Follow-up with} = BLANK(), 
    BLANK(), 
    IF(
        {Expected Follow-up (in days)} = BLANK(), 
        DATETIME_FORMAT(
                        DATEADD(
                            {Initial Contact Date}, 
                            90,
                            'days'
                            ),
                        'MM/DD/YYYY'
                        ), 
        DATETIME_FORMAT(
                        DATEADD(
                            {Initial Contact Date},
                            {Expected Follow-up (in days)},
                            'days'
                            ), 
                        'MM/DD/YYYY'
                        )
        )
    )

Yes, you can paste indented code into the configuration section of a Formula field and have Airtable work just fine. Even better, code copy/pasted from Airtable retains such indentation. (Within the Airtable function editor, indentation is suppressed.) This allows formulas to be created offline, in a standalone editor, and moved to Airtable for testing and validation. Personally, under Windows I use Notepad++, which comes with built-in pair-matching functionality that makes finding a missing ')' or '{' a matter of seconds, but there are hundreds of similar utilities available for virtually any OS and architecture.

@Kevin_Conklin If you precede and follow your formulas with a line that contains only three back-ticks – ``` — you can retain indentation and [some] formatting; in addition, the forum will keep its hands off your single and double quotation marks. Alternatively, if you preface a text string with four space characters, you get the same result:

This
    has
        backticks
    before
        &
    after
These lines
are prefaced
with four taps
of the space bar.

#4

Cool. Thanks. Not sure if my version of his formula actually works or not, hoping @Charles_Pendleton will give it a try.


#5

Thank you @Kevin_Conklin and @W_Vann_Hall.

Kevin, your’s worked once the quotes were fixed, but I had to fix them in AirTable, not my Notes app on the mac as it just seemed to replace with the same - which I wonder if in one of my many attempts was part of the problem.

Mr Hall (I don’t know if you go by “W” or Vann…) your version worked “out of the box”, and when I pasted it out of AirTable and into my Notes app all the line formatting was compressed again. So the indentation seemed not to be preserved.

It would be nice if there was a way to make the formula window in AirTable larger to be able to see everything at once (like say your entire formula when creating it). That little window does not seem to have any drag-arrows associated with it unless I am missing something.

Thank you again for your help and I am sure I will have more questions as I figure this thing out. Have a great day.
Charlie-


#6

Yes, notes or TextEdit on OS X makes quotes the curly quotes by default. You can change that behavior by doing either:

  • Go to System Preferences > Keyboard > Text > Use smart quotes and dashes - and uncheck that; or,
  • In the TextEdit document or the Note, select all then > Edit > Substitutions > uncheck “Smart Quotes”; or,
  • In TextEdit > Preferences > uncheck “Smart Quotes” (I don’t see a preference like this in Notes)

If you start it with ``` then it does seem to retain all the indentation when you copy it out of Airtable, and when you paste it in. The indents don’t show when you click out of the formula, but once you click in, they are there.


#7

There is some sort of app-to-app handoff that seemingly happens in some cases and not in others; you may have to try several editors to find one――

Oh, I’ve got it, I think.

Whether the formula retains indentation and line breaks when copied and pasted from Airtable to an offline editor probably depends on how that editor interprets UNIX end-of-line conventions. (UNIX systems typically use the newline or linefeed character by itself to mark EOL, while Macs use the carriage return character; Windows systems typically use a two-character marker, a [carriage return] followed by a [linefeed].)

I know when I was writing documentation for Wardrobe Manager late last year, a cut-and-paste from Airtable directly into LibreWriter resulted in a block of undifferentiated type. Instead I had to copy from Airtable, paste into Notepad++, copy from Notepad ++, and, finally, paste into LibreWriter.