Week of the month for any year

[SoulSeeker]

Hi,

I would like to get some help on an issue i am having. Im trying to do this:
Date Work Week Number
01/02/2011 Week 1 1

In the example above i have a cell lets say A1 that has the date on the example, i want to know what week number from the month is that date (Work Week starts on Sunday). So if it were 01/30/2011 that would be week 1 of february and if it were 03/27/2011 that would be week 5. Any help is appreciated.

Thanks

 

[AlphaFrog]

I don't understand why 01/30/2011 is not week 5 of January while 03/27/2011 is week 5 of March.

If the week starts on a Sunday, then wouldn't the month that the week's Sunday is in be the Month that the entire week is counted within?

What are the rules (criteria) to determine what month the week is counted within when the week straddles two months?

 

[SoulSeeker]

The reason is because we are talking about work weeks which start on Sunday. Which means:
1/2/11 - 1/8/11 is Week 1
1/9/11 - 1/15/11 is Week 2
1/16/11 - 1/22/11 is Week 3
1/23/11 - 1/29/11 is Week 4

1/30/11 - 2/5/11 is Week 1 of Feb
2/6/11 - 2/12/11 is Week 2 of Feb

And so on. Here's a 2011 Work Week Calendar which has the week number right beside it but what i need is if i give a random date give me the week number based on that.

 

[SoulSeeker]

Just noticed the error, my bad that was a typo. 02/27/2011 would be Week 1 of March and 03/27/2011 would be Week 1 of April, while 04/24/2011 would be Week 5 of April

 

[AlphaFrog]

According to your calendar, wouldn't 03/27/2011 be week 1 of April?

EDIT: Just saw your most recent post. Got it.

 

[SoulSeeker]

So yes, that's basically what i need. If i say 04/24/2011 it gives me a number 5. Which means that's Week 5 of that month

 

[SoulSeeker]

So far the closest formula i was able to get is:
=INT((13-WEEKDAY(N466)+DAY(N466))/7)-1

But it breaks when the week includes another month. Any ideas?

 

[SoulSeeker]

=INT((13-WEEKDAY(N466)+DAY(N466))/7)-1

By the way N466 is the Date in question.

 

[AlphaFrog]

Excel Workbook
	A	B	C
1	Date	Week Number	Month
2	Saturday, January 01, 2011	5	Dec-2010
3	Sunday, January 02, 2011	1	Jan-2011
4	Wednesday, January 26, 2011	4	Jan-2011
5	Monday, January 31, 2011	1	Feb-2011
6	Tuesday, February 01, 2011	1	Feb-2011
7	Sunday, February 27, 2011	1	Mar-2011
8	Tuesday, March 01, 2011	1	Mar-2011
9	Thursday, March 31, 2011	1	Apr-2011
10	Friday, April 01, 2011	1	Apr-2011
11	Sunday, April 24, 2011	5	Apr-2011
12	Sunday, May 01, 2011	1	May-2011
...

Cell Formulas
Range	Formula
B2	=INT(DAY(A2+6-WEEKDAY(A2,1))/7)+1-IF(AND(DAY(DATE(YEAR(A2),1,1)+7-WEEKDAY(DATE(YEAR(A2),1,1),2))>1,MONTH(A2+6-WEEKDAY(A2,1))=1),1,0)
C2	=TEXT(A2+6-WEEKDAY(A2,1), "mmm-yyyy")

 

[SoulSeeker]

My friend, that is exactly what i wanted, you just got a degree as an Excel Master hehe... I was looking into several other threads made by people requesting the same thing, and this answers it all. Thanks a lot