VBA Weekday

Excel VBA Weekday

Can we tell the weekday number by looking at a particular date? Yes, we can tell the day number in that week depending upon the starting day of the week. In regular worksheet function, we have a function called “WEEKDAY” to tell the number of the week for a particular date. In VBA too we have the same function to find the same thing. In this article, we will see about VBA Weekday function. Read on…

What Does Weekday Function do in Excel VBA?

Weekday function returns the provided date’s day number in the week. For example, if you have dates 01^st April to 07^th April and if you want to know the day of the date 05^th April if the starting day of the week is from Monday it is the 5^th day.

To find this we have the same function “Weekday” in a worksheet as well as in VBA. Below is the syntax of the WEEKDAY function.

Date: For which date we are trying to find the weekday. This should be a proper date with the correct format.

[First Day of Week]: In order to determine the weekday of the provided Date we need to mention what is the first day of the week. By default, VBA considers “Monday” as the starting day of the week. Apart from this, we can supply below days as well.

Examples of Weekday Function in Excel VBA

Below are the examples of Weekday in VBA

Example #1

To start the proceedings let me start with a simple example first up. Now we will try to find the weekday for the date “10-April-2019”.

Step 1: Define the variable as String.

Code:

Sub Weekday_Example1()

    Dim k As String

End Sub

Step 2: Assign the value to the variable “k” by applying the WEEKDAY function.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday(

End Sub

Step 3: The date we are testing here is “10-Apr-2019”, so pass the date as “10-Apr-2019”.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday("10-Apr-2019"

End Sub

Step 4: By default, it takes the first day of the week as “Monday”, so ignore this part. Close the bracket. In the next line show the value of variable “k” in the VBA message box.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday("10-Apr-2019")

    MsgBox k

End Sub

Ok, we are done.

If we run the code we will get the result as “4” because starting from Sunday the provided date (10-Apr-2019) falls on 4^th day of the week.

Similarly, if you change the start day of the week it keeps varying. Below is an example line for the same.

Code:

k = Weekday("10-Apr-2019", vbMonday)

‘This returns 3

k = Weekday("10-Apr-2019", vbTuesday)

‘This returns 2

k = Weekday("10-Apr-2019", vbWednesday)

‘This returns 1

k = Weekday("10-Apr-2019", vbThursday)

‘This returns 7

k = Weekday("10-Apr-2019", vbFriday)

‘This returns 6

k = Weekday("10-Apr-2019", vbSaturday)

‘This returns 5

k = Weekday("10-Apr-2019", vbSunday)

‘This returns 4

Example #2 – Arrive Whether the Date is on Weekend or Not

Assume you have a date like the below and you want to find the next weekend date then we can use WEEKDAY function to arrive the results.

We need to use WEEKDAY with IF condition and Loops to arrive the result. I have written the code for you go line by line to get the logic.

Code:

Sub Weekend_Dates()

    Dim k As Integer

    For k = 2 To 9

        If Weekday(Cells(k, 1).Value, vbMonday) = 1 Then
             Cells(k, 2).Value = Cells(k, 1) + 5
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 2 Then
             Cells(k, 2).Value = Cells(k, 1) + 4
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 3 Then
             Cells(k, 2).Value = Cells(k, 1) + 3
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 4 Then
             Cells(k, 2).Value = Cells(k, 1) + 2
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 5 Then
             Cells(k, 2).Value = Cells(k, 1) + 1
        Else
             Cells(k, 2).Value = "This is actually the weekend Date"
        End If

    Next k

End Sub

This will arrive the results like the below.

Look at the cells B6 & B7 we got the result as “This is actually the weekend date” because dates “04-May-2019” and “06-Apr-2019” are actually weekend dates, so no need of showing weekend date for weekend dates. By default, we get the result as this.

You can download this Excel VBA Weekday here – VBA WeekDay Function Template

Recommended Articles

This has been a guide to VBA Weekday. Here we learn how to Use Weekday Function in Excel VBA along with practical examples and downloadable codes. Below are some useful excel articles related to VBA –

• Type Statement in VBA
• Integer in VBA Excel
• VBA Type Mismatch
• VBA IFERROR
• VBA DateDiff Function
• VBA Error Handling
• VBA DIR Function