Formula To Flag Record (This Month, Previous Month, Other)

[Jason_Burke1]

I have a column called “Date”. I’d like another column to look at my date column and if the date is in the same month as TODAY(), then it should say “This Month”. If the date is last month when compared to TODAY(), then it should say “Previous Month”. To be clear, I don’t want it to look at the previous 30 days. So, if a record has a date of 06/30/20 and today is 07/01/20 then that record would say “Previous Month”, but its month is in a previous month when compared to TODAY(). Does that make sense?

[ScottWorld]

The MONTH function will return the month number of a date.

So really, you just need to compare the month number of today with the month number of the other date.

Perhaps by doing a subtraction function.

The tricky part is that you can’t just check to see if today’s month number is 1 greater than last month’s, because January would actually be 11 numbers LESS than December.

So I think that something like this formula would probably work:

SWITCH(
MONTH(TODAY()) - MONTH(Birthdate),
0, "This Month",
1, "Previous Month",
-11, "Previous Month",
"Other"
)

Although I just realized that this formula doesn’t account for different years, so there is definitely more work to be done here!! But this should be a good starting point, at least.

I actually need to go to sleep right now, but hopefully someone else can chime in to create a formula that accounts for handling different years! You’ll probably need a nested IF statement to check for the difference in years, then the difference in months after that.

Lol, this is why I shouldn’t be writing formulas late at night! Because I forget about years altogether!

[Jason_Burke1]

Haha! No problem Scott!

[JonathanBowen]

@Jason_Burke1, @ScottWorld - I’m sure there’s a few different ways to approach this, but here’s one that gets over the issue Scott identified:

I have two columns that show the year/month as an integer value. The formulas are:

VALUE(DATETIME_FORMAT(Date, 'YYYYMM'))

and

VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMM'))

So we can compare the “value” of today with any arbitrary date. The final formula is this:

IF(
  {Month of Date} - {Month Today} = 0,
  'this month',
  IF(
    {Month of Date} - {Month Today} = -1,
    'last month',
    IF(
      {Month of Date} - {Month Today} < -1,
      'past month',
      'future month'
    )
  )
)

[ScottWorld]

Thanks so much for figuring this out, @JonathanBowen!! :slightly_smiling_face:

[ScottWorld]

@JonathanBowen Actually, it looks like your formula won’t work if you’re comparing December of one year to January of the next year. For example, December 2019 to January 2020. It won’t yield “last month”, it will only yield “past month”. This was my dilemma, too… comparing a sequential December and January.

To trap for that scenario, we would need to compare the month of date #1 to the month of date #2, along with the year of date #1 to the year of date #2.

[JonathanBowen]

@ScottWorld ah, yes, you are right, because both the month and the year increment, so the difference is more than 1.

I need to have a think :thinking:

[ScottWorld]

I don’t know if this is the most elegant way to handle it, but we could just tack on an extra nested IF statement at the beginning of the formula.

We could use an AND function to see if all of the following are true:

• Month of current date is “1”
• Month of the previous date is “12”
• Year of current date - 1 = Year of previous date

If so, then the formula would result in “Last Month”.

[Justin_Barrett · Accepted solution]

Here’s what I came up with. This was fun to play with, but as has already been discussed, it was tricky covering all possible options.

IF(
    Date,
    IF(
        Date < TODAY(),
        IF(
            MONTH(Date) < MONTH(TODAY()),
            IF(
                MONTH(TODAY()) - MONTH(Date) = 1,
                "Previous",
                "Past"
            ),
            IF(
                MONTH(Date) > MONTH(TODAY()),
                IF(
                    (MONTH(TODAY()) + 12) - MONTH(Date) = 1,
                    "Previous",
                    "Past"
                ),
                "This"
            )
        ),
        IF(
            Date > TODAY(),
            IF(
                MONTH(Date) > MONTH(TODAY()),
                "Future",
                IF(
                    YEAR(Date) > YEAR(TODAY()),
                    "Future",
                    "This"
                )
            ),
            "This"
        )
    ) & " Month"
)

[ScottWorld]

Really nice solution, @Justin_Barrett!! :smiling_face_with_sunglasses: :raised_hands: :thumbs_up: