Exclude days from date counts

[amoney61]

Any idea on how to exclude sat and sun and other not working days from a days count between dates?
Ex datea - dateb is 8, but without sat and sun is 6
Thanks

 

[alvechurchdata]

I can't find my code but I did something like this last time:

n = 0
Do While datStart < datEnd
   if Weekday(datStart) = 7 or Weekday(datStart) = 1 then
     '-- Do nothing - it's a weekend
   else
     '-- Add logic here to handle other holidays
     n = n + 1
   end if
   datStart = datStart + 1
Loop

There are shorter and more elegant algorithms around which subtract one date from the other and then look at the WeekDay() values to decide how many weekends to subtract but they all then have to be fiddled to account for holidays other than the weekends so I went for the simple, brute force approach. You may need something faster if you're calling this function in the middle of a big loop or query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MajP]

For the holidays look at FAQ705-6003. Then subtract the number of holidays within your range.

 

[TheAceMan1]

How are ya amoney61 . . .

Have a look here Calculate Number of Working Days

... from the result of this code subtract the count from holidays (MajP's post).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Based on what Geoff was saying I tried to find a more efficient solution on the web and in the Tek-tips Faqs, but could not. Everything I found used the brute force method. If someone has one or a link, I would be interested. The brute force can be extremely inefficient especially if the dates are far apart (you iterate every day). In reality I would assume most working day calculations are relatively short periods. However with that said the below function is 30 to 40 times faster on my computer when calculating the working days in a year. It still is about 4 times faster when doing two weeks.

Basically you move to the first monday from the start date and calculate the working days until that monday.
Then figure out how many whole weeks until the end date. There is five working days per week
Move to the last whole week and count the remaining days.
If you find logic problems please post. This question comes up a lot and I think this is a better solution especially for large data sets.

Public Function EWorkingDays(ByVal startDate As Date, ByVal endDate As Date) As Long
  '....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
' EndDate As Date
' Returns: Long
' Author: MajP
' Date:   Dec 2010
' Comment: Accepts two dates and returns the number of weekdays between them
'          This is far faster than interating all days
' Note that this function does not account for holidays.
'....................................................................
  Dim daysBetween As Long
  Dim weeksBetween As Long
  Dim daysRemaining As Integer
  Dim tempDate As Date
  If Not IsDate(startDate) Or Not IsDate(endDate) Then Exit Function
  If startDate > endDate Then
    tempDate = endDate
    endDate = startDate
    startDate = tempDate
  End If
  'Add one if the start date is a weekend and the end date goes at least until monday
  If (Weekday(startDate) = vbSaturday And endDate >= startDate + 2) Or _
     (Weekday(startDate) = vbSunday And endDate >= startDate + 1) Then
       EWorkingDays = EWorkingDays + 1
  End If
  Do While startDate <= endDate And Weekday(startDate) <> vbMonday
    'count the days to first monday
    If Not (Weekday(startDate) = 7 Or Weekday(startDate) = 1) Then
      EWorkingDays = EWorkingDays + 1
    End If
      startDate = startDate + 1
  Loop
 

  daysBetween = endDate - startDate
  weeksBetween = daysBetween \ 7
  daysRemaining = daysBetween Mod 7
  ' Debug.Print startDate
  ' Debug.Print EWorkingDays
  ' Debug.Print "Days Rem: " & daysRemaining
  EWorkingDays = EWorkingDays + (5 * weeksBetween)
  startDate = startDate + (7 * weeksBetween)
  'Back to a monday.
  If daysRemaining < 5 Then
    EWorkingDays = EWorkingDays + daysRemaining
  Else
    EWorkingDays = EWorkingDays + 4
  End If
  End Function

the standard method

Public Function WorkingDays(ByVal startDate As Date, ByVal endDate As Date) As Integer
'....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
'           EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

startDate = startDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While startDate <= endDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(startDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
startDate = startDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

and to test both

Public Sub test()
   Dim startTime As Long
   Dim i As Long
   Dim startDate As Date
   Dim endDate As Date
   Dim diff As Long
   Const its = 10000
   startTime = Timer
   startDate = Date
   endDate = startDate + 100
   For i = 1 To its
      diff = WorkingDays(startDate, endDate)
   Next i
   Debug.Print "WorkingDays " & Timer - startTime
   Debug.Print diff
   startTime = Timer
   For i = 1 To its
      diff = EWorkingDays(startDate, endDate)
   Next i
   Debug.Print "EWorkingDays " & Timer - startTime
   Debug.Print diff
   Debug.Print ""
End Sub