Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 01, 2020 08:26 PM
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?
Solved! Go to Solution.
Jul 02, 2020 10:20 PM
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"
)
Jul 01, 2020 09:58 PM
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!
Jul 02, 2020 12:12 AM
Haha! No problem Scott!
Jul 02, 2020 02:12 AM
@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'
)
)
)
Jul 02, 2020 07:41 AM
Thanks so much for figuring this out, @JonathanBowen!! :slightly_smiling_face:
Jul 02, 2020 09:54 AM
@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.
Jul 02, 2020 01:05 PM
@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:
Jul 02, 2020 01:20 PM
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:
If so, then the formula would result in “Last Month”.
Jul 02, 2020 10:20 PM
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"
)
Jul 03, 2020 01:05 AM
Really nice solution, @Justin_Barrett!! :smiling_face_with_sunglasses: :raised_hands: :thumbs_up: