Skip to main content

Problems with OR and Dates

  • June 28, 2019
  • 2 replies
  • 21 views
M

Hi there,
I do have two date columns {Created Date} and {Last Substantial Contact}. I like to check first if the date in the {Created Date} field is older than 365 days. If it is not then I like the formula to output “covered”, if it is older than 365 days I like to check if {Last Substantial Contact} is less than 365 days. If this is the case then the field should also read covered. If {Last Substantial Contac} is older than 365 then the field should give the output “uncovered”. This is how I tried to implement the above logic:

IF(OR(DATETIME_DIFF(TODAY(),{Created Date},‘days’)<365), (DATETIME_DIFF(TODAY(),{Last Substantial Contact},‘days’)<365), “Covered”, "Uncovered”))

I tried several variations but always get “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.”

Does anybody see where the error in my formula is?

Thanks,
Marc

2 replies

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • June 29, 2019

Missing, extraneous, or mismatched parentheses, depending on whether you fix it by adding one or taking one away.

The following should parse as a valid formula, but I didn’t step through it to make sure it does what you want.

IF(
    OR(
        DATETIME_DIFF(
            TODAY(),
            {Created Date},
            'days'
            )<365,
        DATETIME_DIFF(
            TODAY(),
            {Last Substantial Contact},
            'days'
            )<365
        ),
    "Covered",
    "Uncovered"
    )

(You either needed to add ‘(’ before the first DATETIME_DIFF() or remove the one after the first 365, and you needed to remove one of the two closing ')'s.)

You can copy-and-paste that formula, line-breaks and indentations included, into the formula field, and Airtable will parse it properly. When writing complex formulas, it can be helpful to write them in a third-party editor — personally, I use Notepad++, because it makes matching parentheses easy — but there are hundreds of others to chose from. If you enable the option to translate tabs into spaces most such editors support, you’ll find you can copy-and-paste both to and from Airtable’s formula configuration fields and have the spacing and indentation retained. (Airtable will collapse whitespace when you save the formula, but it’s stored internally and will be restored when you copy-and-paste back into the editor.)

JonathanBowen
Justin_Barrett
M
  • Marc_Maurer
  • Author
  • New Participant
  • June 29, 2019

Missing, extraneous, or mismatched parentheses, depending on whether you fix it by adding one or taking one away.

The following should parse as a valid formula, but I didn’t step through it to make sure it does what you want.

IF(
    OR(
        DATETIME_DIFF(
            TODAY(),
            {Created Date},
            'days'
            )<365,
        DATETIME_DIFF(
            TODAY(),
            {Last Substantial Contact},
            'days'
            )<365
        ),
    "Covered",
    "Uncovered"
    )

(You either needed to add ‘(’ before the first DATETIME_DIFF() or remove the one after the first 365, and you needed to remove one of the two closing ')'s.)

You can copy-and-paste that formula, line-breaks and indentations included, into the formula field, and Airtable will parse it properly. When writing complex formulas, it can be helpful to write them in a third-party editor — personally, I use Notepad++, because it makes matching parentheses easy — but there are hundreds of others to chose from. If you enable the option to translate tabs into spaces most such editors support, you’ll find you can copy-and-paste both to and from Airtable’s formula configuration fields and have the spacing and indentation retained. (Airtable will collapse whitespace when you save the formula, but it’s stored internally and will be restored when you copy-and-paste back into the editor.)


Wow V_WAM_Hall thanks a lot. This really works, awesome!

And the tips with the editor are spot on… fully agree on this as well.

Marc

Terms & ConditionsAccessibility statement