How to Use the VBA Weekday Function (2 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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)

Quick View of the VBA Weekday Function

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.

Output of Quick View of the VBA Weekday Function


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:

Syntax of the VBA Weekday Function

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.

Input Box to Use the VBA Weekday Function

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:


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

VBA Code to Use the VBA Weekday Function

⧭ Output:

Run the code. First, it’ll ask you to enter the date. Here I’ve entered 2/5/2022.

Input Box to Use the VBA Weekday Function

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo