How many week days in a month

Hello again, more help for the same workbook if possible please.

I need to be able to quickly check to see how many mondays, tuesdays, wednesdays, thursdays and fridays there are in any given month.

so for example
a2 = January 2007 (01/07)
a3 = february 2007 (02/07)
etc

then
b1 = monday
c1 = tuesday
etc

then
b2 = number of mondays in January
c2 = number of tuesdays in January
b3 = number of mondays in February
c3 = number of tuesdays in February

Ive posted an example to explain in google to explain what I mean here
any help greatly appreciated
Laurie

[QUOTE=daddylonglegs]Assuming A2 downwards is always the 1st day of the month, formatted how you want and b1, c1 are days of the week in text format (without an "s" on the end) then you could use either of these formulas in B2 copied across and down

=INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+$A3-$A2-1)/7)

or

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2&":"&$A2+30)),"ddddm")=B$1& MONTH($A2)))

the first is more efficient but requires that there is always an extra date in column A, i.e. if you want to calculate for the whole of 2007, up to december then you have to also include 1st January 2008

edit: sorry glitch in 1st formula, edited version above is correct...
QUOTE]
wow, thank you so much, that is exactly what I needed, and I never would of worked that out for myself. Thanks :-)
I used the first formula, as I will probably still need this next year, so I included 1/1/2008 but it comes up with -5636 or -5635 (depending on the day) is this because we are in 2007 and in 2008 it would format correctly?
Thank you SO much you've really helped me out lots.
Laurie

That formula won't work for the last row, you always need the extra date, so if you have dates down to A20 for instance then the formula will only work down to row 19

You can use a formula that will work on any row, it's just a bit longer, thats all

Either of these in B2

=INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(DATE(YEAR($A2),MONTH($A2)+1,-1)))/7)

or using EOMONTH function from Analysis ToolPak

=INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(EOMONTH($A2,0)-1))/7)

=INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(DATE(YEAR($A2),MONTH($ A2)+1,-1)))/7)

wow ... fantastic, thank you. I am truly amazed, I cant even begin to understand this formula, but its going to save me a lot of brain-ache Thank you so much

every time I look at this I find a different way to do it. This is possibly the best.....

=4+(DAY($A2-WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+35)>9)