how many times a particular day of the week appears in a given month
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
"CheapTequila" wrote in message ps.com...
| 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!
|
|