Results 1 to 2 of 2
2009-12-03, 11:17 #1
- Join Date
- Mar 2007
- Montreal, Quebec
- Thanked 0 Times in 0 Posts
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
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?
Montreal (Quebec) CANADA
2009-12-03, 11:52 #2
- Join Date
- Jan 2001
- Redcliff, Alberta, Canada
- Thanked 5 Times in 5 Posts
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.[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile