week number in a month

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/weeknum.htm

you should first define which weeknumber definition you want to use.

 

[pwz]

Hello All,

With a given date in a cell, is there any function to give its week number
in a month? For example, April 24, 2004, it is in the 4th week of April,
assuming starting day of each week is Monday.

Thanks in advance!

Patrick

 

[pwz]

Hello Frank,

Thanks for your reference!

It seems that Pearson's page is dealing with week number for a year.
However, I want the week number for a month instead. How can I exactly get
it?

 

[Frank Kabel]

Hi
for this you also have to define first your algorithmn for determining
the weeknumber of a month. e.g. what's the first week if the 1st of a
month is on a thursday?

 

[JMay]

Build a simple Lookup table for the year 2004 like:

R S T
1 Start End Week Num Input R5 and use formulas for R3 down
= R2+7, etc
2 1/5/04 1/10/04 1 Manually Input Column T
3 1/12/04 1/17/04 2
4 1/19/04 1/24/04 3
5 1/26/04 1/31/04 4
6 2/2/04 2/7/04 1
7 2/9/04 2/14/04 2
8 2/16/04 2/21/04 3
9 2/23/04 2/28/04 4
...............

As you enter dates in A1:A10
On Cell B1 enter and copy down:

=IF(WEEKDAY(A1,2)<=5,VLOOKUP(A1,$R$2:$T$9,3),"") <<chg T9 to your last
row

The Above Excludes Saturdays, chg accordingly...
Until someone comes up with a better answer...
HTH

 

[pwz]

Hi Frank,

Sorry that I've wrongly replied to your personal email address! Please
igore it if you find nuisance.

What I need is just to look at each month individually, like:

Mon Tue Wed Thu Fri Sat Sun
1 2 3 4 5 1st
week
6 7 8 9 10 11 12 2ndweek
....

Is it impossible to get the week number by Excel built-in function?

Anyway, thanks again!

 

[pwz]

Hi JMay,

Your suggestion is very good indeed! However, I still want to see if any
combined built-in functions can do that without building up a look-up table
first.

Very appreciate your help!

 

[Daniel.M]

Hi Patrick,

With your date in A1:

=INT((5+DAY(A1)+WEEKDAY(A1-DAY(A1)))/7)

Regards,

Daniel M.

 

[pwz]

Dear Daniel,

It works exactly what I need. Greatly appreciate your help!

Have a good day!

Patrick