Week number for each month

[MrVillareal]

Hi MrExcel users,

Can anyone explain this formula?

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

This formula is resulting to week number every month. I just can't figure it out.

Column A is the date from january 1, 2011 up to 31, 2011. I want to return the week number for each month in column B.

Thank you in advance.

 

[VoG]

Hi. This may help you http://www.cpearson.com/excel/WeekNumbers.aspx

 

[MrVillareal]

Hi Vog tnx for the link but what i really want is formula and try to avoid VBA. And I found that formula in my post but I just can't figure it out how that formula operates. Can you explain that formula if you dont mind? thank you for your time..

 

[MrVillareal]

Here is my sample data.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">Sat, Jan-01-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">Sun, Jan-02-2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">Mon, Jan-03-2011</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">Tue, Feb-01-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">Wed, Feb-02-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">Thu, Feb-03-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">Tue, Mar-01-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">Wed, Mar-02-2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">Thu, Mar-03-2011</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet26</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A1</font>)-2+WEEKDAY(<font color="Green">A1-DAY(<font color="Purple">A1</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A2</font>)-2+WEEKDAY(<font color="Green">A2-DAY(<font color="Purple">A2</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A3</font>)-2+WEEKDAY(<font color="Green">A3-DAY(<font color="Purple">A3</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A4</font>)-2+WEEKDAY(<font color="Green">A4-DAY(<font color="Purple">A4</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A5</font>)-2+WEEKDAY(<font color="Green">A5-DAY(<font color="Purple">A5</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A6</font>)-2+WEEKDAY(<font color="Green">A6-DAY(<font color="Purple">A6</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A7</font>)-2+WEEKDAY(<font color="Green">A7-DAY(<font color="Purple">A7</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A8</font>)-2+WEEKDAY(<font color="Green">A8-DAY(<font color="Purple">A8</font>)+1</font>)</font>)/7</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">DAY(<font color="Green">A9</font>)-2+WEEKDAY(<font color="Green">A9-DAY(<font color="Purple">A9</font>)+1</font>)</font>)/7</font>)+1</td></tr></tbody></table></td></tr></table><br />

 

[scottylad2]

Can you explain your thinking beside your week numbers? are these week numbers you have counted within each month? the actual weeknumbers for the year don't bear any resemblence to your sample

Excel Workbook
	A	B
1	01/01/2011	1
2	02/01/2011	2
3	03/01/2011	2
4	01/02/2011	6
5	02/02/2011	6
6	03/02/2011	6
7	01/03/2011	10
8	02/03/2011	10
9	03/03/2011	10
Sheet4

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th></tr></thead><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">Sat, Jan-01-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">Sun, Jan-02-2011</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">Mon, Jan-03-2011</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">Tue, Feb-01-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">Wed, Feb-02-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">Thu, Feb-03-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">Tue, Mar-01-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">Wed, Mar-02-2011</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">Thu, Mar-03-2011</td><td style="text-align: right;;">1</td></tr></table>

 

[jasonb75]

You can get the same results from

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

is that easier for you to understand than barry houdini's formula in http://www.mrexcel.com/forum/showthread.php?t=308426

 

[MrVillareal]

Thank you for all the replies and most of all thank you Jasonb75 and yes your right I got it from barry houdini. And I think I will take your formula because its easy for me to understand. Thank you for all your time and effort Excel Genius and Thank you MrExcel for this wonderful site

 

[barry houdini]

You can get the same results from

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

I don't believe that will always give the required results, Jason

My suggested formula, quoted in this thread, works like WEEKNUM within each month so that 1st of the month is always in week 1 and then week 2 starts on the next Sunday. The above formula doesn't do that if the month starts on a Sunday (it assigns 2 to the 1st of the month so 1st May 2011, for example, would be in week 2).

The original thread where I posted that formula was from 2008 - more recently I have posted a shorter version which gives exactly the same results, i.e.

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

see this thread for example

As for how it works......

.....I'll try to explain the shorter version

Assigning 1 to the 1st of the month and then incrementing by 1 every subsequent Sunday is actually the same as counting Sundays in a period including the last 6 days of the previous month up to the A1 date.

I already have a formula for counting Sundays in a period......which goes like this

=INT((8-WEEKDAY(date2)+date2-date1)/7)

see this thread for an explanation of that......[in fact it's a slightly different formula but I quote the above version as an alternative at the end]

so if the period includes the last 6 days from the previous month up to A1 then date2 = A1 and date1 = A1-DAY(A1)-5.....so the above formula becomes

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

which can be further simplified to this

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

and then you can add the 5 to the 8 to get 13 and this version

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

Note that Jason's suggestion is the equivalent of exactly the same formula but with 14 instead of 13 (hence including the last 7 days of the previous month) which is why it mis-performs on months beginning on Sunday