count wednesdays within a month

  • Thread starter Thread starter sato
  • Start date Start date
S

sato

I want to count how many let's say wednesdays are within a calendar month and
for each month of the year
 
The general formula is:

=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW))

Where A1 is any date in the month/year of interest.

Where DOW = the weekday number that you want the count for.

1 = Monday
2 = Yuesday
3 =Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday
 
I have to read the formula as if it were words in a book. In reading this
formula, it kind of makes sense but not really. Is there anyway (without
taking you forever to type it out) that you can expain the formula in more
detail?
 
I find something like this formula easier to understand.

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE(2010,1,1)
&":"&DATE(2010,12,31))),"mmmddd")="FebWed"))

I'm sure that it's much slower than Biff's, but it's pretty straight forward.

The biggest thing is the =indirect() function. That just makes it so that excel
sees this string:
date(2010,1,1)&":"date(2010,12,31)
as a range of rows.

Date(2010,1,1) is just a number that's nicely formatted to excel. If you format
it as general, you'll see 40179 (with a base date of 1904).

=sumproduct() likes to work with numbers, so the -- stuff changes a bunch of
true/false's to 1's and 0's.

And if you put the start date in A1 and the end date in A2, the formula changes
to:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(a1&":"&a2)),"mmmddd")="FebWed"))

And if you wanted to count the number of days in that time interval, you'd
change the format and the string to look for:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Wed"))
 
=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW))

I'm not too good at explaining date formulas.

In general:

If the month has 29 days only the first weekday of the month will have 5 of
those weekdays in the month. This would only apply to February of a leap
year.

If the month has 30 days only the first 2 weekdays of the month will have 5
of those weekdays in the month. This would apply to April, June, September
and November.

If the month has 31 days only the first 3 weekdays of the month will have 5
of those weekdays in the month. This would apply to January, March, May,
July, August, October and December.

Every month has at least 4 full weeks (28 days) so there will be at least 4
Wednesdays (as an exmple) in every month.

Based on the number of days in a particular month and the repeating sequence
of the weekdays we can say that the specific weekday must appear before the
4th day of any month.

So, the weekday to count for must be before the 4th of the next month:

DAY(A1-DAY(A1)+35)

And after a certain weekday of the last week of the previous month:

WEEKDAY(A1-DAY(A1)-DOW)

That probably doesn't make much sense but if you were to break the formula
down into individual tests you would see that this is true.

We're starting with 4 weekdays:

=4+

Then testing that the weekday meets this condition:

(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW))

If that condition is TRUE then it adds 4 + 1. If that condition is FALSE
then it adds 4 + 0.

So, to count how many Wednesdays are in January 2010:

A1 = any date in January 2010 like 1/27/2010.

=4+(DAY(E1-DAY(E1)+35)<WEEKDAY(E1-DAY(E1)-3))

= 4
 
Back
Top