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?

[Jason_Burke1]

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.

[Jason_Burke1]

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!

[Bob_Rutledge]

Don't work.

[itatvue]

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