DatDiff - Working days only?

[DianaW]

Hi,

is there any alternative of the DatDiff function, which counts only working days between two given dates?

Regards,
Diana

 

[RuralGuy]

There are others but here's one:

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

 

[KeithG]

I posted a BusinessDays function I created on the forum. The function will actually calculate the date of every Holiday within the time frame so there is no need for a holidays table.

 

[DianaW]

Thank you.

Thank you to all of you for your help!

Keith, do you remember where you posted it?

Regards
Diana

 

[Alc]

http://www.access-programmers.co.uk/forums/showthread.php?t=133329&highlight=businessdays

 

[DianaW]

Thank you.

Thank you very much!

Diana