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