Calculate Week Number - Week in Month - Holidays

[remeng]

Hi All;

I'm not sure if this will help you, but I think I created some code to use as a template for calculating different things related to dates.

Hope this helps someone. I'll be honest, I haven't verified this code other than simulated so it might not 100% work

Sub Holidays()

Dim first_date_of_month As Date

Dim current_week_number As Integer
Dim month_number As Integer
Dim first_week_number_in_month As Integer
Dim week_number_in_month As Integer
Dim day_in_week As Integer



'--------------------

current_week_number = Format(Date, "ww")


'month_number  Jan = 1, Feb = 2, March = 3...
month_number = Month((Now))

first_date_of_month = month_number & "/1/" & Year(Now())


first_week_number_in_month = Format(first_date_of_month, "ww")

week_number_in_month = current_week_number - first_week_number_in_month

'Monday = 1 , Tuesday = 2 , etc.

day_in_week = Weekday(Now(), vbMonday)





' (1/1/####) New Years

If month_number = 1 And Day(Now()) = 1 Then

    MsgBox "New Year's"

Else: End If


'(01/??/###) Martin Luther King’s Birthday 3rd Monday in January
    
If month_number = 1 And week_number_in_month = 3 And day_in_week = 1 Then


    MsgBox "MLK day"


Else: End If


'(02/??/###) Washington’s Birthday 3rd Monday in February

If month_number = 2 And week_number_in_month = 3 And day_in_week = 1 Then


    MsgBox "President's Day"

Else: End If


'(05/??/###) Memorial Day last Monday in May

If month_number = 5 And week_number_in_month = 4 And day_in_week = 1 Then


    MsgBox "Memorial Day"


Else: End If

'(06/19/###) Juneteenth National Independence Day June 19

If month_number = 6 And Day(Now()) = 19 Then


    MsgBox "Juneteenth"

Else: End If



'(07/04/###) Independence Day July 4


If month_number = 7 And Day(Now()) = 4 Then

    MsgBox "Independence Day"

Else: End If


'(09/??/###) Labor Day 1st Monday in September

If month_number = 9 And week_number_in_month = 1 And day_in_week = 1 Then

    MsgBox "Labor Day"
    
Else: End If


'(10/??/###) Columbus Day 2nd Monday in October


If month_number = 9 And week_number_in_month = 1 And day_in_week = 1 Then

    MsgBox "Columbus Day"

Else: End If



'(11/11/###) Veterans’ Day November 11

If month_number = 11 And Day(Now()) = 11 Then

    MsgBox "Veteran's Day"
    

Else: End If


'(11/??/###) Thanksgiving Day 4th Thursday in November


If month_number = 11 And week_number_in_month = 4 And day_in_week = 4 Then

    MsgBox "Thanks Giving"

Else: End If


'(12/25/###) Christmas Day December 25


If month_number = 12 And Day(Now()) = 25 Then

    MsgBox "Christmas"

Else: End If


'(12/31/####) New Year's Eve


If month_number = 12 And Day(Now()) = 25 Then


    MsgBox "New Years"
    

Else: End If


MsgBox "Not a holiday"


End Sub