VBA WEEKDAY Function

What is Excel VBA Weekday Function?

In Excel VBA, the VBA Weekday function is used to determine the day of the week for a given date. It returns the number that represents the day of the week, where Sunday is considered the first day of the week (represented by the number 1) and Saturday is the last day of the week (represented by the number 7).

Suppose an example where we get VBA Weekday Number from a given date below:

This example takes the date 08-04-2023, and returns the day number of the week in a message box function.

What does Weekday Function do?

The Weekday function returns an integer that corresponds to the day of the week as per the specified [FirstDayOfWeek]. For example, if you have a date that falls on a Tuesday, and you are using the default [FirstDayOfWeek] (Sunday), the function will return 3 (since Tuesday is the third day of the week).

The Weekday function is commonly used in VBA macros or Excel formulas to perform different actions or calculations based on the day of the week. For example, you might use it to determine if a certain date falls on a weekend (Saturday or Sunday) or a weekday (Monday to Friday) or to categorize data based on weekdays.

The function takes two parameters from the syntax:

Weekday(Date, [FirstDayOfWeek])

Date: This is the date for which you want to find the day of the week.

[FirstDayOfWeek] (Optional): This parameter is used to specify the first day of the week. If omitted, Sunday is assumed as the first day of the week. The [FirstDayOfWeek] parameter can take one of the following VBA constants:

• vbUseSystem: This uses the first day of the week specified in your computer’s regional settings.
• vbSunday: Sunday (default).
• vbMonday: Monday.
• vbTuesday: Tuesday.
• vbWednesday: Wednesday.
• vbThursday: Thursday.
• vbFriday: Friday.
• vbSaturday: Saturday.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use Excel VBA Weekday?

To implement VBA Replace string in text file, we perform the following steps as shown below:

Step 1: Go to the “Developer” tax in excel in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and Sub procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Step 2: Start creating a subroutine to give greet prompts based on whether it is a weekday or Weekend with the help of the VBA Weekday number.

Step 3: Declare an integer variable which returns the value given by the weekday function. Here, we’re using the Excel VBA Date function which returns today’s date. You can customize it in such a way that custom dates can be input.

Step 4: Initialize an If-Else statement that checks if the VBA Weekday number is either Saturday or Sunday.

If it is either Saturday, it prints the weekend prompt in a Message Box.

Step 5: Declare an Else function in case it isn’t a weekend, but a weekday.

Then, end the VBA If-Else statement.

Code:

Sub GreetBasedOnWeekday()
Dim dayOfWeek As Integer
dayOfWeek = Weekday(Date)
If dayOfWeek = vbSaturday Or dayOfWeek = vbSunday Then
MsgBox “It’s the weekend! Enjoy your time off!”
Else
MsgBox “It’s a weekday. Have a productive day!”
End If
End Sub

Step 6: Run the code above. The output is displayed in a message box.

Now that we know how to use the VBA Weekday function, let us see some examples of how to use it in detail.

Examples

Let us look at some examples of how to implement the VBA WeekDay function.

Example #1

Let us consider an example where we want to get the VBA Weekday Name from Date. Given today’s date let us find out the VBA Weekdayname of it.

Step 1: Initialize a subroutine to return VBA Weekdayname.

Step 2: Define the variable as String. This will return the weekday name from the number given.

Step 3: Assign value to the variable. After declaring another variable as the VBA Date datatype, use the Date function in VBA which returns today’s date.

Step 4: Get the VBA Weekday Number from Date and then assign it to another variable with an Integer datatype.

Step 5: Using Select case in VBA, return the VBA Weekdayname by declaring the Weekday Names for numbers 1-7 as shown.

Step 6: Show the value of Variable in MsgBox.

Code:

Sub DisplayDayOfWeek()
Dim dayOfWeek As String
Dim inputDate As Date
inputDate = Date
Dim dayNumber As Integer
dayNumber = Weekday(inputDate)
Select Case dayNumber
Case 1
dayOfWeek = “Sunday”
Case 2
dayOfWeek = “Monday”
Case 3
dayOfWeek = “Tuesday”
Case 4
dayOfWeek = “Wednesday”
Case 5
dayOfWeek = “Thursday”
Case 6
dayOfWeek = “Friday”
Case 7
dayOfWeek = “Saturday”
End Select
MsgBox “The day of the week for ” & inputDate & ” is ” & dayOfWeek
End Sub

Step 7: Run the above code. The output is shown in a MsgBox.

Now, let us look at another example for checking whether a given set of dates are on the weekend or not.

Example #2

Consider an example where you need to color code the dates to differentiate VBA Weekday Number as weekdays or weekends by color coding the adjacent cells of the next column for a given set of data values.

Step 1: Create a subroutine to differentiate weekdays and weekends.

Step 2: Define the Worksheet this subroutine will be working in.

Step 3: Get the last row of the table with the help of the VBA XlUp function.

VBA XlUp function finds the last non-empty cell of the specified column. This is used to make the table dynamic so that in the future when the number of values increases or decreases, there’s no need to change the row value manually all the time.

Step 4: Declare an iterative variable to run through the set of values and an integer to store the value of the VBA Weekday Function.

Step 5: Initialize a FOR loop to run through the set of values. Find the VBA Weekday Number for all the values.

Step 6: Initialize an If-Else statement to check if the given VBA Weekday Number results on a Weekday or a Weekend. If it is a weekday, the color of the adjacent cell is green, and if it is a weekend, the adjacent cell is colored pink.

We use the Offset excel function such that the color of the cell in the adjacent column is changed. Here, Offset(0,1) means that the color will change in the next column.

Code:

Sub HighlightWeekdays()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet2”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
Dim dateCell As Range
Dim dayOfWeek As Integer
For Each dateCell In ws.Range(“A1:A” & lastRow)
dayOfWeek = Weekday(dateCell.Value)
If dayOfWeek = vbSaturday Or dayOfWeek = vbSunday Then
dateCell.Offset(0, 1).Interior.Color = RGB(255, 192, 203) ‘ Pink for weekends
Else
dateCell.Offset(0, 1).Interior.Color = RGB(144, 238, 144) ‘ Light green for weekdays
End If
Next dateCell
End Sub

Step 7: Run the above code, then check the Worksheet to view the results.

Important Things To Note

• Make sure to pass a valid date as the argument to the Weekday function. If you pass an invalid date or a non-date value, the function may return unexpected results or generate errors.
• The Weekday function’s result is influenced by the [FirstDayOfWeek] parameter. If you’re not using the default [FirstDayOfWeek] (Sunday), be clear about the first day of the week you are using.
• To make your code more readable and easier to maintain, use VBA constants like vbSunday, vbMonday, etc., when specifying the [FirstDayOfWeek] parameter.
• Avoid relying on default values in the Weekday function.
• Avoid comparing the results of the Weekday function directly with day names as strings, as it may lead to issues with case sensitivity and regional differences.
• When used in loops, it allows you to iterate through dates for specific weekdays.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA WEEKDAY Function, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA WEEKDAY Function in Excel. Here we learn how to implement weekday function to determine the day of the week in VBA with examples & downloadable excel template. You can learn more from the following articles –

• VBA Match
• VBA DateAdd Function
• VBA DateDiff Function