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?
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!
@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.
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.
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!