working days in a month

[Smart]

Does any one know how to calculate how many working days there are in a month
IE
Total number of days in a month minus Saturdays and Sundays

All contributions gratefully received

Thanks

 

[raskew]

Hi -

Look here: http://www.access-programmers.co.uk/forums/showthread.php?t=102178

Bob

 

[RuralGuy]

Here's a function for one of your standard modules:

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'-- Return the number of WorkingDays between StartDate and EndDate
On Error GoTo err_workingDays

Dim intCount As Integer

If IsDate(StartDate) And IsDate(EndDate) Then
   If EndDate >= StartDate Then
      '-- Force Incoming Dates into consistant format
      StartDate = CDate(Format(StartDate, "Short Date"))
      EndDate = CDate(Format(EndDate, "Short Date"))
      
      intCount = 0
      Do While StartDate < EndDate
         StartDate = StartDate + 1
         If Weekday(StartDate, vbMonday) <= 5 Then
      '-- Use the following code if you have a "Holiday" table
      '   If Weekday(StartDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & StartDate & "#")) Then
            intCount = intCount + 1
         End If
      Loop
      WorkingDays = intCount
   Else
      WorkingDays = -1  '-- To show an error
   End If
Else
   WorkingDays = -1  '-- To show an error
End If

exit_workingDays:
   Exit Function

err_workingDays:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume exit_workingDays

End Function

 

[Smart]

Thanks Chaps I will have a play and let you know how it goes

 

[Smart]

RE:working days

Thanks again for the pointers,I eventually used the following code (daceyj)

Public Function CountWorkingDays(ByVal dtmDate As Date, _
Optional intDOW As Integer = 2)

' Calculate the number of specified days in

' the specified month.

Dim dtmFirst As Date

Dim intCount As Integer

Dim intMonth As Integer

If (intDOW < vbSunday Or intDOW > vbSaturday) Then

' Caller must not have specified DOW, or it

' was an invalid number.

intDOW = Weekday(dtmDate)

End If

intMonth = Month(dtmDate)

' Find the first day of the month

dtmFirst = DateSerial(Year(dtmDate), intMonth, 1)

'' ' Move dtmFirst forward until it hits the
''
'' ' matching day number.

'' Do While Weekday(dtmFirst) <> intDOW
''
'' dtmFirst = dtmFirst + 1
''
'' Loop



' Now, dtmFirst is sitting on the first day

' of the requested number in the month. Just count

' how many of that day type there are in the month.

intCount = 0

Do While Month(dtmFirst) = intMonth

If intDOW <> 1 And intDOW <> 7 Then

intCount = intCount + 1

dtmFirst = dtmFirst + 1
intDOW = intDOW + 1

Else
dtmFirst = dtmFirst + 1
intDOW = intDOW + 1
If intDOW > 7 Then
intDOW = 1
End If
End If

Loop

CountWorkingDays = intCount




End Function

 

[EMP]

Public Function CountWorkingDays(ByVal dtmDate As Date, _
Optional intDOW As Integer = 2)

Not sure what intDOW does.

CountWorkingDays(#4/15/2006#) returned 22 instead of 20.


~

Does any one know how to calculate how many working days there are in a month
IE
Total number of days in a month minus Saturdays and Sundays

The following does what is required literally.

Public Function MyCountWorkDays(dDate As Date) As Integer
    Dim FirstDayOfMonth As Date
    Dim LastDayOfMonth As Date
    
    FirstDayOfMonth = DateSerial(Year(dDate), Month(dDate), 1)
    LastDayOfMonth = DateSerial(Year(dDate), Month(dDate) + 1, 0)
    
    ' [b]Total number of days in a month minus Saturdays and Sundays:-[/b]
    MyCountWorkDays = (LastDayOfMonth - FirstDayOfMonth + 1) _
         - (DateDiff("ww", FirstDayOfMonth, LastDayOfMonth, vbSaturday) _
            - (Weekday(FirstDayOfMonth) = vbSaturday)) _
         - (DateDiff("ww", FirstDayOfMonth, LastDayOfMonth, vbSunday) _
            - (Weekday(FirstDayOfMonth) = vbSunday))
    
End Function

which is based on Jon K's expression.

^