Formula for Week# of Month

[Kuih]

Dear Excel gurus, what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?

For example (Sunday being the first day of the week):
January 5th 2008 = Week 1 of January
January 6th 2008 = Week 2 of January
February 2nd 2008 = Week 1 of February
February 3rd 2008 = Week 2 of February

WEEKNUM perfectly calculates this, but it is applicable for the whole year.

 

[Kuih]

If you want to mimick the ISO definition of a week number, but for the month scale,
you might use the VBA code from Pearson:

http://www.cpearson.com/excel/DateTimeVBA.htm

and adjust it to do the same on a month scale.

Thanks lalbatros, I was thinking of something simpler without enabling the macro functions:

B12=March 1, 2008

AND(DAY(B12)=1,TEXT(B12,"DDDD")="Saturday")

I was hoping a generic code which tells 1st day of a month is what day. If Saturday, then I just offset the codes given by iliace and barry by one day using a condition of course.

 

[Kuih]

That's not the functionality of WEEKNUM. Try WEEKNUM with Jan 1, 2011 and Jan 3, 2011 (identical to your example).

With the functionality you're requesting, what should the output be for March 1, 2008? Last week of February 2008? Week zero?

I agree, WEEKNUM for March 1, 2008 is 9 and using your code is 1.

Consider March 3, 2008 is the first working day of the month, so the output should be Week #1 of March.

 

[Kuih]

I think I'm going to use
=EOMONTH(B12,-1)+1,
B12=March 4, 2008

So it will return as March 1, 2008 (yeah, silly code)

and combine it with

AND(DAY(B12)=1,TEXT(B12,"DDDD")="Saturday") to perform a check if the 1st day of the month returns a Saturday.

 

[Kuih]

Here's my messy code:

=IF(AND(DAY(EOMONTH(B12,-1)+1)=1,TEXT(EOMONTH(B12,-1)+1,"DDDD")="Saturday"),INT((DAY(B12)-2+WEEKDAY(B12-DAY(B12)+1))/7),INT((DAY(B12)-2+WEEKDAY(B12-DAY(B12)+1))/7)+1)

If
B12= Mar 1, 2008 (Saturday) returns 0
B12= June 1, 2008 (Sunday) returns 1
B12= August 1, 2008 (Friday) returns 1
B12= May 1, 2009 (Friday) returns 1
B12= August 1, 2009 (Saturday) returns 0

Thank you all for your kind contribution!

If you know a simpler code, please share with us...

 

[barry houdini]

This will give you the same results

=INT((DAY(B12)+4+WEEKDAY(B12-DAY(B12)+2))/7)

 

[Kuih]

This will give you the same results

=INT((DAY(B12)+4+WEEKDAY(B12-DAY(B12)+2))/7)

Dear Houdini,

You're a natural genius!

 

[KateWantsToLearn]

Hi Iliace!

Your solution to this problem is great and the closest I've found for my similar question, but I get some weeks (I think when the 1st of the month is on a Saturday?) when it will go right from week five to week 2. Can you please help?

Example:

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=157 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1386 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17>Date</TD><TD class=xl1386 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=86>Wk of Month</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>7/1/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1

</TD></TR>
the week of 10/7/11 below
<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>7/8/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>7/15/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3

</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>7/22/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>7/29/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5

</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>8/5/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>8/12/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2

</TD></TR>
<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>8/19/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>8/26/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4

</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>9/2/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">1

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>9/9/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">2

</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>9/16/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>9/23/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">4

</TD></TR>

<TR style="HEIGHT: 13.5pt" height=18><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>9/30/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">5

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>10/7/2011</TD><TD class=xl1387 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">2

</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD class=xl1384 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>10/14/2011</TD><TD class=xl1385 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">3

</TD></TR></TBODY></TABLE>

I can't think of a built-in, or otherwise efficient way to get that result, but try this formula:

=SUMPRODUCT(--(DATE(YEAR(B12),MONTH(B12),ROW($1:$31))<=B12),--(WEEKDAY(DATE(YEAR(B12),MONTH(B12),ROW($1:$31)),1)=1))+1

Where B12 is where you have the date you want the week number for.

 

[iliace]

This will give you the same results

=INT((DAY(B12)+4+WEEKDAY(B12-DAY(B12)+2))/7)

Did you try this one? I think it works as you want for October 2011.

 

[KateWantsToLearn]

Dang, you're good!!! Thank you so much for your quick and very helpful reply!!

Did you try this one? I think it works as you want for October 2011.

 

[MrVillareal]

If it works like weeknum then the first of every month will be week 1 and week 2 will start on the next Sunday. Of course this means that some months will go up to week 6. To do that, with date in A1

=INT((DAY(A1)-2+WEEKDAY(A1-DAY(A1)+1))/7)+1

Hi Sir Barry,

If its ok with you sir can you explain the formula.

INT((DAY(A1)-2+WEEKDAY(A1-DAY(A1)+1))/7)+1

I do understand the INT, DAY, AND WEEKDAY FUNCTION. But the way you rationalized the data is simply "Genius"..Tnx in advance..