How to Remove #Error Results from Formula

[Rachel_Mendoza]

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”)

[kuovonne]

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?

[Rachel_Mendoza]

Thanks for reaching out!

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.

[kuovonne]

Rachel_Mendoza:

I always get an error if the purchase close date field isn’t filled in.

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")
)