Getting workdays by month using VBA

[bcurrey]

I'm building a query that will run automatically at night using vba code. Some of the calculations will be based on # of working days in the current month and prior month.

Any ideas on how I can have this included in the macro to do automatically? I know how to get # of days in a month, but is there anything that I can do to get days worked?

Appreciate your help.

 

[robertgama]

You can use the weekday function as you are looping though the days of the month to determine the number of weekdays

 

[robertgama]

You can also use this function to return the number of days in a month before doing it:

Public Function DaysInMonth(dateString As String) As Integer
   Dim myDate As Date
   myDate = DateValue(dateString)
   DaysInMonth = day(Application.WorksheetFunction.EoMonth(myDate, 0))
End Function

 

[AlphaFrog]

The NETWORKDAYS function can be used to calculate the number of workdays between two dates. It also has the capability to exclude Holidays.

 

[robertgama]

I like that suggestion for networkdays.

Putting it together word look something like this:

Public Function WorkDaysInMonth(myDate As Date) As Integer
    Dim curMonth As Integer
    Dim curYear As Integer

    curMonth = Month(myDate)
    curYear = Year(myDate)
    begindate = CDate(curMonth & "/" & 1 & "/" & curYear)
    enddate = Application.WorksheetFunction.EoMonth(myDate, 0)
    WorkDaysInMonth = Application.WorksheetFunction.NetworkDays(begindate, enddate)
End Function

Sub test()
    MsgBox WorkDaysInMonth("September 14, 2011")
End Sub

 

[Rick Rothstein]

Putting it together word look something like this:

Public Function WorkDaysInMonth(myDate As Date) As Integer
    Dim curMonth As Integer
    Dim curYear As Integer
 
    curMonth = Month(myDate)
    curYear = Year(myDate)
    begindate = CDate(curMonth & "/" & 1 & "/" & curYear)
    enddate = Application.WorksheetFunction.EoMonth(myDate, 0)
    WorkDaysInMonth = Application.WorksheetFunction.NetworkDays(begindate, enddate)
End Function

Here is a modification to a function that I have posted in the past which you might consider using instead... it does not call out to the worksheetfunctions, choosing instead to use native VBA functions only.

Function CWD(myDate As Date) As Long
  Dim StartDate As Date, EndDate As Date
  StartDate = myDate - Day(myDate) + 1
  EndDate = DateSerial(Year(myDate), Month(myDate) + 1, 0)
  CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1)
End Function

The CWD function name is short for Count Working Days.