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

  • Create Rows From Comma Separated Cell Values In Excel

    create rows from comma separated values .in excel, split comma separated values and create rows from each value in excel, break comma separated string in a cell into rows, create rows from comma separated text string in excel

    Share The Knowledge
  • Create Checklist In Excel

    Let’s see how to create a checklist in excel to know the current status of the action items. Below checklist […]

    Share The Knowledge
  • Count Number Of Weekdays From Date Column In Excel

    This post demonstrates how to calculate number of weekdays in a given date values in excel. Suppose we have a […]

    Share The Knowledge
  • Arabic Function In Excel

    Let’s see how to use Arabic function in excel. Arabic function was introduced in excel 2013. Arabic function is used […]

    Share The Knowledge