• Home
 · 
• String Manipulation
 · 
• This is a function I created to tell me the exact date that a holiday falls on. This is for those h

This is a function I created to tell me the exact date that a holiday falls on. This is for those h

This is a function I created to tell me the exact date that a holiday falls on. This is for those holidays that do not have a specific date

API Declarations


Private Type tyMonth
datSunday(1 To 6) As Date
datMonday(1 To 6) As Date
datTuesday(1 To 6) As Date
datWednesday(1 To 6) As Date
datThursday(1 To 6) As Date
datFriday(1 To 6) As Date
datSaturday(1 To 6) As Date
End Type




Rate This is a function I created to tell me the exact date that a holiday falls on. This is for those h

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'lngYear is the year you want
'strHoliday is the name of the holiday.  I would use one of the following for 
'this: MLK Day, Presidents' Day, Memorial Day, Labor Day, Columbus Day, 
'Thanksgiving Day

Public Function GetDateOfWeekday(intMonth As Integer, lngYear As Long, _
    strHoliday As String) As Date
    
    Dim udtMonth As tyMonth
    Dim datDate As Date
    Dim Index As Integer
    Dim strWeekday As String

    'create a variable date of the first day of the month
    datDate = CDate(intMonth & "/" & 1 & "/" & lngYear)
    Index = 1

    'add up how many of each weekday there are in the specified month
    Do Until Month(datDate) <> intMonth
        strWeekday = WeekdayName(Weekday(datDate))
        Select Case strWeekday
            Case "Sunday"
                udtMonth.datSunday(Index) = datDate
            Case "Monday"
                udtMonth.datMonday(Index) = datDate
            Case "Tuesday"
                udtMonth.datTuesday(Index) = datDate
            Case "Wednesday"
                udtMonth.datWednesday(Index) = datDate
            Case "Thursday"
                udtMonth.datThursday(Index) = datDate
            Case "Friday"
                udtMonth.datFriday(Index) = datDate
            Case "Saturday"
                udtMonth.datSunday(Index) = datDate
                Index = Index + 1
        End Select
        datDate = datDate + 1
    Loop

    'Calculate the date for the chosen holiday
    Select Case strHoliday
        Case "MLK Day"
            Index = 1
            Do Until udtMonth.datMonday(Index) <> 0
               Index = Index + 1
            Loop
            GetDateOfWeekday = udtMonth.datMonday(Index + 2)
        Case "Presidents' Day"
            Index = 1
            Do Until udtMonth.datMonday(Index) <> 0
               Index = Index + 1
            Loop
            GetDateOfWeekday = udtMonth.datMonday(Index + 2)
        Case "Memorial Day"
            Index = 6
            Do Until udtMonth.datMonday(Index) <> 0
               Index = Index - 1
            Loop
            GetDateOfWeekday = udtMonth.datMonday(Index)
        Case "Labor Day"
            Index = 1
            Do Until udtMonth.datMonday(Index) <> 0
               Index = Index + 1
            Loop
            GetDateOfWeekday = udtMonth.datMonday(Index)
        Case "Columbus Day"
            Index = 1
            Do Until udtMonth.datMonday(Index) <> 0
               Index = Index + 1
            Loop
            GetDateOfWeekday = udtMonth.datMonday(Index + 1)
        Case "Thanksgiving Day"
            Index = 6
            Do Until udtMonth.datThursday(Index) <> 0
               Index = Index - 1
            Loop
            GetDateOfWeekday = udtMonth.datThursday(Index)
    End Select
   
End Function