Prev: Auto insert row while entering raw data
Next: Disable conversion from text to number from .csv file
From: Maverick_TG on 7 Jul 2008 15:28
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
From: T. Valko on 7 Jul 2008 16:56
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" <caseyjverk(a)gmail.com> wrote in message
news:4164914a-a036-4af5-bb72-7b37af5178dc(a)59g2000hsb.googlegroups.com...
>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


From: Jarek Kujawa on 8 Jul 2008 02:32
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
 | 
Pages: 1
Prev: Auto insert row while entering raw data
Next: Disable conversion from text to number from .csv file