I want to count how many let's say wednesdays are within a calendar month and
for each month of the year
--
sato panago

Here's how you can count the number of Wednesdays in a month using Microsoft Excel:

1. First, create a new Excel sheet and enter the months of the year in column A (starting from A1) and the year in column B (starting from B1).
2. In column C, enter the formula "
   Code:

   =EOMONTH(DATE(B1,A1,1),0)

   " - this will give you the last day of the month for each month and year combination.
3. In column D, enter the formula "
   Code:

   =NETWORKDAYS(DATE(B1,A1,1),C1)

   " - this will give you the total number of working days (excluding weekends) in the month.
4. Finally, in column E, enter the formula "
   Code:

   =NETWORKDAYS.INTL(DATE(B1,A1,1),C1,11)

   " - this will give you the total number of Wednesdays in the month (assuming that Wednesday is the 3rd day of the week, which is represented by the number 11 in the formula).
5. Copy the formulas in columns C, D, and E for all the months of the year.

That's it! You should now have a table that shows the last day of each month, the total number of working days in each month, and the total number of Wednesdays in each month for the year you specified.

Look he

http://www.cpearson.com/excel/DateTi...tm#LastWeekday

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"sato" wrote in message
...
I want to count how many let's say wednesdays are within a calendar month
and
for each month of the year
--
sato panago

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

--
Biff
Microsoft Excel MVP


"sato" wrote in message
...
I want to count how many let's say wednesdays are within a calendar month
and
for each month of the year
--
sato panago

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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"sato" wrote in message
...
I want to count how many let's say wednesdays are within a calendar month
and
for each month of the year
--
sato panago


.

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"))



sato wrote:

I want to count how many let's say wednesdays are within a calendar month and
for each month of the year
--
sato panago

--

Dave Peterson

"sato" ha scritto nel messaggio
...
I want to count how many let's say wednesdays are within a calendar month
and
for each month of the year

in A1 start date, e.g.:
1/1/2010
in B1 end date, e.g:
=DATE(YEAR(A1),MONTH(A1)+1,0)
in C1 (general formula)
=INT((B1-x)/7)-INT((A1-x-1)/7)
where
x is 4 for wednesday (1 sunday, 2 monday, ...)
cfr.: http://www.prodomosua.eu/zips/delledate.xls
.f
--
fernando cinquegrani
http://www.prodomosua.eu

=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

--
Biff
Microsoft Excel MVP


"Superblonde64" wrote in message
...
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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"sato" wrote in message
...
I want to count how many let's say wednesdays are within a calendar
month
and
for each month of the year
--
sato panago


.

Ooops!

A1 = any date in January 2010 like 1/27/2010.
=4+(DAY(E1-DAY(E1)+35)<WEEKDAY(E1-DAY(E1)-3))

Should be:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=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

--
Biff
Microsoft Excel MVP


"Superblonde64" wrote in message
...
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?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"sato" wrote in message
...
I want to count how many let's say wednesdays are within a calendar
month
and
for each month of the year
--
sato panago


.