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.
Table of contents
- The Weekday function is a built-in VBA function that doesn’t require any additional library or reference. It helps determine the position of a given date within the week.
- The [FirstDayOfWeek] parameter can take values like vbSunday, vbMonday, etc., for customizing the first day of the week. If [FirstDayOfWeek] is not specified, the default is Sunday.
- The function can be used in various scenarios, such as scheduling tasks, filtering data based on weekdays, or generating reports with specific date formats.
- It simplifies date-related operations by converting dates into their corresponding weekday numbers.
- The weekday numbering follows the ISO standard: 1 (Monday) to 7 (Sunday).
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:
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.
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.
Dim dayOfWeek As Integer
dayOfWeek = Weekday(Date)
If dayOfWeek = vbSaturday Or dayOfWeek = vbSunday Then
MsgBox “It’s the weekend! Enjoy your time off!”
MsgBox “It’s a weekday. Have a productive day!”
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.
Let us look at some examples of how to implement the VBA WeekDay function.
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.
Dim dayOfWeek As String
Dim inputDate As Date
inputDate = Date
Dim dayNumber As Integer
dayNumber = Weekday(inputDate)
Select Case dayNumber
dayOfWeek = “Sunday”
dayOfWeek = “Monday”
dayOfWeek = “Tuesday”
dayOfWeek = “Wednesday”
dayOfWeek = “Thursday”
dayOfWeek = “Friday”
dayOfWeek = “Saturday”
MsgBox “The day of the week for ” & inputDate & ” is ” & dayOfWeek
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.
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.
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
dateCell.Offset(0, 1).Interior.Color = RGB(144, 238, 144) ‘ Light green for weekdays
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)
You can get the weekday from a date in Excel VBA using the Weekday function. It returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week.
• Forgetting that Weekday returns numbers 1 to 7, not day names.
• If the date input contains time information, it may affect the results. Use DateValue or format the date to remove the time component.
• Passing an empty cell, text, or a non-date value as input to Weekday can cause errors or unexpected results.
• Missing Required Argument: Forgetting to provide the mandatory Date argument when calling the function will result in an error.
• Implicit Variant Conversion: Implicitly converting date values to Variant data types may lead to issues, explicitly declaring data types.
Use the Weekday function in VBA with a valid date as an argument. It returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week. To get the day name, map the number to the corresponding day (e.g., 1 = Sunday, 2 = Monday).
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.
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 –