Skip to main content
Solved

If Statement with DATEADD

  • April 27, 2021
  • 7 replies
  • 61 views
L

I’m trying to write a simple IF statement in the CM Maturity Date column comparing the Policy Type field and outputting either a DATEADD function, if “Claims Made”, or a text string (“N/A”) if “Occurrence”:

This is the formula that I currently have in CM Maturity Date that results in the above screen cap:

IF({Policy Type} = ‘Claims Made’, DATEADD({Retro Date}, 5, ‘year’), “N/A”)

For the Claims Made policy type, it should have added 5 years to the retro date.

I can’t figure out what I’m doing wrong here, so I’d appreciate any help.

Thanks!

Best answer by ScottWorld

I fixed the quotes, which took care of the error, but I’m still only getting the N/A in every row, even for Claims Made policy types. It’s like it sees all the policy type entries as Occurrence.


You may want to check to make sure that the values that you typed into the “Policy Type” field don’t have any extra spaces in them (before, after, or in between the words). Airtable doesn’t actually visually show you extra spaces onscreen… you have to manually double-click into the fields to see the spaces.

7 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 27, 2021

Welcome to the community, @Lori_Hein!

Put “Claims Made” in double quotation marks, and use the word ‘years’ (in single quotation marks).

L
  • Lori_Hein
  • Author
  • New Participant
  • April 27, 2021

Welcome to the community, @Lori_Hein!

Put “Claims Made” in double quotation marks, and use the word ‘years’ (in single quotation marks).


Thanks, Scott!

It gave me an error:

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 27, 2021

Thanks, Scott!

It gave me an error:


Airtable only accepts straight quotes instead of stylized quotes, so adjust the single quotation marks that you have around the word ‘years’ so that it looks like this: 'years'. Same thing with the word “N/A”… it needs to look like this: "N/A".

L
  • Lori_Hein
  • Author
  • New Participant
  • April 27, 2021

Airtable only accepts straight quotes instead of stylized quotes, so adjust the single quotation marks that you have around the word ‘years’ so that it looks like this: 'years'. Same thing with the word “N/A”… it needs to look like this: "N/A".


I fixed the quotes, which took care of the error, but I’m still only getting the N/A in every row, even for Claims Made policy types. It’s like it sees all the policy type entries as Occurrence.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • April 27, 2021

I fixed the quotes, which took care of the error, but I’m still only getting the N/A in every row, even for Claims Made policy types. It’s like it sees all the policy type entries as Occurrence.


You may want to check to make sure that the values that you typed into the “Policy Type” field don’t have any extra spaces in them (before, after, or in between the words). Airtable doesn’t actually visually show you extra spaces onscreen… you have to manually double-click into the fields to see the spaces.

L
  • Lori_Hein
  • Author
  • New Participant
  • April 27, 2021

You may want to check to make sure that the values that you typed into the “Policy Type” field don’t have any extra spaces in them (before, after, or in between the words). Airtable doesn’t actually visually show you extra spaces onscreen… you have to manually double-click into the fields to see the spaces.


That was it; thanks so much!

I never would have thought of that, so I greatly appreciate your help.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 27, 2021

That was it; thanks so much!

I never would have thought of that, so I greatly appreciate your help.


@Lori_Hein You’re welcome! It’s my #1 pet peeve & biggest frustration with Airtable.

Please send an email to them at support@airtable.com to let them know how much this messed you up, and that you would really like them to fix this problem!

Airtable is trying to be a database product that doesn’t show you your actual data! :rofl: :joy: :grinning_face_with_sweat:

    Terms & ConditionsAccessibility statement