Skip to main content

Formula limit? airtable shortens my formula?

  • June 24, 2022
  • 4 replies
  • 54 views
E
Forum|alt.badge.img

is there a formula limit in airtable? i wish to compute for the payment rental reminder. could be every 2 months or every 3 months. but upon saving, airtable is shortening it to half of what i type. what’s happening?

IF(AND(FIND(“Bank Transfer Regular”, {Payment Term}), Status= “Active”),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),{Contract Start}< DATEADD({Contract Start}, 2, ‘month’)), DATETIME_FORMAT(DATEADD({Contract Start}, 2, ‘month’),‘MM-DD-YYYY’)),
SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 2, ‘month’), (TODAY()< DATEADD({Contract Start}, 4, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 4, ‘month’),‘MM-DD-YYYY’)),SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 4, ‘month’), (TODAY()< DATEADD({Contract Start}, 6, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 6, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 6, ‘month’), (TODAY()< DATEADD({Contract Start}, 8, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 8, ‘month’),‘MM-DD-YYYY’)), SWITCH(AND(FIND(“2 months advance”, {Payment Term}),TODAY()> DATEADD({Contract Start}, 8, ‘month’), (TODAY()< DATEADD({Contract Start}, 10, ‘month’))), DATETIME_FORMAT(DATEADD({Contract Start}, 10, ‘month’),‘MM-DD-YYYY’)))

4 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • June 24, 2022

Hey @East_Lima_Realty!

I’ve written a much longer formula than the one you provided and haven’t encountered the same issue.

I do, however, think I see what the problem is.
It seems that you’re trying to create a nested IF formula.

Here’s the exact same formula you posted but with a bit of formatting:

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        Status = “Active”
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            {Contract Start} < DATEADD({Contract Start}, 2, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},2,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 2, ‘month’),
            TODAY() < DATEADD({Contract Start}, 4, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},4,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 4, ‘month’),
            TODAY() < DATEADD({Contract Start}, 6, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},6,‘month’),
            ‘MM-DD-YYYY’)
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 6, ‘month’),
            TODAY()< DATEADD({Contract Start}, 8, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 8, ‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 8, ‘month’),
            TODAY()< DATEADD({Contract Start}, 10, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 10, ‘month’),‘MM-DD-YYYY’
        )
    )
)

The IF formula syntax is: IF(statement, a, b).
There’s a few errors, but the main reason for the formula failure is that you wrote the formula in this syntax: IF(condition, a, b, c, d, e).

Here’s a version of your formula that fits within the formula syntax.
I have also refactored the formula body to be a bit more efficient in its flow.

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        FIND(
            "2 months advance",
            {Payment Term}
        ),
        {Status} = “Active”
    ),
    IF(
        {Contract Start} < DATEADD({Contract Start}, 2, 'month'),
        DATETIME_FORMAT(
            DATEADD({Contract Start}),
            'MM-DD-YYYY'
        ),
        IF(
            AND(
                TODAY() > DATEADD({Contract Start}, 2, 'month'),
                TODAY() < DATEADD({Contract Start}, 4, 'month')
            ),
            DATETIME_FORMAT(
                DATEADD({Contract Start}, 4, 'month'),
                'MM-DD-YYYY'
            ),
            IF(
                AND(
                    TODAY() > DATEADD({Contract Start}, 4, 'month'),
                    TODAY() < DATEADD({Contract Start}, 6, 'month')
                ),
                DATETIME_FORMAT(
                    DATEADD({Contract Start}, 6, 'month'),
                    'MM-DD-YYYY'
                ),
                IF(
                    AND(
                        TODAY() > DATEADD({Contract Start}, 6, 'month'),
                        TODAY() < DATEADD({Contract Start}, 8, 'month')
                    ),
                    DATETIME_FORMAT(
                        DATEADD({Contract Start}, 8, 'month'),
                        'MM-DD-YYYY'
                    ),
                    IF(
                        AND(
                            TODAY() > DATEADD({Contract Start}, 8, 'month'),
                            TODAY() < DATEADD({Contract Start}, 10, 'month')
                        ),
                        DATETIME_FORMAT(
                            DATEADD({Contract Start}, 10, 'month'),
                            'MM-DD-YYYY'
                        )
                    )
                )
            )
        )
    )
)

You should be able to just copy and paste that into your formula field to see how it behaves.
I do recommend that you check to make sure that the formatting on your field names is correct, or else it will toss you an error.

Let me know if I’m misunderstanding something or if something breaks with the formula.
Definitely feel free to let me know if you have any questions!

Edited for clarity

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • June 24, 2022

Hey @East_Lima_Realty!

I’ve written a much longer formula than the one you provided and haven’t encountered the same issue.

I do, however, think I see what the problem is.
It seems that you’re trying to create a nested IF formula.

Here’s the exact same formula you posted but with a bit of formatting:

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        Status = “Active”
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            {Contract Start} < DATEADD({Contract Start}, 2, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},2,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 2, ‘month’),
            TODAY() < DATEADD({Contract Start}, 4, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},4,‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 4, ‘month’),
            TODAY() < DATEADD({Contract Start}, 6, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start},6,‘month’),
            ‘MM-DD-YYYY’)
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 6, ‘month’),
            TODAY()< DATEADD({Contract Start}, 8, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 8, ‘month’),
            ‘MM-DD-YYYY’
        )
    ),
    SWITCH(
        AND(
            FIND(
                “2 months advance”,
                {Payment Term}
            ),
            TODAY() > DATEADD({Contract Start}, 8, ‘month’),
            TODAY()< DATEADD({Contract Start}, 10, ‘month’)
        ),
        DATETIME_FORMAT(
            DATEADD({Contract Start}, 10, ‘month’),‘MM-DD-YYYY’
        )
    )
)

The IF formula syntax is: IF(statement, a, b).
There’s a few errors, but the main reason for the formula failure is that you wrote the formula in this syntax: IF(condition, a, b, c, d, e).

Here’s a version of your formula that fits within the formula syntax.
I have also refactored the formula body to be a bit more efficient in its flow.

IF(
    AND(
        FIND(
            “Bank Transfer Regular”,
            {Payment Term}
        ),
        FIND(
            "2 months advance",
            {Payment Term}
        ),
        {Status} = “Active”
    ),
    IF(
        {Contract Start} < DATEADD({Contract Start}, 2, 'month'),
        DATETIME_FORMAT(
            DATEADD({Contract Start}),
            'MM-DD-YYYY'
        ),
        IF(
            AND(
                TODAY() > DATEADD({Contract Start}, 2, 'month'),
                TODAY() < DATEADD({Contract Start}, 4, 'month')
            ),
            DATETIME_FORMAT(
                DATEADD({Contract Start}, 4, 'month'),
                'MM-DD-YYYY'
            ),
            IF(
                AND(
                    TODAY() > DATEADD({Contract Start}, 4, 'month'),
                    TODAY() < DATEADD({Contract Start}, 6, 'month')
                ),
                DATETIME_FORMAT(
                    DATEADD({Contract Start}, 6, 'month'),
                    'MM-DD-YYYY'
                ),
                IF(
                    AND(
                        TODAY() > DATEADD({Contract Start}, 6, 'month'),
                        TODAY() < DATEADD({Contract Start}, 8, 'month')
                    ),
                    DATETIME_FORMAT(
                        DATEADD({Contract Start}, 8, 'month'),
                        'MM-DD-YYYY'
                    ),
                    IF(
                        AND(
                            TODAY() > DATEADD({Contract Start}, 8, 'month'),
                            TODAY() < DATEADD({Contract Start}, 10, 'month')
                        ),
                        DATETIME_FORMAT(
                            DATEADD({Contract Start}, 10, 'month'),
                            'MM-DD-YYYY'
                        )
                    )
                )
            )
        )
    )
)

You should be able to just copy and paste that into your formula field to see how it behaves.
I do recommend that you check to make sure that the formatting on your field names is correct, or else it will toss you an error.

Let me know if I’m misunderstanding something or if something breaks with the formula.
Definitely feel free to let me know if you have any questions!

Edited for clarity


The version of the formula that I see has curly quotes in places that need straight quotes.

I haven’t actually read the formula, and it seems to have a lot going on. (It looks inside-out to me.) I just wanted to give this warning.

Ben_Young1
Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • June 24, 2022

The version of the formula that I see has curly quotes in places that need straight quotes.

I haven’t actually read the formula, and it seems to have a lot going on. (It looks inside-out to me.) I just wanted to give this warning.


Yeah… the formula is relatively dense.
I spent a bit too long last night pulling it apart and cleaning it.

@East_Lima_Realty - Kuovonne is correct. I tend to get lazy in my formulas and code when it comes to my quotes; keep an eye out for them if you try to use the formula.

Alexey_Gusev
Forum|alt.badge.img+25
IF(AND(
      FIND("Bank Transfer Regular",{Payment Term}),
      FIND("2 months advance",{Payment Term}),
      {Status} = "Active",
      {Contract Start},
      IS_AFTER(TODAY(),{Contract Start})
    ),

DATETIME_FORMAT(
DATEADD({Contract Start},2+2*INT(DATETIME_DIFF(TODAY(),{Contract Start},'months')/2),'months'),'MM-DD-YYYY')
)
Terms & ConditionsAccessibility statement