Convert Date Into Week Number In VBA

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.

Open developer tab to write VBA to convert a date into week number

Step 2

Insert a module in which we will write a VBA code to convert date into week number.

Insert a module in the VB editor

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

VBA code showing how to get week number from date

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.

Result showing week number

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

vba code converting date variable to week number in the year


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.

Share The Knowledge

Random Posts