Excel/VBA Weekday Function
The Weekday function returns the number of day of the Week for the given date.
What is Weekday function in VBA?
The Weekday function returns the number of day of the Week for the given date.
For example, we want to know what is Weekday (Sat, Sun, Mon, etc.) for the date 1st Jan 2000.
We can pass this date to the Weekday() function and it returns the number of day as a whole number.
Syntax:
Weekday(date, [ firstdayofweek ])
The last section of tutorial shows the usage of firstdayofweek argument.
An example of using Weekday function in VBA
For the example, we are using the same date as given above i.e. 1st Jan 2001.
See what we get after using the Weekday function and displaying the returned value in a message box:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub Date_ex() Dim ChkDate, WkDay 'Checkign the date for number of day in the Week ChkDate = #1/1/2000# WkDay = Weekday(ChkDate) MsgBox ("Number of day for 1st Jan 2000: " & WkDay) End Sub |
Output:
How VBA treats the number of day?
Well, VBA has meaning for the return values as follows:
- Constant Value Description
- vbSunday 1 Sunday
- vbMonday 2 Monday
- vbTuesday 3 Tuesday
- vbWednesday 4 Wednesday
- vbThursday 5 Thursday
- vbFriday 6 Friday
- vbSaturday 7 Saturday
As we got 7 in our above example, so it was Saturday.
Using Select/Case statement in VBA to get day name for day number
In the following example, we are using VBA Select Case statement, so that a day name is displayed based on the given date.
As such, it may be hard to remember which number belongs to what day name.
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
Sub Date_ex() Dim ChkDate, WkDay 'Checkign the date for number of day in the Week ChkDate = #1/1/2000# WkDay = Weekday(ChkDate) Select Case WkDay Case 1 MsgBox "Sunday On: " & ChkDate Case 2 MsgBox "Monday On: " & ChkDate Case 3 MsgBox "Tuesday On: " & ChkDate Case 4 MsgBox "Wednesday On: " & ChkDate Case 5 MsgBox "Thursday On: " & ChkDate Case 6 MsgBox "Friday On: " & ChkDate Case 7 MsgBox "Saturday On: " & ChkDate Case Else MsgBox "No Way!" End Select End Sub |
Output:
Using second argument: firstdayofweek
If you want to change the first day of the week other than Sunday (default) then you may specify it by using the firstdayofweek argument.
It has the following possible values:
- vbUseSystem 0 Use the NLS API setting.
- vbSunday 1 Sunday (default)
- vbMonday 2 Monday
- vbTuesday 3 Tuesday
- vbWednesday 4 Wednesday
- vbThursday 5 Thursday
- vbFriday 6 Friday
- vbSaturday 7 Saturday
In the example below, we will make Monday as the first day of the Week.
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub Date_ex() Dim ChkDate, WkDay ChkDate = #1/1/2000# 'Making Mon as 1, Tue = 2,... Sat = 6 WkDay = Weekday(ChkDate, vbMonday) MsgBox ("Number of day for 1st Jan 2000: " & WkDay) End Sub |
Output:
You can see, we used the same code as in the first example except used the second argument and we got 6 for Saturday unlike 7 in the first example.
Using Excel cells to get the number of day in the week
To make things more clear about Weekday function and particularly, its second parameter, we will use a range of cells from A2 to A6 in an Excel sheet.
We have different date formats in the cells.
In VBA code, we used a For..Each loop. In each iteration, the Weekday() function is used to get the number of day.
The returned result is assigned to adjacent B, C, and D cells.
- B2:B6 = Sunday as 1
- C2:C6 = Monday as 1
- D2:D6 = Friday as 1
See the code and output below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Sub Date_ex() Dim Rng As Range Dim x 'Setting our range of cells in Excel Sheet Set Rng = Range("A2:A6") x = 2 'Using for each loop to get through each A2 to A6 cells and get the number of day For Each cell In Rng Range("B" & x) = Weekday(cell) Range("C" & x) = Weekday(cell, vbMonday) Range("D" & x) = Weekday(cell, vbFriday) x = x + 1 Next End Sub |
Output:
