3 month interval formula

Welcome to the community, @Blake_Ramage! :grinning_face_with_big_eyes: Let’s break down the problem.

First, we’ll assume that the {Month} field you’re pulling from (guessing the name) has the full month spelled out; e.g. “March” not “Mar”. Your example indicated this, but I just want to confirm that this is the case. It’s only a slight tweak to the formula if you’re using abbreviations. Also, I got the impression that this {Month} field only contains the name of the desired month. If it’s an actual date field, the final formula will need some tweaks.

We can get a date representing the first of that month using this formula:

DATETIME_PARSE(Month, "MMMM")

Using March as an example, that will output a datetime representing March 1 of the current year.

Now we need to get a datetime representing the first day of the current month. This can be done by extracting the month and year from the datetime returned by the NOW() function (NOTE: this will shift when the date changes in GMT, not your local timezone; one of the quirks of Airtable’s date calculation system), building a string representing the first of that month, and then parsing the result:

DATETIME_PARSE(MONTH(NOW()) & "/1/" & YEAR(NOW()), "M/D/YYYY")

The difference between those two dates will tell us how many months apart they are (splitting the formula across multiple lines for clarity:

DATETIME_DIFF(
    DATETIME_PARSE(Month, "MMMM"),
    DATETIME_PARSE(
        MONTH(NOW()) & "/1/" & YEAR(NOW()),
        "M/D/YYYY"
    ),
    "months"
)

To see if that difference is an exact multiple of 3 months, we can feed that difference into the MOD() function with a divisor value of 3. If the output from that function is 0, it’s an exact multiple of 3. This will work whether the current month is before or after the listed month.

MOD(
    DATETIME_DIFF(
        DATETIME_PARSE(Month, "MMMM"),
        DATETIME_PARSE(
            MONTH(NOW()) & "/1/" & YEAR(NOW()),
            "M/D/YYYY"
        ),
        "months"
    ), 3
)

Here’s my test table using this example:

Checking to see if the MOD() function output equals 0 will return a 1 when that’s true, and a 0 otherwise. Here’s the final formula and its output:

MOD(
    DATETIME_DIFF(
        DATETIME_PARSE(Month, "MMMM"),
        DATETIME_PARSE(
            MONTH(NOW()) & "/1/" & YEAR(NOW()),
            "M/D/YYYY"
        ),
        "months"
    ), 3
) = 0

This is incredible help - thank you so much!!! Brilliant to get such a good reply and hugely appreciated.

I have a few other things to add in if you don’t mind helping a little bit further:

The month is taken from a start date (which is in a date field) but I’m having trouble formatting this outside of the typical format, in my timezone we use DD/MM/YYYY. Is it possible just to get the MM part of this - the other is irrelevant for my purposes, or just use the MM part for the above calculation?

Then two more conditions:

• It should only return a 1 according to the above if the “Pet” field is “Dog” (if “Pet” field is “Cat” should always return 0)
• It should return a 1 always if “Pet age” is “6-12 months” (if pet age isn’t this then it should use the formula above).

I’m incredibly grateful for your help Justin. Absolutely legend!