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

In this article, we’ll explain how to use the VBA Weekday function in Excel.

VBA Weekday Function (Quick View)

Running this code will return 7, because the date “1/1/2022” was Saturday, and considering Sunday as the 1st day of the week, the number for Saturday is 7.

Download Practice Workbook

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:

The syntax of the Weekday function is:

Arguments:

The argument FirstDayofWeek can contain a maximum of 8 values, each representing a specific weekday as the 1st day of the week.

Return Value: 

Returns a number representing the weekday of the given date.

2 Examples Using the VBA Weekday Function

Here are some detailed examples to demonstrate use of the VBA Weekday function.

Example 1 – Finding the Weekday Name from a Given Date using the VBA Weekday Function

In this example, we’ll use the VBA Weekday function to return the weekday name from a given date.

First, we’ll take the date from the user as an input:

Input_Date = InputBox("Enter the Date: ")

We’ll use the VBA Weekday function to get the weekday number of the date, considering Sunday as the 1st day of the week.

Weekday_Number = Weekday(Input_Date)

We’ll 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

The complete VBA code is as follows:

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, we’ve entered 2/2/2022.

Then click OK. Returned will be the name of the weekday of the provided date. Here it’s Wednesday.

Read More: How to Use VBA WeekdayName Function in Excel (2 Examples)

Similar Reading:

• 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 weekend days for his / her region, separated by commas:

Weekends=InputBox("Enter the Weekends of Your Region (Separated by Commas): ")

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)

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 is as follows:

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 we’ve entered 2/5/2022.

Then it’ll ask you to enter the weekends of your region, separated by commas. Here we’ve entered Saturday, Sunday.

Click OK and a message box will display “It’s a weekend.”, because the date 2/5/2022 is a Saturday.

Related Content: How to Use VBA DateDiff Function in Excel (9 Examples)

More Reading

The WeekdayName is the corollary function of the Weekday function in VBA. It returns the name of the weekday corresponding to a given number.

Related Articles

• How to Use VBA While Wend Statement in Excel (4 Examples)
• Excel Date Picker for Entire Column
• How to Use VBA Len Function in Excel (4 Examples)