Count Days of Week

[iherndon]

I guess this might be sorta simple for most, but I'm kind of stumped.

Here's what I've got:

25,000 rows of data from 1/1/2012 to 6/1/2012. Cell A1 is the format 'MM/DD/YYYY HH:MM:SS AM/PM'

I'm essentially trying to determine the volume by day. I have a total count for each day but now need a simplified way to determine x amount of Mondays to divide by the total # of records generated on Mondays. So if there are 100 records for a particular date range and that period of time consists of 10 different Mondays, the average would be 10.

Thanks for any help you can provide!

@IanHerndon

 

[iherndon]

I didn't find this with my first search but ended up discovering the solution from this previous thread: http://www.mrexcel.com/forum/showthread.php?526867-Breakdown-Days&highlight=count+days+week

In Excel's WEEKDAY function, Sunday = 1, so this array formula will give the answer if:

A2 = start date
B2 = end date
C2 = day of week to count

This formula in E2:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

...confirmed by pressing CTRL-SHIFT-ENTER to confirm the formula.

Sheet2

	A	B	C	D	E
1	From	To	Day of Week		Count
2	6/1/2011	6/22/2011	1		3

<colgroup><col style="font-weight: bold; width: 30px; "><col style="width: 64px; "><col style="width: 64px; "><col style="width: 88px; "><col style="width: 64px; "><col style="width: 64px; "></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 {=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4