The day of the week becomes worthwhile for planning, scheduling, and even financial analysis. In a previous article, I discussed how to use the WEEKDAY function in Excel. But in today’s talks, I am presenting you with an outstanding alternative to get the day of the week using the VBA code.
Firstly, I’ll demonstrate the application of the VBA Weekday function, and then the usage of the WeekdayName function also with their formula and syntax. After that, you’ll show how to get the next weekend for a specific date. Lastly, how to display the weekday using VBA MsgBox will be discussed.
Download Practice Workbook
How to Find Day of Week Using VBA
This is our dataset where some employee names are provided with their joining date. Now we’ll get the day of the week for the joining date.
1. Finding the Day of Week Using the Weekday Function
Before using the VBA Weekday function, let’s have a glimpse of its basics.
Function Objective
Finds the day of the week as a number (integer)
Syntax
Weekday(date, [ firstdayofweek ])
Arguments Explanation
Argument | Required/Optional | Explanation |
---|---|---|
date | Required | The value of date in any format from which you want to get the day of the week. |
firstdayofweek | Optional | A constant value that defines the first day of the week (1 for vbSunday is the default value). |
Note:
The firstdayofweek argument has following settings:
VBA Constant | Value | Explanation |
---|---|---|
vbUseSystem |
0 | Use the National Language Support(NLS) API setting |
vbSunday | 1 | Starting day of the week is Sunday (default) |
vbMonday | 2 | Starting day of the week is Monday |
vbTuesday | 3 | Starting day of the week is Tuesday |
vbWednesday | 4 | Starting day of the week is Wednesday |
vbThursday | 5 | Starting day of the week is Thursday |
vbFriday | 6 | Starting day of the week is Friday |
vbSaturday | 7 | Starting day of the week is Saturday |
Now, let’s see how you can apply the VBA Weekday function to get the day of the week.
Step 1:
Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.
Step 2:
Then, copy the following code in your module (module 1 in the practice workbook).
Sub VBA_Weekday()
Range("D5").Value = Weekday(Range("C5"))
Range("D6").Value = Weekday(Range("C6"))
Range("D7").Value = Weekday(Range("C7"))
Range("D8").Value = Weekday(Range("C8"))
Range("D9").Value = Weekday(Range("C9"))
Range("D10").Value = Weekday(Range("C10"))
Range("D11").Value = Weekday(Range("C11"))
Range("D12").Value = Weekday(Range("C12"))
End Sub
Step 3:
Finally, run the code.
Notice:
The following things are essential in the above VBA code.
- Worksheet name: Here, the worksheet name is “VBA_Weekday”
- Logic: The Weekday function returns the day of the week.
- Input Cell: Here, the input cell is C5, C6, etc.
- Output range: The output range is D5, D6, etc.
The output will look like this.
Read more: How to Use the Day Function in Excel VBA
2. Obtaining the Day of Week Using WeekdayName Function
We found the day of the week using the VBA Weekday function in the first method, now we’ll see how to get the name of that day of the week. In such a situation, we need to utilize the WeekdayName function.
Function Objective
Returns the name of the day of the week.
Syntax
WeekdayName(Weekday, [Abbreviate], [FirstDayOfWeek])
Arguments Explanation
Argument | Required/Optional | Explanation |
---|---|---|
weekday | Required | The value of date in any format from which you want to get the day of the week |
abbreviate | Optional | A boolean value that defines abbreviated name (Use ‘True’ eg Sun, Tue) or full name (Use ‘False’ eg Sunday, Tuesday) |
firstdayofweek | Optional | A constant value that defines the first day of the week (1 for vbSunday is the default value) |
Now, let’s see how you can apply the VBA WeekdayName function to get the name of the day of the week.
Step 1:
Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.
Step 2:
Then, copy the following code in your module (module 3 in the practice workbook).
Sub VBA_WeekdayName()
Range("E5").Value = WeekdayName(Range("D5"), "True", vbMonday)
Range("E6").Value = WeekdayName(Range("D6"), "True", vbMonday)
Range("E7").Value = WeekdayName(Range("D7"), "True", vbMonday)
Range("E8").Value = WeekdayName(Range("D8"), "True", vbMonday)
Range("E9").Value = WeekdayName(Range("D9"), "True", vbMonday)
Range("E10").Value = WeekdayName(Range("D10"), "True", vbMonday)
Range("E11").Value = WeekdayName(Range("D11"), "True", vbMonday)
Range("E12").Value = WeekdayName(Range("D12"), "True", vbMonday)
End Sub
Step 3:
Finally, run the code.
Notice:
The following things are essential in the above VBA code.
- Worksheet name: Here, the worksheet name is “VBA_WeekdayName”
- Logic: The WeekdayName function returns the name of the day of the week.
- Input Cell: Here, the input cell is D5, C6, etc.
- Output range: The output range is E5, E6, etc.
After running the code, the output will be as follows.
Read more: Excel VBA to Find Week Number
Similar Readings
- Sort Dates in Excel by Year (4 Easy Ways)
- How to Convert Date from String Using VBA (7 Ways)
- Use Excel Date Shortcut
- How to Use IF Formula with Dates (6 Easy Examples)
3. Getting the Next Weekend Date Using VBA Weekday Function
At this moment, we’ll explore an interesting thing, that is, to get the next weekend from the joining date utilizing the VBA Weekday function
Just follow the steps
Step 1:
Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.
Step 2:
Then, copy the following code in your module (module 4 in the practice workbook).
Sub VBA_WeekendDate()
Dim k As Integer
For k = 5 To 12
If Weekday(Cells(k, 2).Value, vbMonday) = 1 Then
Cells(k, 3).Value = Cells(k, 2) + 5
ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 2 Then
Cells(k, 3).Value = Cells(k, 2) + 4
ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 3 Then
Cells(k, 3).Value = Cells(k, 2) + 3
ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 4 Then
Cells(k, 3).Value = Cells(k, 2) + 2
ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 5 Then
Cells(k, 3).Value = Cells(k, 2) + 1
Else
Cells(k, 3).Value = "This is actually the weekend Date"
End If
Next k
End Sub
Step 3:
Finally, run the code.
Notice:
The following things are essential in the above VBA code.
- Worksheet name: Here, the worksheet name is “VBA_WeekendDate”
- Logic: The WeekdayName function returns the name of the day of the week.
You’ll see the following output.
4. Displaying The Day of Week Using VBA MsgBox
Though this is not an individual method, it can be used to display the day of the week utilizing VBA MsgBox.
Step 1:
Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.
Step 2:
Then, copy the following code in your module (module 5 in the practice workbook).
Sub VBA_WeekDayDisplay()
'Variable declaration
Dim dDate As Date
Dim iWkDay As Integer
dDate = "1/10/2022"
iWkDay = Weekday(dDate)
MsgBox "The Weekday of " & dDate & " is : " & iWkDay, vbInformation, "VBA Weekday Function"
End Sub
Step 3:
Finally, run the code.
Then you’ll see the following box where the output is 2.
Things to Remember
If the specified date is not a valid date, the VBA Weekday function returns the Run-time error ‘13’.
Conclusion
This is how you might get the day of the week using VBA in Excel. I firmly believe that the above methods will enrich your Excel learning. However, if you have any queries or suggestions, please leave them below in the comments section.