Find WEEKNUM of the MONTH

[Parashar sarnad]

Hello Ladies & Gentlemen,

I'm unable to get my head around this WEEKNUM Solution which you had provided on Excel Campus by Jon Acompora.[TABLE="width: 579"]
<tbody>[TR]
[TD="class: xl65, width: 579"]http://www.excelcampus.com/pivot-tables/month-to-date-mtd-comparisons-pivot-table[/TD]
[/TR]
</tbody>[/TABLE]
So I shall be breaking it down so that you can point out where my understanding is wrong.

Please download the screenshot by using the link: [TABLE="width: 579"]
<tbody>[TR]
[TD="class: xl65, width: 579"]https://we.tl/PHHgxwe2EX[/TD]
[/TR]
</tbody>[/TABLE]

Let's consider a cell say "A84" which is 3rd FEB 2015. (Highlighted in Orange)

1) WEEKNUM(A84) will give a result as 6, which is 6th Week of the year 2015.
2) WEEKNUM(DATE(YEAR(A84), MONTH(A84),1),2) will give a result as 5, which is 5th Week of the year and is Monday as we have put 2 and also be starting of the Feb month
3) Now Subtracting 1)-2) and adding +1 will give a result 2, which is 2nd Week /*This is part I don't understand*/

Also if 1st Feb is Monday (considered as starting of the week) then how can 3rd Feb fall in the 2nd week of the month ??****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 579"]
<tbody>[TR]
[TD="class: xl65, width: 579"]https://we.tl/PHHgxwe2EX[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Ron Coderre]

Re: How to find WEEKNUM of the MONTH

With this data in A1:A52

RefData
Monday, December 01, 2014
Tuesday, December 02, 2014
Wednesday, December 03, 2014
Thursday, December 04, 2014
Friday, December 05, 2014
Saturday, December 06, 2014
Sunday, December 07, 2014
Monday, December 08, 2014
Tuesday, December 09, 2014
Wednesday, December 10, 2014
Thursday, December 11, 2014
Friday, December 12, 2014
Saturday, December 13, 2014
Sunday, December 14, 2014
Monday, December 15, 2014
Tuesday, December 16, 2014
Wednesday, December 17, 2014
Thursday, December 18, 2014
Friday, December 19, 2014
Saturday, December 20, 2014
Sunday, December 21, 2014
Monday, December 22, 2014
Tuesday, December 23, 2014
Wednesday, December 24, 2014
Thursday, December 25, 2014
Friday, December 26, 2014
Saturday, December 27, 2014
Sunday, December 28, 2014
Monday, December 29, 2014
Tuesday, December 30, 2014
Wednesday, December 31, 2014
Thursday, January 01, 2015
Friday, January 02, 2015
Saturday, January 03, 2015
Sunday, January 04, 2015
Monday, January 05, 2015
Tuesday, January 06, 2015
Wednesday, January 07, 2015
Thursday, January 08, 2015
Friday, January 09, 2015
Saturday, January 10, 2015
Sunday, January 11, 2015
Monday, January 12, 2015
Tuesday, January 13, 2015
Wednesday, January 14, 2015
Thursday, January 15, 2015
Friday, January 16, 2015
Saturday, January 17, 2015
Sunday, January 18, 2015
Monday, January 19, 2015
Tuesday, January 20, 2015

This formula, copied down, returns the Week of the Month for the referenced data:

B2: =NETWORKDAYS.INTL(EOMONTH(A2,-1-(WORKDAY.INTL(EOMONTH(A2,-1),1,"0111111")>A2)),A2,"0111111")

For that data, these are the results:

RefData                         WeekOfMth
Monday, December 01, 2014         1
Tuesday, December 02, 2014        1
Wednesday, December 03, 2014      1
Thursday, December 04, 2014       1
Friday, December 05, 2014         1
Saturday, December 06, 2014       1
Sunday, December 07, 2014         1
Monday, December 08, 2014         2
Tuesday, December 09, 2014        2
Wednesday, December 10, 2014      2
Thursday, December 11, 2014       2
Friday, December 12, 2014         2
Saturday, December 13, 2014       2
Sunday, December 14, 2014         2
Monday, December 15, 2014         3
Tuesday, December 16, 2014        3
Wednesday, December 17, 2014      3
Thursday, December 18, 2014       3
Friday, December 19, 2014         3
Saturday, December 20, 2014       3
Sunday, December 21, 2014         3
Monday, December 22, 2014         4
Tuesday, December 23, 2014        4
Wednesday, December 24, 2014      4
Thursday, December 25, 2014       4
Friday, December 26, 2014         4
Saturday, December 27, 2014       4
Sunday, December 28, 2014         4
Monday, December 29, 2014         5
Tuesday, December 30, 2014        5
Wednesday, December 31, 2014      5
Thursday, January 01, 2015        5
Friday, January 02, 2015          5
Saturday, January 03, 2015        5
Sunday, January 04, 2015          5
Monday, January 05, 2015          1
Tuesday, January 06, 2015         1
Wednesday, January 07, 2015       1
Thursday, January 08, 2015        1
Friday, January 09, 2015          1
Saturday, January 10, 2015        1
Sunday, January 11, 2015          1
Monday, January 12, 2015          2
Tuesday, January 13, 2015         2
Wednesday, January 14, 2015       2
Thursday, January 15, 2015        2
Friday, January 16, 2015          2
Saturday, January 17, 2015        2
Sunday, January 18, 2015          2
Monday, January 19, 2015          3
Tuesday, January 20, 2015         3

Does that give you what you need?

 

[Joe4]

Re: How to find WEEKNUM of the MONTH

Welcome to the Board!

I'm unable to get my head around this WEEKNUM Solution which you had provided on Excel Campus by Jon Acompora.

Not sure what you mean by "you" in the statement above. The link your referenced is from "excelcampus.com", which has no affiliation with "mrexcel.com".

Regardless, I hope Ron's explanation gives you what you were looking for.

 

[Parashar sarnad]

Re: How to find WEEKNUM of the MONTH

Oops! Apologies Sir. I forgot to delete or correct the line.
Thank you for bringing it to my notice. I will make sure to proofread the sentence I write before posting it.

 

[Joe4]

Re: How to find WEEKNUM of the MONTH

Oops! Apologies Sir. I forgot to delete or correct the line.
Thank you for bringing it to my notice. I will make sure to proofread the sentence I write before posting it.

No worries! I just wanted to clarify that you post you referenced did not come from our site.