working days calcualation

[Rick B]

Do a search this is asked and answered all the time.

 

[Brent]

I'm a novice access user and want to have a query calculate the number of
working days between 2 dates in a record set. In excel I accomplish this
function with the "networkingdays" function. Does access have a similar
function or can anyone provide sample code to only count weekdays? Thanks

 

[Edward G]

Hi Brent,

You might try this. Open the modules tab of the database window in your
database. Click New.
Copy and paste the code that follows into the open module. Then save it and
name it.
Now open your query and create an expression similar to this:
Weekdays: DateDiffW([yourfirstdatefield],[yourseconddatefield])
Hope that helps.


Function DateDiffW(BegDate, EndDate)
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)
End If
End Function