how many times a particular day of the week appears in a given month

[CheapTequila]

Hello - I'm building a spreadsheet that is supposed to calculate how
many times a particular day of the week appears in the month for a
billing letter. Students are scheduled for a lesson on one of the days
of the week, and based on that day, I want to count how many times that
day appears in a given month and calculate their bill. For example,
there are 5 Tuesdays, Wednesdays and Thursdays in July 2006, but only 4
Fridays, Saturdays, Sundays and Mondays. I've got a column for the
date, and another column for the day (Monday, Tues. etc.) Is there some
function in Excel that would help do this or a formula or algorithm?
Thanks!

 

[Bob Phillips]

You mean August not July

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2006,8,1)&":"&DATE(2006,9,0))))=3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Niek Otten]

This formula from Chip Pearson gives you the date of the Nth Day of the week in a month and year:

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.



Use it to find the 5th day in a month.
Now just compare the month of that date (using the MONTH() function with your month; if they'r equal, you have 5 of those days,
otherwise 4.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello - I'm building a spreadsheet that is supposed to calculate how
| many times a particular day of the week appears in the month for a
| billing letter. Students are scheduled for a lesson on one of the days
| of the week, and based on that day, I want to count how many times that
| day appears in a given month and calculate their bill. For example,
| there are 5 Tuesdays, Wednesdays and Thursdays in July 2006, but only 4
| Fridays, Saturdays, Sundays and Mondays. I've got a column for the
| date, and another column for the day (Monday, Tues. etc.) Is there some
| function in Excel that would help do this or a formula or algorithm?
| Thanks!
|

 

[daddylonglegs]

You say you have column for the month and column for the day.

If the month has been input in the format Aug-06 and day is the ful
weekday, e.g. "Wednesday" then assuming date in A2 and weekday in B2

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&A2+31-(DAY(A2+31)))),"dddd")=B2)

 

[Ron Rosenfeld]

Hello - I'm building a spreadsheet that is supposed to calculate how
many times a particular day of the week appears in the month for a
billing letter. Students are scheduled for a lesson on one of the days
of the week, and based on that day, I want to count how many times that
day appears in a given month and calculate their bill. For example,
there are 5 Tuesdays, Wednesdays and Thursdays in July 2006, but only 4
Fridays, Saturdays, Sundays and Mondays. I've got a column for the
date, and another column for the day (Monday, Tues. etc.) Is there some
function in Excel that would help do this or a formula or algorithm?
Thanks!

To calculate the number of a particular weekday in a month:

=4+(MONTH(A1+35-WEEKDAY(A1+7-WDN))=MONTH(A1))

where A1 contains day 1 in the month (e.g. 1 AUG 2006) and WDN is the weekday
number (1=Sun, 2=Mon, etc).


--ron