Help

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

3904 4
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
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

IF({Fast Action $7K Paid}=BLANK(),“Error Will Robinson”, DATEADD({Fast Action $7K Paid},30,‘days’))

So that looks right and THANK YOU! But I’m now getting the message that that formula doesn’t work

you have to re-type the single quotes(maybe the double as well), they get formatted as a different but similar character in the forum. (copy and paste from the forum doesn’t work as we’d like)

Charlotte_Barne
5 - Automation Enthusiast
5 - Automation Enthusiast

That did it! Thank you Michael!

Charlotte_Barne
5 - Automation Enthusiast
5 - Automation Enthusiast

One more question Michael as this adjustment has now caused another error. How do I adjust DATEADD to only be the date and not the time. I don’t have the option to edit it using formatting with this formula for some reason.

have a look at this

5f73751092c6afb3485d0dfe997b3809227f5002.png

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

Thanks, I’ll have to take a deeper look at it as the adjustments I made from that support article didn’t work.

put your formula into: DATETIME_FORMAT( your formula, ‘M-D-YYYY’)

That unfortunately didn’t work for me either.

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!