Convert YYYYWW from string to date for time series functions

Maybe not exactly what you were hoping for, but I have had pretty good luck with these functions (VBA) over the years. I can't remember where I came across them, or I would give proper credit. But it might get you going int he right direction...

Rich P

Option Explicit

Type typDateBlock
    WeekNumber As Integer
    StartDate As Date
    EndDate As Date
End Type


Public NewDateBlock As typDateBlock

Public Function GetDateBlockByWeek(intWeekNum As Integer, intYearNum As Integer)

    With NewDateBlock
        .StartDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7)
        .EndDate = .StartDate + 6
        .WeekNumber = ISOWeekNum(.StartDate)
        
        Debug.Print .WeekNumber, .StartDate, .EndDate
    End With
     
End Function

Public Function GetDateBlockByDate(dteGivenDate As Date, Optional WhichFormat As Variant)
    Dim ThisYear As Integer
    Dim PreviousYearStart As Date
    Dim ThisYearStart As Date
    Dim NextYearStart As Date
    Dim intYearNum As Integer

    ThisYear = Year(dteGivenDate)
    ThisYearStart = YearStartDate(ThisYear)
    PreviousYearStart = YearStartDate(ThisYear - 1)
    NextYearStart = YearStartDate(ThisYear + 1)
    
    With NewDateBlock
        Select Case dteGivenDate
            Case Is >= NextYearStart
                .WeekNumber = (dteGivenDate - NextYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate) + 1
            Case Is < ThisYearStart
                .WeekNumber = (dteGivenDate - PreviousYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate) - 1
            Case Else
                .WeekNumber = (dteGivenDate - ThisYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate)
        End Select

        .StartDate = YearStartDate(intYearNum) + ((.WeekNumber - 1) * 7)
        
        .EndDate = .StartDate + 6
        
        Debug.Print .WeekNumber, .StartDate, .EndDate, intYearNum
    End With
     
End Function


Public Function YearStartDate(intYearNum As Integer) As Date
    Dim WeekDay As Integer, NewYear As Date
    
    NewYear = DateSerial(intYearNum, 1, 1)
    WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
    
    If WeekDay < 4 Then
        YearStartDate = NewYear - WeekDay
    Else
        YearStartDate = NewYear - WeekDay + 7
    End If

End Function
     
Public Function WeekStartDate(intWeekNum As Integer, intYearNum As Integer) As Date

    WeekStartDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7)

End Function

Public Function WeekEndDate(intWeekNum As Integer, intYearNum As Integer) As Date

    WeekEndDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7) + 6

End Function


Public Function ISOWeekNum(dteGivenDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
    Dim ThisYear As Integer
    Dim PreviousYearStart As Date
    Dim ThisYearStart As Date
    Dim NextYearStart As Date
    Dim YearNum As Integer
    
    ThisYear = Year(dteGivenDate)
    ThisYearStart = YearStartDate(ThisYear)
    PreviousYearStart = YearStartDate(ThisYear - 1)
    NextYearStart = YearStartDate(ThisYear + 1)
    
    Select Case dteGivenDate
        Case Is >= NextYearStart
            ISOWeekNum = (dteGivenDate - NextYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate) + 1
        Case Is < ThisYearStart
            ISOWeekNum = (dteGivenDate - PreviousYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate) - 1
        Case Else
            ISOWeekNum = (dteGivenDate - ThisYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate)
    End Select
    
    If IsMissing(WhichFormat) Then Exit Function
    
    If WhichFormat = 2 Then
        ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & Format(ISOWeekNum, "00"))
    End If

End Function