Formula to check multiple months

[Larcen60]

Hi everyone,

I'm looking to set up conditional formatting to highlight a cell if the month does not fall in one of the 4 quarters of the year (January, April, July, October). So if someone enters "02/01/19" it would highlight the cell because it's not one of the 4 quarter months.

I've tried =MONTH(A1) <> 1 and that works for one month but I'm having trouble figuring out how to make it check all 4 months. Does anyone know how to check all 4 months? Thanks very much!!

 

[jtakw]

Hi,

Use this as your CF formula:

=AND(MONTH(K2)<>1,MONTH(K2)<>4,MONTH(K2)<>7,MONTH(K2)<>10)

Change cell reference as needed.

 

[DRSteele]

Or perhaps

=SUM(--(MONTH(A1)={1;4;7;10}))=0

 

[jtakw]

Don't think you can use {} in CF formulas, otherwise I would've done that with my AND version like this: =AND(MONTH(K2)<>{1,4,7,10}) But again, this does Not work for CF.

 

[Larcen60]

Jtakw is correct, I get an error saying I can't use reference operators when I use the Sum formula for conditional formatting. When I put it into the spreadsheet it works great though.

Unfortunately, I get an error when I try to use the =And code that Jtakw provided.

Is there any other options you can think of? Thanks so much for the help!

 

[jtakw]

What error are you getting?

Works for me...:

Book1
	K	L
2	2/1/2019	TRUE
Sheet301

Cell Formulas
Range	Formula
L2	=AND(MONTH(K2)<>1,MONTH(K2)<>4,MONTH(K2)<>7,MONTH(K2)<>10)

Edit: Very likely, if you're getting Error, your Dates are Not real dates, and are Text.

 

[DRSteele]

Oopth. My mistake. Sorry about that.

This one is more fun anyway.

=MOD(MONTH(A1),3)<>1

 

[Larcen60]

Jtakw,

That's totally my mistake, your formula worked fine. I actually tried a similar formula to what you provided, but the error I was getting was about an invalid formula. I forgot that I changed my default seperator from a comma to a pipe when I was fixing a macro I was doing a little while ago. I changed the default back to comma and it worked great. Sorry for misleading you! Thank you both very much for the help, I appreciate it!

 

[jtakw]

Glad you sorted it out, and you're welcome.