VBA Weekday Function in Excel

VBA Weekday function in Excel is categorized as a Date & Time function. This is a built-in Excel VBA Function. This function returns a number representing the day of the week, given a date value.

We can use this function in VBA as well as in Excel. This function we use in either procedure or function in a VBA editor window in Excel. We can use this VBA Weekday Function in any number of times in any number of procedures or functions. Let us learn what is the syntax and parameters of the Weekday function, where we can use this Weekday Function and real-time examples in Excel VBA.

Syntax of VBA Weekday Function

The syntax of the Weekday Function in VBA is

Weekday(Date,[FirstDayOfWeek])

The Weekday function returns a numeric value. It can be any of the following value.

VB Constant	Return Value
vbUseSystem	Returns a number from 1 to 7 (It displays the first day of the week based on system settings).
vbSunday	It returns a number from 1 to 7 (Monday to Sunday).
vbMonday	It returns a number from 1 to 7 (Tuesday to Monday).
vbTuesday	It returns a number from 1 to 7 (Wednesday to Tuesday).
vbWednesday	It returns a number from 1 to 7 (Thursday to Wednesday).
vbThursday	It returns a number from 1 to 7 (Friday to Thursday).
vbFriday	It returns a number from 1 to 7 (Saturday to Friday).
vbSaturday	It returns a number from 1 to 7 (Sunday to Saturday).

Parameters or Arguments:

The Weekday function has two arguments in Excel VBA.
where
Date:The Date is a required argument. It represents the date for which the weekday is to be returned.
[FirstDayOfWeek]:The [FirstDayOfWeek] is an optional argument. It represents the first day of the week. If ignored this parameter, vbSunday is the default value. It can be one of the following value.

VB Constant	Value	Description
vbUseSystem	0	Use the National Language Support(NLS) API setting
vbSunday	1	Sunday (default)
vbMonday	2	Monday
vbTuesday	3	Tuesday
vbWednesday	4	Wednesday
vbThursday	5	Thursday
vbFriday	6	Friday
vbSaturday	7	Saturday

Where we can apply or use VBA Weekday Function?

We can use this Weekday Function in VBA MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Display the Weekday for specified date

Here is a simple example of the VBA Weekday function. This below example displayed the Weekday of 01/01/2018.

'Display the Weekday for specified date
Sub VBA_Weekday_Function_Ex1()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate)
        
    MsgBox "The Weekday number of " & dDate & " is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the first example output.

Example 2: Display the Weekday for specified date using vbUseSystemDayOfWeek

Let us see one more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbUseSystemDayOfWeek.

'Display the Weekday for specified date using vbUseSystemDayOfWeek  
Sub VBA_Weekday_Function_Ex2()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbUseSystemDayOfWeek)
        
    MsgBox "The Weekday number of " & dDate & " using vbUseSystemDayOfWeek is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output:Here is the screen shot of the second example output.

Example 3: Display the Weekday for specified date using vbSunday

Let us see another example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbSunday.

'Display the Weekday for specified date using vbSunday 
Sub VBA_Weekday_Function_Ex3()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbSunday)
        
    MsgBox "The Weekday number of " & dDate & " using vbSunday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the third example output.

Example 4: Display the Weekday for specified date using vbMonday

One more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbMonday.

'Display the Weekday for specified date using vbMonday 
Sub VBA_Weekday_Function_Ex4()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbMonday)
        
    MsgBox "The Weekday number of " & dDate & " using vbMonday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the fourth example output.

Example 5: Display the Weekday for specified date using vbTuesday

One more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbTuesday.

'Display the Weekday for specified date using vbTuesday  
Sub VBA_Weekday_Function_Ex5()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbTuesday)
        
    MsgBox "The Weekday number of " & dDate & " using vbTuesday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the fifth example output.

Example 6: Display the Weekday for specified date using vbWednesday

Let us show another example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbWednesday.

'Display the Weekday for specified date using vbWednesday
Sub VBA_Weekday_Function_Ex6()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbWednesday)
        
    MsgBox "The Weekday number of " & dDate & " using vbWednesday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the sixth example output.

Example 7: Display the Weekday for specified date using vbThursday

Let us show one more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbThursday.

'Display the Weekday for specified date using vbThursday
Sub VBA_Weekday_Function_Ex7()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbThursday)
        
    MsgBox "The Weekday number of " & dDate & " using vbThursday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the seventh example output.

Example 8: Display the Weekday for specified date using vbFriday

Let us show one more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbFriday.

'Display the Weekday for specified date using vbFriday
Sub VBA_Weekday_Function_Ex8()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbFriday)
        
    MsgBox "The Weekday number of " & dDate & " using vbFriday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the eighth example output.

Example 9: Display the Weekday for specified date using vbSaturday

Let us show one more example of the VBA Weekday function. This below example displays the Weekday of 01/01/2018 using vbSaturday.

'Display the Weekday for specified date using vbSaturday
Sub VBA_Weekday_Function_Ex9()

    'Variable declaration
    Dim dDate As Date
    Dim iWkDay As Integer
                
    dDate = "01/01/2018"
    
    iWkDay = Weekday(dDate, vbSaturday)
        
    MsgBox "The Weekday number of " & dDate & " using vbSaturday is : " & iWkDay, vbInformation, "VBA Weekday Function"
    
End Sub

Output: Here is the screen shot of the ninth example output.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers