-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
With all due respect to Zeller's Congruence:
Function IsWeekday(dtTestDate As Date) as Boolean
IsWeekday = (WeekDay(dtTestDate) > 1 And WeekDay(dtTestDate) < 7)
End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQXrt/4echKqOuFEgEQIlyQCfVDJeNX4aCOCuAELfwWQpAE2kkI4AoKP y
8Bosq51wyk7iCLTD0FHQiIK+
=CaOh
-----END PGP SIGNATURE-----
James Fortune wrote:
[color=blue]
> 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[/color]
