Formula IF statement with DATEADD based on single select value

[Willo_Co_Suppor]

Hi there,

I have a table with a single select field type called PROGRAM TYPE.
It contains 3 different program types, which I’ll just call PROGRAM A, PROGRAM B, PROGRAM C.

I also have a Date Joined field, and I’m creating a new date field called PROGRAM EXPIRES.
PROGRAM A expires 12 months after its ‘Date Joined’ date, and PROGRAM B expires 3 months after its ‘Date Joined’ date. PROGRAM C does not need an expiration date and can be an empty string, or by default be set to 12 months as well.

When I try:

IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,‘months’), DATEADD({Date Joined}, 3,‘months’))

I get an error.

I’ve also tried:

IF({PROGRAM TYPE}="PROGRAM A", DATEADD({Date Joined}, 12,'months'), IF({PROGRAM TYPE}="PROGRAM B",DATEADD({Date Joined}, 3,‘months'), IF({PROGRAM TYPE}="PROGRAM C", DATEADD({Date Joined}, 12,'months')))

And still get an error. I’ve made sure all spaces/quotations are correct based on other suggestions in this forum. Would greatly appreciate any help with this!

[Kamille_Parks]

Its possible the errors come from some of your quote marks being “curly”. Syntax-wise, your formulas are fine. Its also possible you’re getting an error when the {Date Joined} field is empty.

You can try this formula:

IF(
   AND({PROGRAM TYPE}, {Date Joined}),
   SWITCH(
      {PROGRAM TYPE},
      "PROGRAM A", DATEADD({Date Joined}, "12", "months"),
      "PROGRAM B", DATEADD({Date Joined}, "3", "months"),
   )
)

[Willo_Co_Suppor]

Thanks for the response! Unfortunately, I am getting an “invalid formula” response with this switch statement as well. The {Date Joined} field is not empty in any of the cells, so that is not the issue.

[Kamille_Parks]

Strange. Can you post a screenshot?

[Raminder_Singh]

Kamille_Parks:

IF(
   AND({PROGRAM TYPE}, {Date Joined}),
   SWITCH(
      {PROGRAM TYPE},
      "PROGRAM A", DATEADD({Date Joined}, "12", "months"),
      "PROGRAM B", DATEADD({Date Joined}, "3", "months"),
   )
)

@Kamille_Parks, @Willo_Co_Support there is a stray comma in the third line of the SWITCH call. Try this:

IF(
   AND({PROGRAM TYPE}, {Date Joined}),
   SWITCH(
      {PROGRAM TYPE},
      "PROGRAM A", DATEADD({Date Joined}, "12", "months"),
      "PROGRAM B", DATEADD({Date Joined}, "3", "months")
   )
)

PS - I’m working on a tool that catches these kinds of errors in Airtable formulas. Still very early in development but my tool definitely found the error in this formula. Are you guys interested in such a tool?

[Kamille_Parks]

Raminder_Singh:

Are you guys interested in such a tool?

Absolutely. Is it an Airtable app or something that runs outside of Airtable?

[kuovonne]

Raminder_Singh:

I’m working on a tool that catches these kinds of errors in Airtable formulas.

This could be very useful. Airtable formulas have a unique grammar.

You may also be interested in this tool.

[Raminder_Singh]

Well, how it will be packaged is still undecided as the code is in very early stages of development. I will prefer making the user experience as frictionless as possible such that people don’t have to drastically alter their workflows to use my tool. But how it will turn out remains to be seen.

I am still working on the parts that will do the heavy lifting of formatting/syntax highlighting and other IDE like features we developers have come to expect as standard.

[Raminder_Singh]

kuovonne:

Airtable formulas have a unique grammar.

Yes, and that is indeed one of the harder parts – gleaning information about the grammar by trying out hundreds of formulas in a table.

kuovonne:

You may also be interested in this tool .

Orc is indeed a helpful tool but what I am planning to do is much more than just pretty printing. Some of the planned features are:

• Syntax highlighting
• Pretty printing/formatting
• Reporting errors – why is your formula not being accepted by the Airtable formula editor
• Autocompletion
• Suggesting fixes for common problems – e.g. using curly quotes instead of regular ones

And as a bit of a teaser, the syntax highlighting as seen in my terminal: