View Single Post
 
Old April 12th, 2008, 05:14 PM
RuralGuy RuralGuy is offline
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's a function you can put in a standard module.
Code:
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

      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] = " & Format(StartDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) 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
HTH RuralGuy (RG for short) acXP WinXP Pro