# Thread: First Monday of the month - conditional format

1. Hi gang,
I want to put conditional formatting (shading) so that the first Monday of the month looks different.
Looking at past threads I have figured out how to calculate the first Monday of the month.
D5 = Nov. 30, 2009 (in date format)
the formula is
=D5-DAY(D5)+1+MOD(2-WEEKDAY(D5-DAY(D5)+1);7)
the result is Nov. 2, 2009 which is the first Monday of the month
When I put this formula in my conditional formatting for cell D5 it gets shaded and all the other cells where I copied the conditional formatting get shaded as well.
What am I missing?
tks

2. I'd try a formula like =AND(DAY(C1)<8,WEEKDAY(C1,2)=1)
Where C1 is the cell containing the date you want examined.
I'm attaching a sample sheet[attachment=86880:Formatting the first monday in the month.xlsx]

From your example, I'd say that the test you are performing =D5-DAY(D5)+1+MOD(2-WEEKDAY(D5-DAY(D5)+1);7) is not a true/false test
(it is returning a date). So it is true throughout the spreadsheet for every cell, hence every cell changes colour.

For conditional formatting [s]with a date to work [/s]- the formula has to evaluate to a True/False result.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•