Excel VBA Weekday Function

Can we tell the weekday number by looking at a particular date? Yes, we can tell the day number that week, depending upon the starting day of the week. In regular worksheet functions, we have a function called WEEKDAY in excel to tell the week’s number for a particular date. In VBA, too, we have the same function to find the same thing.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Weekday (wallstreetmojo.com)

What does Weekday Function do?

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

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

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

Examples

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.”

Code:

Sub Weekday_Example1()

Dim k As String

End Sub

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

Sub Weekday_Example1()

Dim k As String

k = Weekday(

End Sub

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

Sub Weekday_Example1()

Dim k As String

k = Weekday("10-Apr-2019"

End Sub

Sub Weekday_Example1()

Dim k As String

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

MsgBox k

End Sub

We have completed it now.

If we run the code, we will get the result as “4” because starting from Sunday, the provided date (10-Apr-2019) falls on the 4th 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.

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 one below. You want to find the date next weekend. Then, we can use the WEEKDAY function to arrive at the results.

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

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

It will arrive at the results below.

This article has been a guide to VBA Weekday. Here, we learn how to use the VBA Weekday function to get the last day of the week with examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • IFERROR Function in VBAVBA DateDiff FunctionExcel Weekly Planner TemplateWhat is VBA DATEVALUE Function?VBA Paste Values