I have an excel list that includes different start and end date as you can see above. I would like to change the date format to yyyyww in VBA. Could you please advise me how to do it?

I have tried different formatting codes but couldn't find the right one for the week numbers.

CodePudding user response：

I've tried the formatting you proposed, but the ww was not recognised. I have found a formula, however, for generating the information you're looking for:

=YEAR(G2)&WEEKNUM(G2)

Good luck

CodePudding user response：

You probably need the ISO 8601 weeknumbering. For this, you can use my function FormatWeekIso8601 found in module DateText at GitHub: VBA.Date.

' Returns, for a date value, a formatted string expression with
' year and weeknumber according to ISO-8601.
' Optionally, a W is used as separator between the year and week parts.
'
' Typical usage:
'
'   FormatWeekIso8601(Date)
'   ->  2017-23
'
'   FormatWeekIso8601(Date, True)
'   ->  2017W23
'
' 2017-04-28. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatWeekIso8601( _
    ByVal Expression As Variant, _
    Optional ByVal WSeparator As Boolean) _
    As String
    
    Const Iso8601Separator  As String = "W"
    Const NeutralSeparator  As String = "-"
    
    Dim Result              As String
    
    Dim IsoYear As Integer
    Dim IsoWeek As Integer
    
    If IsDate(Expression) Then
        IsoWeek = Week(DateValue(Expression), IsoYear)
        Result = _
            VBA.Format(IsoYear, String(3, "0")) & _
            IIf(WSeparator, Iso8601Separator, NeutralSeparator) & _
            VBA.Format(IsoWeek, String(2, "0"))
    End If
    
    FormatWeekIso8601 = Result

End Function

It uses another function Week:

' Returns the ISO 8601 week of a date.
' The related ISO year is returned by ref.
'
' 2016-01-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Week( _
    ByVal Date1 As Date, _
    Optional ByRef IsoYear As Integer) _
    As Integer

    Dim Month       As Integer
    Dim Interval    As String
    Dim Result      As Integer
    
    Interval = IntervalSetting(dtWeek)
    
    Month = VBA.Month(Date1)
    ' Initially, set the ISO year to the calendar year.
    IsoYear = VBA.Year(Date1)
    
    Result = DatePart(Interval, Date1, vbMonday, vbFirstFourDays)
    If Result = MaxWeekValue Then
        If DatePart(Interval, DateAdd(Interval, 1, Date1), vbMonday, vbFirstFourDays) = MinWeekValue Then
            ' OK. The next week is the first week of the following year.
        Else
            ' This is really the first week of the next ISO year.
            ' Correct for DatePart bug.
            Result = MinWeekValue
        End If
    End If
        
    ' Adjust year where week number belongs to next or previous year.
    If Month = MinMonthValue Then
        If Result >= MaxWeekValue - 1 Then
            ' This is an early date of January belonging to the last week of the previous ISO year.
            IsoYear = IsoYear - 1
        End If
    ElseIf Month = MaxMonthValue Then
        If Result = MinWeekValue Then
            ' This is a late date of December belonging to the first week of the next ISO year.
            IsoYear = IsoYear   1
        End If
    End If
    
    ' IsoYear is returned by reference.
    Week = Result
        
End Function

Several constants are used well - too much to post here.