How to Get the Day of Week Using VBA (4 Suitable Methods)

The day of the week becomes worthwhile for planning, scheduling, and even financial analysis. In a previous article, I discussed how to use the WEEKDAY function in Excel. But in today’s talks, I am presenting you with an outstanding alternative to get the day of the week using the VBA code.

Firstly, I’ll demonstrate the application of the VBA Weekday function, and then the usage of the WeekdayName function also with their formula and syntax. After that, you’ll show how to get the next weekend for a specific date. Lastly, how to display the weekday using VBA MsgBox will be discussed.

1. Finding the Day of the Week Using the VBA WEEKDAY Function

This is our dataset where some employee names are provided with their joining date. Now we’ll get the day of the week for the joining date.

Before using the VBA WEEKDAY function, let’s have a glimpse of its basics.

Function Objective

Finds the day of the week as a number (integer)

Syntax

Weekday(date, [ firstdayofweek ])

Arguments Explanation 

Note:

The firstdayofweek argument has following settings:

Now, let’s see how you can apply the VBA Weekday function to get the day of the week.

Step 1: 

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Step 2: 

Then, copy the following code in your module (module 1 in the practice workbook).

Sub VBA_Weekday()
Range("D5").Value = Weekday(Range("C5"))
Range("D6").Value = Weekday(Range("C6"))
Range("D7").Value = Weekday(Range("C7"))
Range("D8").Value = Weekday(Range("C8"))
Range("D9").Value = Weekday(Range("C9"))
Range("D10").Value = Weekday(Range("C10"))
Range("D11").Value = Weekday(Range("C11"))
Range("D12").Value = Weekday(Range("C12"))
End Sub

Step 3:

Finally, run the code.

Notice:

The following things are essential in the above VBA code.

• Worksheet name: Here, the worksheet name is “VBA_Weekday”
• Logic: The Weekday function returns the day of the week.
• Input Cell: Here, the input cell is C5, C6, etc.
• Output range: The output range is D5, D6, etc.

The output will look like this.

Read More: How to Use the Day Function in Excel VBA

2. Obtaining the Day of Week Using VBA WEEKDAYNAME Function

We found the day of the week using the VBA Weekday function in the first method, now we’ll see how to get the name of that day of the week. In such a situation, we need to utilize the WeekdayName function.

Function Objective

Returns the name of the day of the week.

Syntax

WeekdayName(Weekday, [Abbreviate], [FirstDayOfWeek])

Arguments Explanation

Now, let’s see how you can apply the VBA WeekdayName function to get the name of the day of the week.

Step 1: 

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Step 2: 

Then, copy the following code in your module (module 3 in the practice workbook).

Sub VBA_WeekdayName()
Range("E5").Value = WeekdayName(Range("D5"), "True", vbMonday)
Range("E6").Value = WeekdayName(Range("D6"), "True", vbMonday)
Range("E7").Value = WeekdayName(Range("D7"), "True", vbMonday)
Range("E8").Value = WeekdayName(Range("D8"), "True", vbMonday)
Range("E9").Value = WeekdayName(Range("D9"), "True", vbMonday)
Range("E10").Value = WeekdayName(Range("D10"), "True", vbMonday)
Range("E11").Value = WeekdayName(Range("D11"), "True", vbMonday)
Range("E12").Value = WeekdayName(Range("D12"), "True", vbMonday)
End Sub

Step 3:

Finally, run the code.

Notice:

The following things are essential in the above VBA code.

• Worksheet name: Here, the worksheet name is “VBA_WeekdayName”
• Logic: The WeekdayName function returns the name of the day of the week.
• Input Cell: Here, the input cell is D5, C6, etc.
• Output range: The output range is E5, E6, etc.

After running the code, the output will be as follows.

Read More: Excel VBA to Find Week Number

Similar Readings

• Sort Dates in Excel by Year (4 Easy Ways)
• How to Convert Date from String Using VBA (7 Ways)
• Use Excel Date Shortcut
• How to Use IF Formula with Dates (6 Easy Examples)

3. Applying VBA WEEKDAY Function to Get the Next Weekend Date

At this moment, we’ll explore an interesting thing, that is, to get the next weekend from the joining date utilizing the VBA Weekday function

Just follow the steps

Step 1: 

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Step 2: 

Then, copy the following code in your module (module 4 in the practice workbook).

Sub VBA_WeekendDate()
    Dim k As Integer
    For k = 5 To 12
        If Weekday(Cells(k, 2).Value, vbMonday) = 1 Then
             Cells(k, 3).Value = Cells(k, 2) + 5
        ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 2 Then
             Cells(k, 3).Value = Cells(k, 2) + 4
        ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 3 Then
             Cells(k, 3).Value = Cells(k, 2) + 3
        ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 4 Then
             Cells(k, 3).Value = Cells(k, 2) + 2
        ElseIf Weekday(Cells(k, 2).Value, vbMonday) = 5 Then
             Cells(k, 3).Value = Cells(k, 2) + 1
        Else
             Cells(k, 3).Value = "This is actually the weekend Date"
        End If
    Next k
End Sub

Step 3:

Finally, run the code.

Notice:

The following things are essential in the above VBA code.

• Worksheet name: Here, the worksheet name is “VBA_WeekendDate”
• Logic: The WeekdayName function returns the name of the day of the week.

You’ll see the following output.

4. Displaying The Day of Week Using VBA MsgBox

Though this is not an individual method, it can be used to display the day of the week utilizing VBA MsgBox.

Step 1: 

Firstly, open a module by clicking Developer>Visual Basic>Insert>Module.

Step 2: 

Then, copy the following code in your module (module 5 in the practice workbook).

Sub VBA_WeekDayDisplay()
    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
    dDate = "1/10/2022"
    iWkDay = Weekday(dDate)
    MsgBox "The Weekday of " & dDate & " is : " & iWkDay, vbInformation, "VBA Weekday Function"
End Sub

Step 3:

Finally, run the code.

Then you’ll see the following box where the output is 2.

Things to Remember

If the specified date is not a valid date, the VBA Weekday function returns the Run-time error ‘13’.

Download Practice Workbook

Conclusion

This is how you might get the day of the week using VBA in Excel. I firmly believe that the above methods will enrich your Excel learning. However, if you have any queries or suggestions, please leave them below in the comments section.

Further Readings

• How to Insert Current Date in Excel (3 Ways)
• Date Variable in VBA Codes (7 Uses of Macros with Examples)
• How to Get the Current Date in VBA (3 Ways)
• Use VBA DateValue Function in Excel (6 Examples)