×
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

Calculate Week Number - Week in Month - Holidays

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close