Dear Expert,
Is there any functions that can identify the number and the sequence of week
in a month please ?
Weekday(A1) can only give Mon,Tue, Wed ....
Say today 14 May is the 3rd week of May.
7 May is 2nd week of May.
1 and 2 May are the 1st week of May.
I have used Weekday(A2) - Weekday(A1) to sort out the weekend.
But it is hard to give this is the 1st or 2nd or 3rd or 4th week in that
month.
Hope someone can help

Not sure how you want to count the weeks. Do you mean that May 1 and 2 are in
the first week, May 3 to May 9 in 2nd week, May 10 to May 16 in third week
etc.

If above is correct then you can use the WEEKNUM function that returns the
wee number of the year and calculate the week number in the mont by
subtraction as per the following example.

=WEEKNUM(A1,1) - WEEKNUM(DATEVALUE("1/5/09"),1)+1

Where A1 contains a date in the month.

Lookup WEEKNUM in Help for how to start week on Sunday or Monday.

--
Regards,

OssieMac


"Elton Law" wrote:

Dear Expert,
Is there any functions that can identify the number and the sequence of week
in a month please ?
Weekday(A1) can only give Mon,Tue, Wed ....
Say today 14 May is the 3rd week of May.
7 May is 2nd week of May.
1 and 2 May are the 1st week of May.
I have used Weekday(A2) - Weekday(A1) to sort out the weekend.
But it is hard to give this is the 1st or 2nd or 3rd or 4th week in that
month.
Hope someone can help

=WEEKNUM(A1)-WEEKNUM(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+1



"Elton Law" wrote:

Dear Expert,
Is there any functions that can identify the number and the sequence of week
in a month please ?
Weekday(A1) can only give Mon,Tue, Wed ....
Say today 14 May is the 3rd week of May.
7 May is 2nd week of May.
1 and 2 May are the 1st week of May.
I have used Weekday(A2) - Weekday(A1) to sort out the weekend.
But it is hard to give this is the 1st or 2nd or 3rd or 4th week in that
month.
Hope someone can help

A1 : 14 May 2009
A2 : 7 May 2009
A3 : 1 May 2009
A4 : 2 May 2009

B1, entered the formula, and filled down

=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1&MID("stndrdthth",(WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1)*2-1,2)&" week of "&TEXT(A1,"mmmm")

The result of B1 will return : 3rd week of May

Regards
Bosco


"Elton Law" wrote:

Dear Expert,
Is there any functions that can identify the number and the sequence of week
in a month please ?
Weekday(A1) can only give Mon,Tue, Wed ....
Say today 14 May is the 3rd week of May.
7 May is 2nd week of May.
1 and 2 May are the 1st week of May.
I have used Weekday(A2) - Weekday(A1) to sort out the weekend.
But it is hard to give this is the 1st or 2nd or 3rd or 4th week in that
month.
Hope someone can help

Hi Bosco,
Yours work. Thanks so much.

Hi Teethless Mama,
14 May, 7 May, 1 May, 2 May work under your formula.
But I tried 30 Jun, 2 Jul, 29 Jul .... they gave me 10,11,10,14 respectively.
I am afirad yours cannot make it.

Anyway, thanks indeed.


"bosco_yip" wrote:

A1 : 14 May 2009
A2 : 7 May 2009
A3 : 1 May 2009
A4 : 2 May 2009

B1, entered the formula, and filled down

=WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1&MID("stndrdthth",(WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1)*2-1,2)&" week of "&TEXT(A1,"mmmm")

The result of B1 will return : 3rd week of May

Regards
Bosco


"Elton Law" wrote:

Dear Expert,
Is there any functions that can identify the number and the sequence of week
in a month please ?
Weekday(A1) can only give Mon,Tue, Wed ....
Say today 14 May is the 3rd week of May.
7 May is 2nd week of May.
1 and 2 May are the 1st week of May.
I have used Weekday(A2) - Weekday(A1) to sort out the weekend.
But it is hard to give this is the 1st or 2nd or 3rd or 4th week in that
month.
Hope someone can help