Conditional Formatting of Months in a Date Range

[Chris Slater]

I am trying to create a list of Meeting details for the year for my filmmaking club. I would like to format the list using the month within the date column for each meeting so the January's meetings are in one colour, February's in another colour, March yet another and so on.
As this is an ongoing list I am using a simple formula to create the dates. Our meetings are always on a Monday so I just add "7" to the date above. That way I don't have to waste time working out the meeting dates.
The problem with this seems to be that Excel struggles to work out what it's looking for when trying to interpretate what date is in the cell. I have the cells formatted as "DDD DD YYYY" to produce a date result of Mon 06 Jan for the date 06/01/2025. I have tried to use Conditional formatting's 'ISNUMBER' option to find both "Jan" and "/01/" but it fails on both.
My date column is 'A' with a Header value of "Date" in Cell 'A1'.
Has anyone got a better way of doing this with pasting all the values in the date column as text.

 

[Peter_SSs]

I have the cells formatted as "DDD DD YYYY" to produce a date result of Mon 06 Jan

That formatting would not produce that result. Did you mean "DDD DD MMM"?

Is this the sort of thing you mean?

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

25 01 03.xlsm
	A
1	Date
2	Mon 06 Jan
3	Mon 13 Jan
4	Mon 20 Jan
5	Mon 27 Jan
6	Mon 03 Feb
7	Mon 10 Feb
8	Mon 17 Feb
9	Mon 24 Feb
10	Mon 03 Mar
11	Mon 10 Mar
12	Mon 17 Mar
13	Mon 24 Mar
14	Mon 31 Mar
15	Mon 07 Apr
16	Mon 14 Apr
17	Mon 21 Apr
18	Mon 28 Apr
19	Mon 05 May
20	Mon 12 May
21	Mon 19 May
Dates

Cell Formulas
Range	Formula
A3:A21	A3	=A2+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A21	Expression	=MONTH(A2)=5	text	NO
A2:A21	Expression	=MONTH(A2)=4	text	NO
A2:A21	Expression	=MONTH(A2)=3	text	NO
A2:A21	Expression	=MONTH(A2)=2	text	NO
A2:A21	Expression	=MONTH(A2)=1	text	NO

 

[Chris Slater]

Hi Peter,
That's exactly what I want but I'm afraid I'm not quite sure what I'm looking at. The images in your solution aren't familiar to me.
Could you please expalin what, exactly, have you done to get the result?

My version is 2411 (Build 18227.20162)

 

[Peter_SSs]

Could you please expalin what, exactly, have you done to get the result?

1. Select from A2 down to the bottom of the dates
2. Home ribbon tab -> Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =MONTH(A2)=1 -> Format... -> Fill tab -> Choose yellow-> OK -> OK
3. Repeat step 2 for the other formulas and colours

 

[Chris Slater]

Brilliant! Thanks very much for that Peter it's saved me a lot of manual formatting.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.