In this post we will see how to convert a date into week number in VBA, excel has an inbuilt function WEEKNNUM but it can’t be used in VBA context. .
Our aim is to create vba code that will give us week number based on the argument that we pass in the code
We will use two example to undnerstand how could we get week number from a date variable in VBA, first by using today’s date and in second method we will pass a date variable find its week number.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to convert date into week number.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub find_week_number()
Week_Num = WorksheetFunction.WeekNum(Now, vbMonday)
MsgBox “Today’s week number is ” & Week_Num
End Sub
In this code we are providing today’s date and converting date into week number and the week number will be displayed in a message box.
Step 4
Now we will convert a date into week number of the year by providing a date variable in the program.
Paste below code to pass a date into VBA code to convert it into week number.
Sub find_week_number2()
Week_Num = WorksheetFunction.WeekNum(DateSerial(2021, 11, 11), vbMonday)
End Sub
DateSerial function takes year, month and date as argument and create a date variable and the week number is stored in the variable Week_Num, you can pass date variable by modifying the program.
Hope this helped.
