# Calculate Week Number - Week in Month - Holidays

Status
Not open for further replies.

#### remeng

##### Technical User
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

Code:
``````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``````

Status
Not open for further replies.

Replies
6
Views
109
Replies
17
Views
161
Replies
1
Views
86
Replies
1
Views
88
Replies
3
Views
65