How to get the Week of the month (1:6)-CodePudding

We can find different approaches to determining the week of the month, and even though there are many pages on 1:4 and/or 1:5, there is very few around 1:6 approach.

So to give you a bit of the context, I am working with a pivot table in Excel which gets its values from a Power Query source. In Power Query, there is a function Date.WeekOfMonth which takes in the date and returns a number between 1 and 6. In this definition, weeks start from Sunday and ends on Saturday. So, for example, the first two days of October 2021 -i.e. Fri & Sat- fall in the 1st week of Oct, while the 3rd day of Oct 2021 starts the second week, and then the last day of October 2021,i.e. Oct 31, is the only day in the 6th week.

I had an automation task on hand in which I needed to pull data from the Power Query-generated pivot, so I had to implement a piece of code in VBA which calcs weeks the same. Unfortunately, I couldn't find any prepared snippet, so after implementing I though it might worth sharing. (Any comments and suggestions appreciated)

CodePudding user response：

The DatePart function is perfect for this. Using DatePart with the interval set to "weeks" you can find the week of a given date. Then you subtract the number of weeks before the first day of that month (which sets the first day to week = 1).

Function WeekNumOfDate(D As Date) As Integer
    WeekNumOfDate = DatePart("ww", D) - DatePart("ww", DateSerial(Year(D), Month(D), 1))   1
End Function

Here is a second version of the function that has the ability to set the FirstDayOfWeek argument:

Function WeekNumOfDate(D As Date, Optional FirstDayOfWeek As VbDayOfWeek = vbSunday) As Integer
    WeekNumOfDate = DatePart("ww", D, FirstDayOfWeek) - DatePart("ww", DateSerial(Year(D), Month(D), 1), FirstDayOfWeek)   1
End Function

As an example for using FirstDayOfWeek: With FirstDayOfWeek set to vbThursday, the date "Nov 5th, 2021" will return as Week 2, whereas it would by default be counted as Week 1. November 1st to 3rd of 2021 will be week 1, and then 4th to 10th will be week 2.

CodePudding user response：

Its implementation in VBA is:

Function WeekOfMonth(My_Date As Date)
   
If Day(My_Date) > Day(My_Date - 1) And Weekday(My_Date) > Weekday(My_Date - 1) Then
        
    WeekOfMonth = WeekOfMonth(My_Date - 1)
    
ElseIf Day(My_Date) > Day(My_Date - 1) And Weekday(My_Date) < Weekday(My_Date - 1) Then
        WeekOfMonth = WeekOfMonth(My_Date - 1)   1

Else
    WeekOfMonth = 1
    
End If
End Function

Note that even though the above function is recursive, its time and space complexity is an expression of order N, which here cannot exceed 31.