Tuesday, June 4, 2013

Week number display in VBA

Function GetPhilipsWeekYear(getDate As String)
Dim weeknumber As String
Dim YYYear As String
Dim mmmonth As String
Dim strDate As String

If getDate <> "" Then
' Getting the Week Number and Any format will convert to "mm-dd-yyyy"

strDate = Format(getDate, "mm-dd-yyyy")
weeknumber = Application.WorksheetFunction.WeekNum(strDate, 21)
' Getting the year from the date
YYYear = Year(getDate)
mmmonth = month(getDate)

If Len(weeknumber) = "1" Then
weeknumber = "0" & weeknumber
End If
            If ((weeknumber = "01") And (mmmonth = "12")) Then
            YYYear = YYYear + 1
            End If
            YYYear = Right(YYYear, 2)
            GetPhilipsWeekYear = YYYear & weeknumber
End If
End Function