Error in cells where date is missing in formula

[Lindsey_Garvey]

Hi there,

I know this question has been asked previously, and I realise there’s resources to give an idea, I’m just new to Airtable formulas, and still can’t make it work for me.

My question is super simple. I have a ‘date’ column, and then a column with formula to add 30 days. It then produces an error in all of the cells where the ‘date’ column hasn’t been populated. I realise I need to use ‘IF’… but struggle from there. I’ve tested out examples that I’ve seen, but no luck.

Thank you!

[kuovonne]

Airtable Support

Common formula errors and how to fix them

When building formulas or using one of Airtable's computed fields, you may run across an error. These error codes include #ERROR, invalid formula, NaN, circular reference, and infinity. Below are m...

[Carl]

You can do something like,

IF(date=blank(),“”,[your +30 formula])

e.g.

IF(
  date=BLANK(),
  "",
  DATETIME_FORMAT(
    DATEADD(
    date,
    30,
    'day'
  ),
  'MM-DD-YYYY'
  )
)

[kuovonne]

Your formula could be simpler.

IF(
    {date},
    DATEADD( {date}, 30, 'days')
)

[Carl]

Good point. I had to use BLANK() for something that wouldn’t work like that, and it’s just became a default of mine. But when I did the DATEADD without formatting, it did not keep the {date} formatting.

[kuovonne]

Carl:

But when I did the DATEADD without formatting, it did not keep the {date} formatting.

If you use DATEADD() by itself, you get a date object. If the only thing the formula returns is a date object, then you set formatting in the formula formatting options. If you want to combine the date with other text, or if the formula could return text, that’s when you use DATETIME_FORMAT().