'08-'11 Days of the Wk per Month
 

I have an array of date ranges from 2008-2011 and would like to
calculate how many "Mondays" per month take place within this range.

For example, range #1 6/29/09 - 8/16/09 consists of 7 weeks over 3
months. Is there a way I may seperate the # of weeks per month, June -
1 "Monday", July - 4 "Mondays", August - 2 "Mondays" (1 + 4 + 2 = 7)

As there is 500+ various date ranges, any assistance would be
appreciative. Thanks

'08-'11 Days of the Wk per Month
 

If you want the total count of Monday's from a start date to an end date
(inclusive):

A1 = start = 6/29/2009
B1 = end = 8/16/2009

=INT((WEEKDAY(A1-1,2)+B1-A1)/7)

Or, the generic syntax to count other weekdays:

=INT((WEEKDAY(start-n,2)+end-start)/7)

Where n = day of the week: Monday = 1 through Sunday = 7

If you want a break-out summary then that'll take some work!

--
Biff
Microsoft Excel MVP


"Maverick_TG" wrote in message
...
I have an array of date ranges from 2008-2011 and would like to
calculate how many "Mondays" per month take place within this range.

For example, range #1 6/29/09 - 8/16/09 consists of 7 weeks over 3
months. Is there a way I may seperate the # of weeks per month, June -
1 "Monday", July - 4 "Mondays", August - 2 "Mondays" (1 + 4 + 2 = 7)

As there is 500+ various date ranges, any assistance would be
appreciative. Thanks

'08-'11 Days of the Wk per Month
 

B1=6/29/09
B2=8/16/09
$A$1:$A$40 includes yr dates

=SUM(IF((WEEKDAY($A$1:$A$40,2)=1)*($A$1:$A$40=B1) *($A$1:$A$40<=B2),
1,0))

the result is 6 (in my Excel 2003)

array-enter the formula i.e. CTRL+SHIFT+ENTER

HIH