I'm doing some computations in order to do capacity planning. Instead
of using some function from an Access book to do the weekday
calculation, I decided to come up with an alternate method since a
typical capacity report will look only at hours quoted for the next
six months. I remembered Zeller's Congruence from a Number Theory
class I took at O.U. (that extra Bachelor's Degree in Applied
Mathematics was useful after all) and adapted some code I googled.
Note: Because C.F. Gauss came up with an algorithm for calculating
Easter it's possible to write functions to determine whether a given
date falls on any holiday. For example, Memorial Day (U.S.) always
falls on the last Monday in May. So, in theory, I can replace
tblHoliday by using functions for each holiday and having the user
click which holidays to exclude on a form. The function below worked
for several scenarios I tried out by using a form with two calendar
controls and a command button. Also, holidays falling on weekends
don't have to be placed in tblHoliday. But, once they saw that it
only took an hour and a half to google for code, google for holiday
definitions, write the functions and test them the first thing they
said was, "We'd like checkboxes for including Saturdays or Sundays
that don't fall on holidays also."
'----------------------------------------------------------------------
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
'Use Zeller's Congruence to determine day of week
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
'----------------------------------------------------------------------
James A. Fortune
Temp email: jimfortune AT compumarc DOT com
