Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default count wednesdays within a month

I want to count how many let's say wednesdays are within a calendar month and
for each month of the year
--
sato panago
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: count wednesdays within a month

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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default count wednesdays within a month

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default count wednesdays within a month

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default count wednesdays within a month

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



.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default count wednesdays within a month

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
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default count wednesdays within a month


"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



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default count wednesdays within a month

=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



.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default count wednesdays within a month

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


.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Wednesdays within a calendar month sato Excel Worksheet Functions 1 February 27th 10 11:18 PM
count month when date is in day/month/year format ccKennedy Excel Worksheet Functions 6 April 30th 09 03:32 AM
Excel Auto format for Monday Wednesdays Jim Fisher Excel Discussion (Misc queries) 1 February 8th 06 02:45 AM
2nd and 4th Wednesdays formula? lawdoggy Excel Worksheet Functions 12 August 8th 05 05:20 PM
2nd and 4th Wednesdays formula? lawdoggy Excel Worksheet Functions 2 August 4th 05 06:04 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"