3 month interval formula

[Blake_Ramage]

I want my formula to return 1 if it is a 3-month interval after a particular month contained in another field.

So say the given field has “March” in it. I want the formula to return 1 if the current month is 3 months, 6 months, 9 months, or 12 months after March. Using this example, it would return 1 if the current month was June, September, December, or March. Or be different if the original month was a different month and a 3-month interval also.

What formula would I use for this?

[Justin_Barrett]

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

[Blake_Ramage]

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).

[Justin_Barrett · Accepted solution]

Happy to help! Because you’re using an actual date, you can do the same thing that was done to NOW() to force that date to be the first of the listed month. To implement that part, change…

        DATETIME_PARSE(Month, "MMMM"),

…to…

        DATETIME_PARSE(
            MONTH({Start Date}) & "/1/" & YEAR({Start Date}),
            "M/D/YYYY"
        ),

(Change the {Start Date} references to your actual field name, of course.)

Even though your timezone formats dates differently, Airtable can still parse dates written in a different format because of the formatting string. However, you can change “M/D/YYYY” to “D/M/YYYY” if you want, but you’ll also have to change the order of the pieces being concatenated for the parsing operations.

To add the other conditions, we’ll wrap the revised formula inside a couple of IF() functions. Here’s the new final result:

IF(
    Pet = "Dog",
    IF(
        {Pet age} = "6-12 months",
        1,
        MOD(
            DATETIME_DIFF(
                DATETIME_PARSE(
                    MONTH({Start Date}) & "/1/" & YEAR({Start Date}),
                    "M/D/YYYY"
                ),
                DATETIME_PARSE(
                    MONTH(NOW()) & "/1/" & YEAR(NOW()),
                    "M/D/YYYY"
                ),
                "months"
            ), 3
        ) = 0
    ), 0
)

[Blake_Ramage]

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