×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
To Your Posts
• Keyword Search
• One-Click Access To Your
Favorite Forums
• Automated Signatures
On Your Posts
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Calculate Week Number - Week in Month - Holidays

 Forum Search FAQs Links MVPs

Calculate Week Number - Week in Month - Holidays

(OP)
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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.