Get week number for month from date

Presumably the 1st of the month is always in week 1 and then week 2 starts from the first Friday after the 1st? If so then with date in A1 try this formula for the week number

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

Thanks barry. its working correctly. would you kindly explain the logic of the formula?

I sometimes use this formula to calculate the number of Fridays between a date in B1 and a later date in C1

=INT((8-WEEKDAY(C1-5)+C1-B1)/7)

[the 5 determines the day to count 1 = Mon through to 7 = Sun]

In this case what you want equates to a count of Fridays in the period ending with your date and starting 6 days before the start of the month so

C1 = A1
B1 = A1-DAY(A1)-5

so if we do the replace we get

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

which becomes the formula I posted, once tidied up

Hi ! Here Suggest to you

<table style="border-collapse: collapse;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> DataSheet= Sheet1</td></tr><tr><td align="right" width="30"><table rowspan="39" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td></td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">8 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">9 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">10 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">11 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">12 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">13 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">14 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">15 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">16 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">17 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">18 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">19 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">20 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">21 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">22 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">23 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">24 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">25 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">26 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">27 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">28 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">29 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">30 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">31 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">32 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">33 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">34 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">35 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">36 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">37 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">38 </td></tr></tbody></table></td><td width="472"><table colspan="6" rowspan="19" bgcolor="#939393" border="0" cellspacing="1" width="472"><col width="92"><col width="76"><col width="76"><col width="76"><col width="76"><col width="76"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">C</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">D</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">E</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc">F</td></tr><tr height="18"><td rowspan="2" align="center" bgcolor="#ffffff">Date</td><td align="center" bgcolor="#ffffff">Week'sNum</td><td align="center" bgcolor="#ffffff">Week'sNum</td><td align="center" bgcolor="#ffffff">Week'sNum</td><td rowspan="2" align="center" bgcolor="#ffffff">One
Shoot</td><td rowspan="2" align="center" bgcolor="#ffffff">Weekday</td></tr><tr height="18"><td align="center" bgcolor="#ffffff">in One Year</td><td align="center" bgcolor="#ffffff">in Month/1</td><td align="center" bgcolor="#ffffff">in His Month</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2007-12-28</td><td align="right" bgcolor="#ffff00">52</td><td align="right" bgcolor="#ffff00">48</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">6</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2007-12-29</td><td align="right" bgcolor="#ffff00">52</td><td align="right" bgcolor="#ffff00">48</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">7</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2007-12-30</td><td align="right" bgcolor="#ffff00">53</td><td align="right" bgcolor="#ffff00">48</td><td align="right" bgcolor="#ffff00">6</td><td align="right" bgcolor="#ffffff">6</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2007-12-31</td><td align="right" bgcolor="#ffff00">53</td><td align="right" bgcolor="#ffff00">48</td><td align="right" bgcolor="#ffff00">6</td><td align="right" bgcolor="#ffffff">6</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-01</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">3</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-02</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">4</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-03</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">5</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-04</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">6</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-05</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">7</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-06</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-01-07</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-01-27</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-01-28</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-01-29</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">3</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-01-30</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">4</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-01-31</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">5</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-01</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">6</td></tr></tbody></table><table colspan="6" rowspan="19" bgcolor="#939393" border="0" cellspacing="1" width="472"><col width="92"><col width="76"><col width="76"><col width="76"><col width="76"><col width="76"><tbody bgcolor="#ffffff"><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-02</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">7</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-03</td><td align="right" bgcolor="#ffff99">6</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-04</td><td align="right" bgcolor="#ffff99">6</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-05</td><td align="right" bgcolor="#ffff99">6</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">3</td></tr><tr height="18"><td align="left" bgcolor="#ffff99">2008-02-06</td><td align="right" bgcolor="#ffff99">6</td><td align="right" bgcolor="#ffff99">5</td><td align="right" bgcolor="#ffff99">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">4</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-22</td><td align="right" bgcolor="#ffff00">8</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">4</td><td align="right" bgcolor="#ffffff">4</td><td align="center" bgcolor="#c0c0c0">6</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-23</td><td align="right" bgcolor="#ffff00">8</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">4</td><td align="right" bgcolor="#ffffff">4</td><td align="center" bgcolor="#c0c0c0">7</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-24</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-25</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-26</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">3</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-27</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">4</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-28</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">5</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-02-29</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffff00">5</td><td align="right" bgcolor="#ffffff">5</td><td align="center" bgcolor="#c0c0c0">6</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-03-01</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">1</td><td align="right" bgcolor="#ffffff">1</td><td align="center" bgcolor="#c0c0c0">7</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-03-02</td><td align="right" bgcolor="#ffff00">10</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">1</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-03-03</td><td align="right" bgcolor="#ffff00">10</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">2</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-03-04</td><td align="right" bgcolor="#ffff00">10</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">3</td></tr><tr height="18"><td align="left" bgcolor="#ffff00">2008-03-05</td><td align="right" bgcolor="#ffff00">10</td><td align="right" bgcolor="#ffff00">9</td><td align="right" bgcolor="#ffff00">2</td><td align="right" bgcolor="#ffffff">2</td><td align="center" bgcolor="#c0c0c0">4</td></tr><tr height="18"><td align="left" bgcolor="#ffffff"></td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr></tbody></table></td></tr></tbody></table>
<colspan=14 width="14" rowspan="11" height="198"></colspan=14><table style="border-collapse: collapse;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="14" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center">B3</td><td>=WEEKNUM(A3)</td><td align="right">52</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=WEEKNUM(A3)");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center"></td><td>B3 His Formula Used This Cell -> B3:B37</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">3</td><td align="center">C3</td><td>=WEEKNUM(TEXT(A3,"yyyy-mm-")&1)*1</td><td align="right">48</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=WEEKNUM(TEXT(A3,\"yyyy-mm-\")&1)*1");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center"></td><td>C3 His Formula Used This Cell -> C3:C37</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">5</td><td align="center">D3</td><td>=B3-C3+1</td><td align="right">5</td><td align="right"><form name="PrNames5"><input onclick='window.clipboardData.setData("Text","=B3-C3+1");' value="Do Copy" name="MyNames5" type="button"></form></td></tr><tr height="20"><td align="center">6</td><td align="center"></td><td>D3 His Formula Used This Cell -> D3:D37</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">7</td><td align="center">E3</td><td>=WEEKNUM(A3)-(WEEKNUM(TEXT(A3,"yyyy-mm-")&1)*1)+1</td><td align="right">5</td><td align="right"><form name="PrNames7"><input onclick='window.clipboardData.setData("Text","=WEEKNUM(A3)-(WEEKNUM(TEXT(A3,\"yyyy-mm-\")&1)*1)+1");' value="Do Copy" name="MyNames7" type="button"></form></td></tr><tr height="20"><td align="center">8</td><td align="center"></td><td>E3 His Formula Used This Cell -> E3:E37</td><td align="left"></td><td></td></tr><tr height="20"><td align="center">9</td><td align="center">F3</td><td>=WEEKDAY(A3)</td><td align="center">6</td><td align="right"><form name="PrNames9"><input onclick='window.clipboardData.setData("Text","=WEEKDAY(A3)");' value="Do Copy" name="MyNames9" type="button"></form></td></tr><tr height="20"><td align="center">10</td><td align="center"></td><td>F3 His Formula Used This Cell -> F3:F37</td><td align="left"></td><td></td></tr><tr height="24"><td colspan="14">
How about this suggest? </td></tr></tbody></table>

Thanks to duo for the reply and clarification.

barry houdini said:

Presumably the 1st of the month is always in week 1 and then week 2 starts from the first Friday after the 1st? If so then with date in A1 try this formula for the week number

Click to expand...

barry houdini said:

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

Click to expand...

Sorry to bring up old thread but what does 13 do here?
If 5 is for friday and 7 for the week number from 1 - 7

Please advice.

pedie said:

Sorry to bring up old thread but what does 13 do here?
If 5 is for friday and 7 for the week number from 1 - 7

Please advice.

Click to expand...

13 is to ensure the calculated day dived by 7 is between 1 and 2 so the INT returns 1 for the first week found.

Thanks to the original poster, but the problem I had was to deduce the week number including weeks numbers carried over from the previous month. ie If the first of the month was a Tuesday, then Tue 1st, Wed 2nd & Thu 3rd would be either week 4 or week 5 of the previous month, then Fri 4th would be week 1.

Without the original post I may not have solved this but I finally did, building on the same principle above.

=IF(INT((13+DAY(A1)-WEEKDAY(A1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=6,0,1)=0,INT((13+DAY(DATE(YEAR(A1),MONTH(A1),1)-1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)-1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,1))=6,0,1),INT((13+DAY(A1)-WEEKDAY(A1-5))/7)-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=6,0,1))

You can do that more easily with this formula

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

The 5 indicates the week start day, 5 = Friday in this case - change to 1 to 7 for (Monday to Sunday)