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.


Download Practice Workbook


How to Find Day of Week Using VBA

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.

Dataset


1. Finding the Day of Week Using the Weekday Function

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 

Argument Required/Optional Explanation
date Required The value of date in any format from which you want to get the day of the week.
firstdayofweek Optional A constant value that defines the first day of the week (1 for vbSunday is the default value).

Note:

The firstdayofweek argument has following settings:

VBA Constant Value Explanation

vbUseSystem

0 Use the National Language Support(NLS) API setting
vbSunday 1 Starting day of the week is Sunday (default)
vbMonday 2 Starting day of the week is Monday
vbTuesday 3 Starting day of the week is Tuesday
vbWednesday 4 Starting day of the week is Wednesday
vbThursday 5 Starting day of the week is Thursday
vbFriday 6 Starting day of the week is Friday
vbSaturday 7 Starting day of the week is Saturday

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

VBA Code for Weekday

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.

Finding the Day of Week Using the Weekday Function


2. Obtaining the Day of Week Using 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

Argument Required/Optional Explanation
weekday Required The value of date in any format from which you want to get the day of the week
abbreviate Optional A boolean value that defines abbreviated name (Use ‘True’ eg Sun, Tue)  or full name (Use ‘False’ eg Sunday, Tuesday)
firstdayofweek Optional A constant value that defines the first day of the week (1 for vbSunday is the default value)

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

VBA Code for WeekdayName

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.

Obtaining the Day of Week Using WeekdayName Function


3. Getting the Next Weekend Date Using VBA Weekday Function

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

VBA Code for WeekendDate

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.

Getting the Next Weekend Date Using VBA WeekDay Function


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

VBA Code for Displaying the day of the week

Step 3:

Finally, run the code.

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

Displaying The Day of Week Using VBA MsgBox


Things to Remember

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

Run-time error


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.

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo