Number of Weekdays

[Guest]

How can I create a formula in a worksheet and/or Pivot Table where I can
figure out an average based on how many certain weekdays fall within that
month. For instance here is a sample:
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 12 PM 50
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 1 PM 75
1/3/2006 Kenyon Clinic 07398 Chest-- PA & Lateral 5 PM 123
1/4/2006 Kenyon Clinic 07225 Shoulder 2vw 7 AM 89
1/4/2006 Kenyon Clinic 07405 Knee 2vw AP/Lat 1 PM 56

I need to summarize these numbers for an entire year . They want to
summarize it by the hour for each day. Then I need to come up with an
average where I need to use the number of each weekday within each month as
the denominator. So, if I had data for three out of five Mondays in a month,
then I need to take my summary and divide that by five to get a true figure.
Right now I am only getting the summary divided by three to get my average
and that is not what they want. Any ideas or tricks up anyone's sleeve? How
can I set up a formula that will figure out how many Sundays, Mondays, etc.
in each given month and then use that in my formula?

 

[Guest]

Put the first date of of the month in A1

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($A$1),MONTH($A$1),1)&":"&DATE(YEAR($A$1),MONTH($A$1)+1,0))),2)=1))

will give you the number of Mondays in that month

change the =1 to =2 and you'll get Tuesdays

if you want to check Mondays for all months in 2006, put 01/01/06 in A1 and
use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($A$1),ROWS($A$1:A1),1)&":"&DATE(YEAR($A$1),ROWS($A$1:A1)+1,0))),2)=1))


copy down 12 rows and you'll get Jan, Feb, Mar and so on


Regards,

Peo Sjoblom

 

[daddylonglegs]

If you have the 1st of the month in A1 this formula will give the number
of Mondays in that month

=INT((WEEKDAY(A1-2)+31-DAY(A1+31))/7)

replace the 2 with 3 for Tuesday etc.

 

[daddylonglegs]

Or even simpler...

=4+(DAY(A1+35-WEEKDAY(A1-2))>7)