How can I find the 2nd Business Day of the Month?

[Jarobe86]

Hey guys could really use some help!



I am having trouble finding a formula to calculate the second business day of the month. I need this for the rest of my code. The closest thing I found is:



Private Sub Workbook_Open()

Dim lngEoM As Long
lngEoM = Date-Day(Date)

If Date = lngEOM + Choose(Weekday(lngEoM,vbMonday),0,0,0,0,0,2,1) Then
MsgBox "It's the first day of the month"
End If

End Sub



How can I change this to the second day?

I was hoping I could get the value of this date so I can use it.

Thanks to anyone who might be able to help!

 

[Yeldarb]

Not sure if you are trying to search the current date or you just want to find the correct day of a given month

Here is what works for me. Hope it helps you in some way.

Private Sub SecondBusinessDayOfTheMonth_Click()
Dim PretendMonth, WhatDayIsIt As Integer
Dim PretendYear As Long
Dim FirstOfTheMonth, TheSecondIsTheOne, TheThirdIsTheOne, TheFourthIsTheOne, Test As String
PretendMonth = InputBox("Please enter a Month, Ex: 1 for January", "Test")
PretendYear = InputBox("Please enter a Year, Ex: 2012 for this year", "Test")
FirstOfTheMonth = PretendMonth & "/1/" & PretendYear
WhatDayIsIt = Weekday(FirstOfTheMonth)
TheSecondIsTheOne = WeekdayName(Weekday(DateAdd("d", 1, FirstOfTheMonth))) & _
", " & PretendMonth & "/2/" & PretendYear & " is the second business day of the month"
TheThirdIsTheOne = WeekdayName(Weekday(DateAdd("d", 2, FirstOfTheMonth))) & _
", " & PretendMonth & "/3/" & PretendYear & " is the second business day of the month"
TheFourthIsTheOne = WeekdayName(Weekday(DateAdd("d", 3, FirstOfTheMonth))) & _
", " & PretendMonth & "/4/" & PretendYear & " is the second business day of the month"

Test = Choose(WhatDayIsIt, TheThirdIsTheOne, TheSecondIsTheOne, TheSecondIsTheOne, _
TheSecondIsTheOne, TheSecondIsTheOne, TheFourthIsTheOne, TheFourthIsTheOne)
MsgBox Test
End Sub