Getting the Day Number Related To A (Text) Day Of The Week (eg "Sat" = 7)

[Ark68]

VBA function WEEKDAY(date) returns the the number relating to the day of the week. eg Sun = 1, Mon = 2 etc.
Suppose I have a day text value, like Sat. What is a VBA solution to getting it's value 7?

I could do an If Elseif Then routine, but I'm hoping their might be a shorter method.

 

[Joe4]

One way is to create your own Custom Function in VBA like this:

Function GetWeekday(d As Variant) As Integer

    Select Case d
        Case "Sun."
            GetWeekday = 1
        Case "Mon."
            GetWeekday = 2
        Case "Tue."
            GetWeekday = 3
        Case "Wed."
            GetWeekday = 4
        Case "Thu."
            GetWeekday = 5
        Case "Fri."
            GetWeekday = 6
        Case "Sat."
            GetWeekday = 7
        Case Else
            GetWeekday = 0
    End Select
    
End Function

You can reference it like any other Excel function in VBA or on the worksheet, i.e.

Sub Test()
    MsgBox GetWeekday("Sat.")
End Sub

 

[Ark68]

Thanks Joe. A very clever and practical approach. Thank you.

 

[Joe4]

You are welcome.