# Excel VBA to Find Week Number (6 Quick Examples)

Get FREE Advanced Excel Exercises with Solutions!

Most of us have learned that a year has 52 weeks. There are at least six different week numbering systems currently in use around the world. Week Number refers to the first complete week of the calendar year, which is Week No.1, and continues in numerical order until Week No. 52. In this article, we will use Excel VBA to find the week number.

## How to find Week Number with Excel VBA: 6 Examples

There are several basic week numbering systems in use around the world. We should be aware of the tiny distinctions between each system. Any of these systems can be used with Excel. Simple week numbering, ISO Week, Excel’s the WEEKNUM function. In this article, we will look at some examples of week numbering and find the week number using Excel VBA.

### 1. Transform Date to Week Number

In Excel, we can easily convert date to week number by using a formula or any function or we can do it manually. Now, let’s have a look at how it would be done using VBA. For this, we are going to use the following dataset which contains some random dates and the day of those dates. We will find the week number or those random dates. In the picture below, column B carries the random dates, column C holds the day of those random days and in column D, we will put the week number using Excel VBA macros. So, let’s see how it works. To transform the date into a week number we have to follow some steps below.

STEPS:

➤ First, go to the Developer Tab and then select Visual Basic. This will open the visual basic editor. ➤ Now, the visual basic window will appear where we are going to write our VBA codes.

➤ Then, click the Insert drop-down and select Module. This will insert a new module window. ➤ Or, we can open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code. ➤ After that, just write down 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`````` ➤ Finally, Run the code or press the keyboard shortcut F5 to run the code.

➤ And in the end, we can see our desired result of the week’s numbers in column B. ### 2. VBA to Acquire Week Number from Date

Week numbers refer to the number of weeks in a year. Below we will find how to find the week number from a date. For this, we will input a date with the CDATE function. After that, we will find which week number the date belongs to. And the result will show up in a msg box. So, give the go-ahead to see how to do the task.

STEPS:

By the same token as the above methods, go to the Visual Basic editor by right-clicking on the worksheet > Click on View Code. ➤ Now, write down the code here.

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`````` ➤ In the end, press F5 and run the code.

➤ Finally, we can see the result. “2/1/2022” is the 6th week of the year. ### 3. Get All Weeks in a Month Using VBA

Likewise, with the above examples, we will find the week’s numbers but in a different way. Now, we will input month and year and return all week numbers for this month.

STEPS:

➤ In the beginning, go to the Developer tab > Visual Basic. Then, the visual basic editor will show up.

Insert > Module. Or, right-click on the sheet and select View Code. ➤ After that, just write down the VBA Code here.

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
Next
For i = 1 To arr.Count
MsgBox arr(i)
Next
End Sub`````` ➤ At last, Run the code by pressing the F5 or play button.

➤ Finally, we will see the msg box with all the week numbers for that particular month.

This message box shows the 1st-week number. This message box displays the month’s second week. This msg box shows the third week of the month. Like those, up to 6 weeks will show in the msg box.

### 4. Excel WEEKNUM Function in VBA

The WEEKNUM Function returns the week number in a year (1-52). The WEEKNUM Function is a DATE and TIME function in Excel. It will provide us with the week number for a given date. The function will return an integer corresponding to a week number ranging from 1 to 52 weeks per year. We can use the WEEKNUM function in Excel VBA to get the week number. We are going to use the same dataset as before.

STEPS:

➤ Sequentially, go to Developer tab > Visual Basic > Insert > Module. Or, right-click on the sheet > View Code. ➤ After that, write down 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`````` ➤ Finally, press F5 or the play button to run the code. And the result is in column D. ### 5. Appear First Day and Last Day in Week

With Excel VBA, we can find the first day and the last day of the week.

STEPS:

➤ Correspondingly to the earlier methods, go to the View Code by right-clicking on the worksheet. ➤ Then, copy and paste the below 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`````` ➤ Now, run the code by pressing F5 or clicking the play button.

➤ And finally, we will see the result in the message box. This msg box shows the first day of the week. ➤ This msg box shows the last day of the week. ### 6. 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. We can also find the ISO week number in Excel using VBA macros.

STEPS:

➤ First, go to View Code by right-clicking on the worksheet. ➤ After that, write down the VBA code here.

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`````` ➤ Then, run the code by clicking on the play button or using the keyboard shortcut F5.

➤ And finally, we can see the result in the msg box. ## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  