Sabrina Ayon
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.
Download Practice Workbook
You can download the workbook and practice with them.
6 Examples of VBA Week Number
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 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 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 is carrying 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.
Read more: VBA Date to String Conversion in Excel
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.
Read more: How to Calculate Due Date with Formula in Excel
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 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
arr.Add a, CStr(a)
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 is showing 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.
Read more: How to Sort Dates in Excel by Year
Similar Readings
- How to Use Year Function in Excel VBA (5 Suitable Examples)
- Use Excel VBA MONTH Function (7 Suitable Examples)
- How to Use EoMonth in Excel VBA (5 Examples)
- Use the VBA DatePart Function in Excel (7 Examples)
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.
Read more: How to Insert Current Date in Excel
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.
Read more: How to Get the Day of Week Using VBA
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.
Notes
- Before Excel 2010, there was no standard worksheet function for the ISO week number, and there was also no standard worksheet function for the basic week numbering system.
- WEEKNUM is a standard worksheet function in Excel 2007 and later, thus you won’t have the issues mentioned above if you share your workbook between multiple Excel 2007-2013 language versions and the add-in is unchecked.
- Simple week numbering does not have a built-in worksheet function in Excel.
Conclusion
Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
