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
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 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.
Read More: How to Convert Date to Week Number of Month in Excel
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.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Keep learning new tricks in Excel!
