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
Formula To Flag Record (This Month, Previous Month, Other)
Best answer by Justin_Barrett
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"
)
+35The 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!
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!
Haha! No problem Scott!
+18@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'
)
)
)
@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'
)
)
)
Thanks so much for figuring this out, @JonathanBowen!! :slightly_smiling_face:
+35@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'
)
)
)
@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.
+18@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.
@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:
+35@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:
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”.
+21Here’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"
)
+35Here’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"
)
Really nice solution, @Justin_Barrett!! :smiling_face_with_sunglasses: :raised_hands: :thumbs_up:
Hi Everyone! I finally have some free time to check in on this post (it’s been a busy week) and I can see that I’ve missed a lot! Thank you all who have contributed. I’m going to read through it, test our some solutions, and comment as I go.
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"
)
Ok, I feel like the biggest idiot.
When I saw your formula earlier today - I copied and pasted it right now and couldn’t get it to work. So then I said, “look at what the formula is doing and try to recreate it”, and of course that was a nightmare.
I just now realized, I didn’t copy the entire formula. The gray box the formula sits inside of, gives me no indication there’s more hidden text. It was only when I copied it again, and my cursor just to happen to scroll past the bottom did a slider bar on the right side appear and then I saw the rest of your code. OMG! haha!
Thank you for all your help! It’s working beautifully!
+3Here’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"
)
Don't work.
+2Thanks to everyone for the useful info in this thread. I know there are many ways to solve this problem. Here's what I came up with and it works for my application. In the example below, we are comparing today's date to a date field named "Close Date":
IF(
AND(MONTH(NOW()) = MONTH({Close Date}),YEAR(NOW()) = YEAR({Close Date})),
"This Month",
IF(
MONTH({Close Date}) & YEAR({Close Date}) = MONTH(DATEADD(TODAY(),-1,'month')) & YEAR(DATEADD(TODAY(),-1,'month')),
"Last Month",
IF(
MONTH({Close Date}) & YEAR({Close Date}) = MONTH(DATEADD(TODAY(),1,'month')) & YEAR(DATEADD(TODAY(),1,'month')),
"Next Month",
IF({Close Date} < TODAY(),
"Past Month",
"Future Month")
)
)
)I hope this is helpful to someone.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.