Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How can I adjust this formula to not have the ERROR message appear when the field is blank

11723 19
cancel
Showing results for 
Search instead for 
Did you mean: 
Charlotte_Barne
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! Below is the formula I’m using

DATEADD({Fast Action $7K Paid},30,‘days’)

I want to prevent the “#ERROR!” message from appearing when the “Fast Action $7K Paid” box is left empty. This field won’t always be used and the error message constantly showing is a tad frustrating. What do I need to add to the above formula to ensure that when nothing is put in the “Fast Action $7K Paid” then the error message won’t show in the next box?

Thank you for any and all help!

19 Replies 19

I see it broke the way it handles the blank
image.png

I could have sworn I put it in just like that! Thank you so much Michael!

Sorry, need to move the DATETIME_FORMAT to surround just the DATEADD portion so you still get the correct error handling
image.png

Was just coming back to ask this :slightly_smiling_face:

All is now well! Thank you thank you!

Tony_Lewis
4 - Data Explorer
4 - Data Explorer

I had the same issue and, after emailing support, came up with a slightly different formula:

IF({Construction Start Date},(DATEADD({Construction Start Date},-33,‘day’)))

The difference between this and the other solution in this thread is that if instead of #ERROR, the field will just show blank if the “Construction Start Date” hasn’t been entered.

J_W1
6 - Interface Innovator
6 - Interface Innovator

Struggling to make these solutions work for me.
In my case if a field referenced by the formula is itself an #ERROR!, i’d like the concurrent formula referencing to it to output as BLANK().

Used this:
IF({Year} = “#ERROR!”, BLANK(), DATETIME_PARSE(CONCATENATE({Year}, “-“, {MONTH(1-12)},”-“,{Day(1-31})))

But still gives me an #ERROR! when the {Year} is also an #ERROR!

#feature request?

  • Wouldn’t it be great if Airtable would give us error handling options with formula’s? Eg. provide the option to choose how an #ERROR! is shown in de fields: null or #ERROR!
    OR
  • If we could filter on #ERROR! values, just like we can filter on Empty values)

You could use ISERROR({Year}) to test if the {Year} formula result is an error. However, a simpler formula only calculates the date if there is a year.


IF(
    {Year},
    DATETIME_PARSE( 
        {Year} & "-" & {MONTH(1-12)}  & "-" & {Day(1-31},
        "YYYY-M-D"
    )
)


By the way, I copied the field names from your post, but it looks like there might be a missing closing parentheses in you {Day(1-32} field name.

J_W1
6 - Interface Innovator
6 - Interface Innovator

Did the job perfectly. Thanks for bringing iserror() and how to use it to my attention. Quite useful.

That’s the best solution for the problem. Thanks, @Tony_Lewis!