Calculating the number of Weekdays between 2 dates. 2

[mcnallyn]

Hi there,

I'd like to be able to calculate the number of working days (Mon-Fri) between 2 dates using a query.

Any assistance would be greatly appreciated.

 

[rjoubert]

Place the following function into a code module, then, in your query pass your two dates into a call to this function...

Public Function NetWorkingDays(StartDate As Date, EndDate As Date) As Integer

   Dim intCount As Integer

   intCount = 0

   Do While StartDate <= EndDate
      Select Case Weekday(StartDate)
         Case 1, 7
            intCount = intCount
         Case 2, 3, 4, 5, 6
            intCount = intCount + 1
      End Select
   
      StartDate = StartDate + 1
   Loop

   NetWorkingDays = intCount

End Function

 

[SgtJarrow]

That would give you the working days, but just as a FYI - it does not account for holidays or the like. Check out faq705-4531 in the Access Modules: VBA Coding forum. Here I list some commonly used functions for different date calculations.....You might find some usefull.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer