Skip to main content

I’m having an issue catching an error return in my formula field. The field is called Status and in it, I check if a task is complete (i.e., a check mark in the Completed Field) or if it is late or coming due based on the Due Date.


If it is not complete and I haven’t assigned a due date yet, I’d like to have Status return blank.


In other words:

Task completed => Status: Complete

Task overdue => Status: Late

Task due in 2 days => Status: Coming Due

Task has no due date => Status: Blank

Otherwise (i.e., Task has due date that is not within 2 days of due date and isn’t complete) => Status: In Progress


My formula for Status is as follows:

IF({Completed?}=1,“Completed”,IF(IS_AFTER(TODAY(),{Due Date}),“Late”,IF(IS_AFTER(DATEADD(TODAY(), 2, ‘days’),{Due Date}),“Coming Due”,IF({Due Date} = BLANK(),"",“In Progress”)))


However, whenever I have a date that is blank, I get #ERROR! instead of a blank. I tried to error catch by wrapping this around an “IF(ISERROR({Due Date} = BLANK()” but it still returns an error.

Two possibilities:



  1. Your formula, as given, is short one closing parenthesis (')'). However, as that would give you a formula parsing error (that is, Airtable would complain when you tried to save the formula), I suspect it was merely dropped when you entered it here. Instead, I suspect it’s

  2. Try IF({Due Date}='' rather than BLANK(). BLANK() and '' seem interchangeable in some places but not so in others. To date, I’ve not figured out when and why, but using one when the other fails often seems to work.

Thanks - I still couldn’t get it to work with your suggestions. However, I wrapped the whole formula with an IF(ISERROR({original formula}),"",{original formula)), which is kind of ugly, but it works.

Thanks - I still couldn’t get it to work with your suggestions. However, I wrapped the whole formula with an IF(ISERROR({original formula}),"",{original formula)), which is kind of ugly, but it works.


Oh, I’m sorry; I completely missed this first time through.


The ERROR() message is coming from the first test against {Due Date}, when you check to see if TODAY() is after {Due Date}; you can’t have a blank value there. You simply need to reorder your IF() statements to test for an empty {Due Date} first, like so:


IF(

    {Due Date}=BLANK(),

    '',

    IF(

        {Completed?},

        'Completed',

        IF(

            IS_AFTER(TODAY(),{Due Date}),

            'Late',

            IF(

                IS_AFTER(DATEADD(TODAY(),2,'days'),{Due Date}),

                'Coming Due',

                'In Progress'

                )

            )

        )

    )

(And, yes, you can paste this indented code into the Formula field and have it work; in fact, I copied it from a Formula field. I typically write all but the simplest of formulas in Notepad++ , taking advantage of its automatic indentation and parenthesis matching, and afterwards copy-and-pasting them into Airtable. When I modify a formula, it goes through the process in reverse: While Airtable collapses the indentation on saving the formula, when the formula is opened for editing, the indentation is restored. So I mark-and-copy from the Formula field (Ctrl-A Ctrl-C) and paste it into Notepad++.)

Oh, I’m sorry; I completely missed this first time through.


The ERROR() message is coming from the first test against {Due Date}, when you check to see if TODAY() is after {Due Date}; you can’t have a blank value there. You simply need to reorder your IF() statements to test for an empty {Due Date} first, like so:


IF(

    {Due Date}=BLANK(),

    '',

    IF(

        {Completed?},

        'Completed',

        IF(

            IS_AFTER(TODAY(),{Due Date}),

            'Late',

            IF(

                IS_AFTER(DATEADD(TODAY(),2,'days'),{Due Date}),

                'Coming Due',

                'In Progress'

                )

            )

        )

    )

(And, yes, you can paste this indented code into the Formula field and have it work; in fact, I copied it from a Formula field. I typically write all but the simplest of formulas in Notepad++ , taking advantage of its automatic indentation and parenthesis matching, and afterwards copy-and-pasting them into Airtable. When I modify a formula, it goes through the process in reverse: While Airtable collapses the indentation on saving the formula, when the formula is opened for editing, the indentation is restored. So I mark-and-copy from the Formula field (Ctrl-A Ctrl-C) and paste it into Notepad++.)


Makes sense - thanks!

Reply


Terms & Conditions