Help

Re: How to Remove #Error Results from Formula

1172 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Rachel_Mendoza
4 - Data Explorer
4 - Data Explorer

Hello. I am having issue with my formula returning errors instead of 0. I tried using adding If to the beginning of the formula and also the ISERROR but nothing is working the way I need it to. My formula is returning 1 if Purchase Close Date was last month. Below is what the formula currently looks like. Thanks in advance for any help!

DATETIME_FORMAT({Purchase Close Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), “month”, -1), “YYYY-MM”)

4 Replies 4

Welcome to the Airtable community!

This sounds frustrating.

Can you provide a screen capture showing the {Purchase Close Date} field and your formula field?

Is your {Purchase Close Date} a lookup field? (Using lookup values in formulas can be tricky.) If so, can you convert it to a roll up field?

Do you always get an error or only sometimes?

Thanks for reaching out!

Purchased Previous Month Column Error

I always get an error if the purchase close date field isn’t filled in. The [Purchase Close Date] field is a regular date field.

Thanks for this detail. This very common issue is discussed in this support article.

The fix is to wrap the formula in an IF statement that checks that the date field has a value.

IF( 
  {Purchase Close Date},
  DATETIME_FORMAT({Purchase Close Date}, "YYYY-MM") = DATETIME_FORMAT(DATEADD(NOW(), "month", -1), "YYYY-MM")
)
Heather_Hale
7 - App Architect
7 - App Architect

I am having a similar problem.

I am SO CLOSE - but not quite there yet!

I have a list of actors, authors and other people with Dates of Birth and a few Dates of Death.

I want to know their ages.

If they have passed, I want to limit that calculation to show their age at death (instead of how old they'd be today).

I have all this working fine BUT if they are missing a DOB and/or DOD (not always known), I get an #Error result in the "Age" (or Age at Death) column.

I know I am just one step away from solving this. I have done everything under the sun (except, of course 😉 the solution!) I can get it working one way or the other - but not working across all the columns without that #ERROR code in at least one column.

Do I need to do a Pivot Table? 

Or am I just missing an extra "IF("?

Here's the formula I'm working with at the moment:

 

IF({DOD}=BLANK(),
DATETIME_DIFF(TODAY(), {DOB}, 'years') & ' years ' & MOD(DATETIME_DIFF(TODAY(), {DOB}, 'months'), 12) & ' months',
DATETIME_DIFF( {DOD}, {DOB}, 'years') & ' years ' & MOD(DATETIME_DIFF({DOD}, {DOB}, 'months'), 12) & ' months')
 
##

 Any ideas?

THANK YOU!