Get week number for month from date

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
I want to calculate week number for a month based on a date and the weekday will start from Friday.
Suppose the date is 19/08/08 then the week will be 3
If date is 22/08/08 then week shall be 4
If date is 04/09/2008 then week shall be 1
If date is 11/09/2008 then week shall be 2

I tried to get the result by googling but not found any appropriate result.

Can some expert provided the formula ?
Thanks in advance.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
Thanks barry. its working correctly. would you kindly explain the logic of the formula?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 
Last edited:

theozz

Active Member
Joined
Jun 11, 2007
Messages
328

ADVERTISEMENT

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>
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875

ADVERTISEMENT

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)


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.
 

scot66

New Member
Joined
Sep 14, 2009
Messages
3
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.

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.
 

scot66

New Member
Joined
Sep 14, 2009
Messages
3
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))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,560
Messages
5,548,737
Members
410,869
Latest member
eSoftToolsNSFtoPSTConvert
Top