MM-DD-YYYY to day of week, analyzing consecutive working days

Question

I'm analyzing data based on x consecutive working days, M-F (and later skipping selected holidays). My plan is to take MM-DD-YYYY and determine the day of the week.

How can I do this in VBA?

I found this simple way using .NET but how do I call from Office 2007 VBA, and what reference do I need to add?

DateTime.Parse("2009-10-02").ToString("D", CultureInfo.CreateSpecificCulture("en") .NET Day of week from YYYY-MM-DD

For completeness and to help others, here is how my code ended up: How can I cleanly generalize this for x days look forward?

        myDateStd = myDate
        If Right(Left(myDate, 2), 1) = "/" Then myDateStd = "0" & myDate   'IF M/*/YYYY
        If Len(myDateStd) = 9 Then myDateStd = Left(myDateStd, 3) & "0" & Right(myDateStd, 6) ' If MM/D/YYYY' fix
        dayNumber = Weekday(DateSerial(Right(myDateStd, 4), Left(myDateStd, 2), Mid(myDateStd, 4, 2)), vbMonday)

        Select Case dayNumber
            Case 1 To 3
                workingDaysDelta = 3
            Case 4 To 5      'i.e. its a Friday (or thur) so need to count Fri,S,S,M,T
                workingDaysDelta = 5
            Case 6 To 7     ' its sat/sun
                workingDaysDelta = 4
            Case Else
                MsgBox "ERROR date seams invalid, Date = " & myDateStd
        End Select

'Loop with If testing conditions including:
CDate(wbAn.Cells(j, "E")) < (CDate(myDate) + dayNumber)

Answer 1

Any of the following should work

numeric, e.g. 2

Weekday(DateSerial(Right(s, 4), Left(s, 2), Mid(s, 4, 2)), vbMonday)

short string, e.g. "Tue"

Format(DateSerial(Right(s, 4), Left(s, 2), Mid(s, 4, 2)), "ddd")

long string, e.g. "Tuesday"

Format(DateSerial(Right(s, 4), Left(s, 2), Mid(s, 4, 2)), "dddd")

where s is the string containing the date in 'MM-DD-YYYY' format.

There is no need to add any references.