VBA Tip: Weekday of a Date

To determine the weekday of a date (from 1 to 7) or display it as a text format (Monday-Sunday, MON-SUN, M-S, etc.), there are several options available.

To obtain the weekday number of a date (from 1 to 7), simply use the Weekday function:

Sub example()
    
    dateTest = CDate("4/27/2024") 'An example date

    MsgBox Weekday(dateTest) 'Returns: 7
    
End Sub

You can also use the Format function to obtain the weekday:

Sub example()
    
    dateTest = CDate("4/27/2024") 'An example date

    MsgBox Format(dateTest, "w") 'Returns: 7
    
End Sub

Displaying the Weekday as Text

Here are some examples to display the weekday in different formats (Monday-Sunday, MO-SU, etc.):

Sub example()
    
    dateTest = CDate("4/27/2024") 'An example date
    
    'Format: Monday - Sunday
    MsgBox Format(dateTest, "dddd") 'Returns: Saturday
    
    'Format: MONDAY - SUNDAY
    MsgBox UCase(Format(dateTest, "dddd")) 'Returns: SATURDAY
    
    'Format: MON - SUN
    MsgBox Left(UCase(Format(dateTest, "dddd")), 3) 'Returns: SAT
    
    'Format: MO - SU
    MsgBox Left(UCase(Format(dateTest, "dddd")), 2) 'Returns: SA
    
    'Format: M - S
    MsgBox Left(UCase(Format(dateTest, "dddd")), 1) 'Returns: S
    
    'Custom format (replace "day?" values with your own)
    MsgBox Array("day1", "day2", "day3", "day4", "day5", "day6", "day7")(Weekday(dateTest) - 1) 'Returns: day7
    
End Sub