Date diff excluding weekends

[DeanT]

Is there a formula I can use in a query to calc the diff between 2 dates
excluding weekends (Sat and Sun) ?

Thanks

 

[raskew via AccessMonster.com]

Dean -

Try this:
Function DateDiffW(BegDate As Date, EndDate As Date) As Integer
'Returns number of days, excluding Saturday and Sunday
'As written, counts both BegDate and EndDate, e.g.,
'Monday - Friday would count as 5 days
'coded by: raskew

Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate) + 1
End If
End Function

HTH - Bob