Return the 'week of the month' by the month

Question

Does anybody have a function to return the "week of the month" by the month? Most of the functions searched start the week on the 1st. I would like to go by the week, i.e 1st March 2018 will be week 5 of February. Week 1 of March starts on the 4th of March.

How can I do that?

Possible duplicate of [Get the number of the week of the month from a given date on VBA](https://stackoverflow.com/questions/21690077/get-the-number-of-the-week-of-the-month-from-a-given-date-on-vba) — K.Dᴀᴠɪs, Mar 08 '18 at 05:54
no, the return for 1 March is week 1 for the function, i would like it to return 5 (same week as 28 Feb) — Richard Low, Mar 08 '18 at 06:51

score 0 · Accepted Answer · answered Mar 08 '18 at 07:21

First find the previous Sunday of the date of the month as this date could fall in the previous month:

DateAdd("d", 1 - Weekday(DateOfMonth), DateOfMonth)

Then use this generic function to find the first Sunday of the month:

' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
    ByVal DateInMonth As Date, _
    Optional ByVal Occurrence As Integer, _
    Optional ByVal Weekday As VbDayOfWeek = -1) _
    As Date

    Const DaysInWeek    As Integer = 7

    Dim Offset          As Integer
    Dim Month           As Integer
    Dim Year            As Integer
    Dim ResultDate      As Date

    ' Validate Weekday.
    Select Case Weekday
        Case _
            vbMonday, _
            vbTuesday, _
            vbWednesday, _
            vbThursday, _
            vbFriday, _
            vbSaturday, _
            vbSunday
        Case Else
            ' Zero, none or invalid value for VbDayOfWeek.
            Weekday = VBA.Weekday(DateInMonth)
    End Select

    ' Validate Occurence.
    If Occurrence <= 0 Then
        Occurrence = 1
    ElseIf Occurrence > 5 Then
        Occurrence = 5
    End If

    ' Start date.
    Month = VBA.Month(DateInMonth)
    Year = VBA.Year(DateInMonth)
    ResultDate = DateSerial(Year, Month, 1)

    ' Find offset of Weekday from first day of month.
    Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
    ' Calculate result date.
    ResultDate = DateAdd("d", Offset, ResultDate)

    If Occurrence = 5 Then
        ' The latest occurrency of Weekday is requested.
        ' Check if there really is a fifth occurrence of Weekday in this month.
        If VBA.Month(ResultDate) <> Month Then
            ' There are only four occurrencies of Weekday in this month.
            ' Return the fourth as the latest.
            ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
        End If
    End If

    DateWeekdayInMonth = ResultDate

End Function

Finally, assemble these and use DateDiff to obtain the count of Sundays and add 1 (one) to obtain the weeknumber:

MonthWeekNumber = 1 + DateDiff("w", DateWeekdayInMonth(DateAdd("d", 1 - Weekday(DateOfMonth), DateOfMonth), 1, vbSunday), DateOfMonth)

Hi Gustav Works like a charm. Your comment for finding the first Sunday of the date of the month. Should I create a variable for that and where should I use the variable? Seems to be working very well when I use the MonthWeekNumber. And many thanks for your help... — Richard Low, Mar 09 '18 at 01:28
Sorry, finding the first Sunday is already in the MonthWeekNumber. Thanks again — Richard Low, Mar 09 '18 at 01:50

Return the 'week of the month' by the month

1 Answers1