Get the Week number from a date or Week Beginning

[sagarwal]

As there are 4-5 weeks in a month, so i want to get the Week number ranking from 1 to 5 in that week. There is one condition to it, if there are 5 weeks in a month, then the week number should be considering the Count of Days in that week for that particular month.
For Ex- in Sep'15, there are 5 weeks. 1st week starts with 31st Aug'15, so that should be considered as Week 1 for Sep'15, as there is only one day of Aug falling in that week..Had it been more than 3 days, then it should have been considered as week 5 for Aug'15. For Ex - In WB 28th Sep'15, as there are 3 days falling in that week for Sep'15 (more than 2 days), then I want to be considered as week 5 in Sep'15. This should be the condition with all the weeks in a give year.


I have tried the following formula but not getting the desired result
<code>WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1</code>
<code>Week Beginning Week no.
31-Aug'15 1
7-Sep'15 2
14-Sep'15 3
21-Sep'15 4
28-Sep'15 5
</code>

 

[James006]

Hi,

You could test : =INT((13+DAY(A1)-WEEKDAY(A1-5))/7)

HTH

 

[sagarwal]

James, Thank you for your reply,,Formula which you hve suggested does not solve the purpose..

On 31st May'16, this formula gave me Week no . 5, but I want 31st May to be week 1 for June

This is because the no of days in WB 31st May is less than 3 days....please help..