The Weekday function is one of the most important and widely used functions of VBA. In this article, I’ll show you how you can use the VBA Weekday function with proper examples and illustrations.
VBA Weekday Function (Quick View)
When you run it, it’ll return 7, because the date “1/1/2022” was Saturday, and considering Sunday as the 1st day of the week, the number of Saturday is 7.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
An Introduction to the VBA Weekday Function
⧭ Overview:
The Weekday function in VBA takes a date and a number representing the 1st day of the week as the input and returns a number representing the weekday of the date as the output.
For example, Weekday (“1/1/2022”,1) = 7 (The date “1/1/2022” is Saturday and the weekday of Saturday is 7 considering Sunday as the 1st day.)
⧭ Syntax:
Therefore, the syntax of the Weekday function is:
=Weekday(Date,[FirstDayOfWeek = vbSunday])
⧭ Arguments:
Argument
Required / Optional
Explanation
Date
Required
The date on which the weekday will be returned.
FirstDayOfWeek
Optional
Denotes the first day of the week. The default is Sunday.
The argument FirstDayofWeek can contain a maximum of 8 values. Each represents a specific weekday as the 1st day of the week.
FirstDayofWeek | Corresponding 1st Weekday |
---|---|
0 | NLS API Settings |
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
⧭ Return Value:
Returns a number representing the weekday of the given date.
2 Examples to Use the VBA Weekday Function
Here are a few examples to learn to use the VBA Weekday function in detail.
Example 1: Getting the Weekday Name from a Given Date using the VBA Weekday Function
First of all, we’ll use the VBA Weekday function to get the weekday name from a given date.
First, we’ll get the date from the user as input.
Input_Date = InputBox("Enter the Date: ")
Next, we’ll use the VBA Weekday function to get the weekday number of the date, considering Sunday as the 1st day.
Weekday_Number = Weekday(Input_Date)
Next, we’ll need to extract the name of the weekday from the Weekday_Number using the WeekdayName function.
Week_day = WeekdayName(Weekday_Number)
Finally, we’ll display the Week_day with a Message Box.
MsgBox Week_day
So the complete VBA code will be:
⧭ VBA Code:
Sub Getting_Weekday_from_Date()
Input_Date = InputBox("Enter the Date: ")
Weekday_Number = Weekday(Input_Date)
Week_day = WeekdayName(Weekday_Number)
MsgBox Week_day
End Sub
⧭ Output:
Run the code. First, it’ll ask the user to enter a date.
Here I’ve entered 2/2/2022.
Then click OK. It’ll return the name of the weekday of the date. Here it’s Wednesday.
Read More: How to Use VBA WeekdayName Function in Excel (2 Examples)
Similar Readings:
- How to Use VBA Replace Function in Excel (11 Applications)
- How to Use VBA InstRev Function (7 Suitable Examples)
- VBA Date Function (12 Uses of Macros with Examples)
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
- How to Use VBA SPLIT Function in Excel (5 Examples)
Example 2: Finding Out whether a Given Date is a Weekend or Not Using the VBA Weekday Function
Now we’ll develop a Macro using the Weekday function to find out whether a given date is a weekend or not.
First, we’ll ask the user to enter the given date.
Input_Date = InputBox("Enter the Date: ")
Then we’ll again ask the user to enter the weekends of his / her region separated by commas.
Weekends=InputBox("Enter the Weekends of Your Region (Separated by Commas): ")
Next, we’ll use the Split function to convert the string Weekend to an array.
Weekends=Split(Weekends,",")
Then we’ll use the Weekday function to get the weekday number of the given date.
Weekday_Number = Weekday(Input_Date)
Then we’ll convert the weekday number into a weekday name using the VBA WeekdayName function.
Weekday_Name = WeekdayName(Weekday_Number)
Finally, we’ll check whether the weekday name is equal to any of the weekends or not.
For i = 0 To UBound(Weekends)
If Weekends(i) = Weekday_Name Then
MsgBox "It's a Weekend."
Exit For
End If
Next i
So the complete VBA code will be:
⧭ VBA Code:
Sub Weekend_or_Not()
Input_Date = InputBox("Enter the Date: ")
Weekends = InputBox("Enter the Weekends of Your Region (Separated by Commas): ")
Weekends = Split(Weekends, ",")
Weekday_Number = Weekday(Input_Date)
Weekday_Name = WeekdayName(Weekday_Number)
For i = 0 To UBound(Weekends)
If Weekends(i) = Weekday_Name Then
MsgBox "It's a Weekend."
Exit For
End If
Next i
End Sub
⧭ Output:
Run the code. First, it’ll ask you to enter the date. Here I’ve entered 2/5/2022.
Then it’ll ask you to enter the weekends of your region, separated by commas. Here I’ve entered Saturday, Sunday.
Then click OK. A message box will display “It’s a weekend.” because the date 2/5/2022 is Saturday.
Related Content: How to Use VBA DateDiff Function in Excel (9 Examples)
More Reading
The WeekdayName is just the opposite function of VBA. It returns the name of the weekday corresponding to a given number.
Conclusion
Using these methods, you can use the VBA Weekday function to get the corresponding weekday number from a given date. Do you have any questions? Feel free to ask us.