How do I calculate workdays in access using date diff in a query?

[Leslie]

We are tracking how long it takes to complete product changes in a database
and I would like to use the date diff to calculate the workdays or
networkdays within the database. Is there a way I can do that? In other
databases I do a weeks*5 calculation (workdays*weeks) but these changes
should be completed in days so that formula will not work in this instance.
The datediff does not allow for weekends so something that is taking 2 days
to complete and monday/friday shows up as having taken 4 days.

 

[PC Datasheet]

Leslie,

You have to also consider whether there are holidays between the start and
end dates. So you need to build a holidays table and then include a
procedure in your formula to check if any holiday date in the holiday table
falls between the start and end date.

 

[Leslie]

Yes I'm aware of the holiday issue but I need to know what the formula is, we
only have 6 paid holidays in a year so that isn't a large issue I can do
that, but I need to know what fomula is to use in the query.

 

[PC Datasheet]

From my file ----
Note the use of the holiday table!
Public Function CountWeekdays(dtStart As Date, dtEnd As Date) As Long
Dim dtCurrent As Date
Dim lngWeekDays As Long
Dim lngCount As Long
Dim lngI As Long
lngWeekDays = 0
lngCount = DateDiff("d", dtStart, dtEnd) + 1
If lngCount > 0 Then
For lngI = 1 To lngCount
dtCurrent = DateAdd("d", lngI - 1, dtStart)
If IsWeekday(dtCurrent) Then
If Not IsHoliday(dtCurrent) Then
lngWeekDays = lngWeekDays + 1
End If
End If
Next lngI
End If
CountWeekdays = lngWeekDays
End Function

Private Function IsHoliday(dtTestDate As Date) As Boolean
IsHoliday = -DCount("[Holidate]", "tblHolidays", "[Holidate] = #" _
& CStr(dtTestDate) & "#")
End Function

Private Function IsWeekday(dtTestDate As Date) As Boolean
Dim theDay As Integer
Dim theMonth As Integer
Dim theYear As Integer
Dim theCentury As Integer
Dim intDayNumber As Integer

theDay = Day(dtTestDate)
theMonth = Month(dtTestDate)
If theMonth < 3 Then
theMonth = theMonth + 10
Else
theMonth = theMonth - 2
End If
theYear = Year(dtTestDate) Mod 100
theCentury = Year(dtTestDate) \ 100
intDayNumber = theDay + Int((13 * theMonth - 2) / 5#) + theYear + _
(Int(theYear / 4#)) + Int(theCentury / 4#) - 2 * theCentury
intDayNumber = intDayNumber Mod 7
If intDayNumber < 0 Then intDayNumber = intDayNumber + 7
If intDayNumber = 0 Or intDayNumber = 6 Then
IsWeekday = False
Else
IsWeekday = True
End If
End Function

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com