How to Use Weekday Function in VBA (With Example)

You can use the Weekday function in VBA to obtain the day of the week (as an integer) from a given date.

Here is one common way to use this function in practice:

Sub FindWeekday()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.Weekday(Range("A" & i))
    Next i
    
End Sub

This particular macro will find the day of the week (as an integer) for each date in the range A2:A9 and display the results in the range B2:B9.

Note that the following integers correspond with each day of the week:

• 1: Sunday
• 2: Monday
• 3: Tuesday
• 4: Wednesday
• 5: Thursday
• 6: Friday
• 7: Saturday

The following example shows how to use the Weekday function in practice.

Note: If you would rather return the day of the week as a name then you should use the WeekdayName function instead.

Example: Use Weekday Function in VBA to Find Day of Week

Suppose we have the following column of dates in Excel:

Suppose we would like to obtain the day of the week (as an integer) for each date and display the results in column B.

We can create the following macro to do so:

Sub FindWeekday()
    
    Dim i As Integer

    For i = 2 To 9
        Range("B" & i) = WorksheetFunction.Weekday(Range("A" & i))
    Next i
    
End Sub

When we run this macro, we receive the following output:

Column B displays the day of the week (as an integer) for each date in column A.

For example:

• 1/1/2023 is on a Sunday, so the Weekday function returns 1.
• 1/4/2023 is on a Wednesday, so the Weekday function returns 4.
• 2/23/2023 is on a Thursday, so the Weekday function returns 5.

And so on.

Note: You can find the complete documentation for the VBA Weekday function here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

How to Compare Dates in VBA
How to Convert Date to Week Number in VBA
How to Calculate Days Between Two Dates in VBA