Need help returning second business day of the month please!

[Jarobe86]

Hey guys really frustrated.

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!!!

 

[Joe4]

How about using the WORKDAY function (part of the Analysis ToolPak add-in that comes with Excel)?

Just pick the last day of the previous month as your start date. For example, to get the second workday of April, your formula might look like:

=WORKDAY("3/31/2012",2)

(depending on your regional date settings)

 

[vaskov17]

.

 

[Jeff Meyers]

Not sure of the VBA coding, but you can use the =WORKDAY() function to get the second business day of the month.

Syntax

WORKDAY(start_date, days, [holidays])

The WORKDAY function syntax has the following arguments:

• Start_date Required. A date that represents the start date.
• Days Required. The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
• Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an of the serial numbers that represent the dates.

Examples:

=WORKDAY(DATE(2012,4,1),2) returns 04/03/12 (mm/dd/yy)
=WORKDAY(DATE(2012,12,1),2) returns 12/04/12 (mm/dd/yy)
​

​

 

[Jarobe86]

THANKS It works!