week number in a month

  • Thread starter Thread starter Frank Kabel
  • Start date Start date
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
 
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?
 
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?
 
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
 
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!
 
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!
 
Hi Patrick,

With your date in A1:

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

Regards,

Daniel M.
 
Dear Daniel,

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

Have a good day!

Patrick
 
Back
Top