Using Excel VBA to Find a Week Number - 6 Examples

Example 1 – Transform a Date into a Week Number

The following dataset contains random dates, including the day. To find the week number:

STEPS:

Go to the Developer Tab and select Visual Basic. This will open the visual basic editor.

In Insert, select Module.

You can also open the visual basic editor by right-clicking the sheet and going to View Code.

Use the VBA Code.

VBA Code:

Sub date_to_weekNumber()
For i = 1 To Range("D5:D9").Rows.Count
    For j = 1 To Range("D5:D9").Columns.Count
        Range("D5:D9").Cells(i + 1, j).Value = Int((Range("B5:B9").Cells(i + 1, j) - Range("B5:B9").Cells(1, j)) / 7) + 2
    Next j
Next i
End Sub

Click Run or press F5 to run the code.

The number of weeks numbers is displayed in column B.

Example 2 – Using VBA to get the Week Number from a Date

Use the CDATE function.

STEPS:

Go to the Developer Tab and select Visual Basic.

In Insert, select Module or right-click the sheet and select View Code.

Use the VBA Code.

VBA Code:

Sub WeekNumber()
    Dim d As Date
    Dim week As Long
    d = CDate("2 / 1 / 2022")
    week = Application.WorksheetFunction.WeekNum(d)
    MsgBox week
End Sub

Click Run or press F5 to run the code.

“2/1/2022” is the 6th week of the year.

Example 3 – Get All the Weeks in a Month Using VBA

STEPS:

Go to the Developer Tab and select Visual Basic.

In Insert, select Module or right-click the sheet and select View Code.

Use the VBA Code.

VBA Code:

Sub WeeksInMonth()
    Dim MonthYear As String, txt As String
    Dim d As Date, MonthYearDay As Date
    Dim i As Long, intDaysInMonth As Long, j As Long
    Dim MyArray As Variant
    Dim arr As New Collection, a
    ReDim MyArray(0 To 31)
    j = 0
    d = Now
    MonthYear = month(d) & "/" & Year(d)
    intDaysInMonth = Day(DateSerial(Year(MonthYear), month(MonthYear) + 1, 0))
    For i = 1 To intDaysInMonth
        MonthYearDay = DateSerial(Year(d), month(d), i)
        MyArray(j) = Application.WorksheetFunction.WeekNum(MonthYearDay)
        j = j + 1
    Next i
    ReDim Preserve MyArray(0 To j - 1)
    On Error Resume Next
    For Each a In MyArray
        arr.Add a, CStr(a)
    Next
    For i = 1 To arr.Count
        MsgBox arr(i)
    Next
End Sub

Click Run or press F5 to run the code.

This message box shows the first week number.

This message box displays the second week.

This message box shows the third week.

Up to 6 weeks will be displayed.

Example 4 – Using the Excel WEEKNUM Function in VBA

Use the WEEKNUM function. It returns the week number in a year (1-52).

STEPS:

Go to the Developer Tab and select Visual Basic.

In Insert, select Module or right-click the sheet and select View Code.

Use the VBA Code.

VBA Code:

Sub WeekNum()
Range("D5") = Application.WeekNum(Range("B5"))
Range("D6") = Application.WeekNum(Range("B6"))
Range("D7") = Application.WeekNum(Range("B7"))
Range("D8") = Application.WeekNum(Range("B8"))
Range("D9") = Application.WeekNum(Range("B9"))
Range("D10") = Application.WeekNum(Range("B10"))
End Sub

Click Run or press F5 to run the code.

The result is displayed in column D.

Example 5 – Find the First and Last Day in a Week

STEPS:

Go to the Developer Tab and select Visual Basic.

In Insert, select Module or right-click the sheet and select View Code.

Use the VBA Code.

VBA Code:

Sub First_Last_Weekday()
Dim FirstWeekday, LastWeekday  As Variant
Dim d As Date
d = "15/01/2022"
FirstWeekday = d - Weekday(d, vbUseSystem) + 1
MsgBox FirstWeekday
LastWeekday = d - Weekday(d, vbUseSystem) + 7
MsgBox LastWeekday
End Sub

Click Run or press F5 to run the code.

The result is displayed in the message box: the first day of the week.

The last day of the week:

Example 6 – Using the Excel ISO Week Number in VBA

There are 52 or 53 full weeks in an ISO week-numbering year (sometimes known as an ISO year informally). Instead of the typical 365 or 366 days, there will be 364 or 371 days. All years with Thursday as the 1st of January, as well as leap years that begin on Wednesday the 1st, have these 53-week years. Find the ISO week number:

STEPS:

Go to the Developer Tab and select Visual Basic.

In Insert, select Module or right-click the sheet and select View Code.

Use the VBA Code.

VBA Code:

Sub ISO_Week()
myDate = "2022-1-31"
ISOWeek = DatePart("ww", myDate, vbMonday, vbFirstFourDays)
Week1 = DatePart("ww", myDate, vbMonday, vbFirstFourDays)
Week2 = DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)
ISOYear = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)
Year1 = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)
Year2 = DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)
If ISOWeek = 53 And DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) = 2 Then
   ISOWeek = 1
End If
If ISOWeek = 1 And DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) > ISOYear Then
 ISOYear = ISOYear + 1
End If
MsgBox ("W" & ISOWeek)
End Sub