Here are some ideas you can use for this. First, you need to know how may
days are in the month. Then you can use the function below to return the
number of work days in the month. The subtract the work days from the number
of days, and it will return the week end days.
To Get the last day of the month:
Dim dtmFistDay as Date 'The First Day of a month
Dim dtmLastDay as Date 'The Last Day of a month
Dim lngTotDays As Long 'Total number of days in the month
Dim lngWorkDays as Long 'Work days in the month
Dim lngWkendDays As Long 'Number of Week End days
dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
'Returns the last day of the month
dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
lngWkendDays = lngTotDays - lngWorkDays
Now the function:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
-----
This, of course, does not include any holidays. If you need to count
holidays, you will need a holiday table and you can use something like this
to count the holidays in the month:
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" & dtmFirstDay & "# And #" & dtmLastDay & "#")
