Excluding Weekends from Date Range Calculations

[Fozi]

I've got a start date and a completion date with an internal target of completion within five working days. Should we receive it on a Friday and it's now Monday, how do I write the code to exlude weekend days in the day count?

Clues would be much appreciated.

 

[MStCyr]

This can be achieved by using the Weekday function, such as the example:

' Test for Saturday or Sunday.
If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
....
....
Endif

 

[RuralGuy]

Here's a function you can use. Just put it in a standard module.

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