# Count Days of Week

#### iherndon

##### Board Regular
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.

@IanHerndon

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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>

 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

Replies
9
Views
285
Replies
2
Views
371
Replies
0
Views
177
Replies
1
Views
132
Replies
8
Views
623

1,203,514
Messages
6,055,836
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back