By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Alternate function to count weekdays

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
-----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:
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


Nov 13 '05 #2

P: n/a
MGFoster <me@privacy.com> wrote in message news:<Y6****************@newsread1.news.pas.earthl ink.net>...
-----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-----


Thanks. That's much simpler. I guess that math degree was worthless
after all :-). Sigh.

James A. Fortune

I'm sorry. My responses are limited. You must ask the right
questions. --- Alfred J. Manning, I, Robot.
Nov 13 '05 #3

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
I'm sorry. My responses are limited. You must ask the right
questions. --- Alfred J. Manning, I, Robot.


That was Lanning, not Manning!

As long as I'm here:

Private Function IsEaster(dtTestDate As Date) As Boolean
Dim F As Integer
Dim M As Integer
Dim N As Integer
Dim y As Integer
Dim EDay As Integer
Dim EMonth As Integer

IsEaster = False
If Month(dtTestDate) < 3 Or Month(dtTestDate) > 4 Then Exit Function
If Month(dtTestDate) = 3 And Day(dtTestDate) < 22 Then Exit Function
If Month(dtTestDate) = 4 And Day(dtTestDate) > 26 Then Exit Function
M = 24
N = 5 'At year 2100 set this to 6 :-)
y = Year(dtTestDate)
F = (19 * (y Mod 19) + M) Mod 30 + (2 * (y Mod 4) + 4 * (y Mod 7) _
+ 6 * D + N) Mod 7
If F > 9 Then
EMonth = 4
EDay = F - 9
Else
EMonth = 3
EDay = 22 + F
End If
If Month(dtTestDate) = EMonth And Day(dtTestDate) = EDay Then
IsEaster = True
End If
End Function

James A. Fortune

Seen on a bumper sticker:
If you're happy with Kerry or Bush you haven't been paying attention.
Nov 13 '05 #4

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
F = (19 * (y Mod 19) + M) Mod 30 + (2 * (y Mod 4) + 4 * (y Mod 7) _
+ 6 * D + N) Mod 7


F = (19 * (y Mod 19) + M) Mod 30 + (2 * (y Mod 4) + 4 * (y Mod 7) _
+ 6 * ((19 * (y Mod 19) + M) Mod 30) + N) Mod 7

Rough week.

James A. Fortune
Nov 13 '05 #5

P: n/a
James Fortune wrote:
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

F = (19 * (y Mod 19) + M) Mod 30 + (2 * (y Mod 4) + 4 * (y Mod 7) _
+ 6 * D + N) Mod 7

F = (19 * (y Mod 19) + M) Mod 30 + (2 * (y Mod 4) + 4 * (y Mod 7) _
+ 6 * ((19 * (y Mod 19) + M) Mod 30) + N) Mod 7

Rough week.

James A. Fortune


Isn't Easter the first sunday after the first full moon after the first
day of spring? How does the function know the moon phase?
Nov 13 '05 #6

P: n/a
Trevor Best <no****@besty.org.uk> wrote in message news:<41**********************@news.zen.co.uk>...
Isn't Easter the first sunday after the first full moon after the first
day of spring? How does the function know the moon phase?


I'll see if I can track down the documentation others have written
about C.F. Gauss' computations.

James A. Fortune

If I can automate Access programming enough I can pretend to be a
geographically distributed offshore development team. :-)
Nov 13 '05 #7

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
Trevor Best <no****@besty.org.uk> wrote in message news:<41**********************@news.zen.co.uk>...
Isn't Easter the first sunday after the first full moon after the first
day of spring? How does the function know the moon phase?


I'll see if I can track down the documentation others have written
about C.F. Gauss' computations.

James A. Fortune

If I can automate Access programming enough I can pretend to be a
geographically distributed offshore development team. :-)


Following information from a post on the subject, I ordered a book I
think was called "Oxford Guide to Time." I should get it on Monday or
Tuesday. Hopefully, I will be able to find the answer to your
question. I just wrote an Access database that creates a calendar in
pdf format similar to a calendar created by pscal. The reason for the
CalendarPDF program was for displaying due dates for jobs on a
calendar. I created a scale transformation option on the entire
document with the 'cm' operator while expanding the MediaBox and
CropBox so that the calendar can be printed on the plotters used for
blueprints. It even has the previous and next month insets with a
boolean variable that keeps the inset from having insets. If the book
also allows me to calculate moon phases I can place moon phases and
holidays optionally on the calendar. Right now the calendar prints
with Acrobat Reader but not with ghostscript GView.

James A. Fortune

Two of the top nineball players in the world, Alex Pagulayan and
Allison Fisher, each weigh about 105 pounds.
Nov 13 '05 #8

P: n/a
James Fortune wrote:
ja******@oakland.edu (James Fortune) wrote in message

news:<a6**************************@posting.google. com>...
Trevor Best <no****@besty.org.uk> wrote in message news:<41**********************@news.zen.co.uk>...
Isn't Easter the first sunday after the first full moon after the first day of spring? How does the function know the moon phase?


I'll see if I can track down the documentation others have written
about C.F. Gauss' computations.

James A. Fortune


Easter Function explanation Part I

The information for this post is contained in "The Oxford Companion to
the year."

The reason this is taking so long is that I have to go through a ream
of information (literally) to extract the pertinant information
necessary to understand C. F. Gauss' calculations. I decided to try to
infer all the pieces of the puzzle using the information in the
Companion rather than look up the final explanation in another source.
I will try to avoid the confusion of earlier times caused by dealing
with the rates of the Earth on its axis, the Moon around the Earth and
the Earth around the Sun. Note that the time from a new Moon to a new
Moon is easier to observe than its period of revolution around the
Earth. A culture living on any planet revolving around a star and
having one moon would almost certainly face the exact same issues. I
apologize to the authors in advance if I paraphrase any of the
information incorrectly.

In order to assist in computing Easter it is advantageous to construct
a lunar calendar. Since some cultures already use a lunar calendar
much is known about adjusting them to the solar year and keeping the
vernal equinox date at about the same day each year. An Athenian
astronomer named Meton (c. 432 B.C.) observed that 235 lunar months is
roughly equivalent to 19 solar years. I.e.,

235 lunar months = 6939.68865 days
19 solar years = 2939.6018 days

based on what we know now that the period from a new moon to another
new moon is 29.53059 days and a solar year = 365.2422 days. This
observation is referred to as the Metonic Cycle. In addition to a
constructed lunar calendar it is also advantageous to consider the
differences between the Julian calendar and the Gregorian calendar.
The Julian calendar acts as a bridge between the Gregorian calendar in
use today and our constructed lunar calendar. Since 365.2422 is close
to 365.25, the Julian calendar is one in which a leap year occurs each
and every four years. The Julian calendar, after a few bumpy starts
has been kept continuously since 8 A.D. The book notes that this start
date conveniently makes leap years divisible by four. The difference
of 11 minutes 12 seconds kept accumulating each year until people
recognized in the 13th century that the difference between the calendar
and the true positions of the sun and moon were getting beyond
acceptable limits. The Council of Trent in 1563 authorized the papacy
to correct the calendar. Pope Gregory XIII started his reform in 1579
and adjusted the calendar in 1582. The Gregorian calendar includes a
correction to make up for the drift caused by the Julian approximation
plus new leap year rules to get closer to the 365.2422 value.

James A. Fortune

It is a curious fact that the same side of the moon always faces the
Earth. It's not what you'd expect. There's no apparent physical
reason for it to be that way. Some have theorized a dumbbell-shaped
core of the moon as a possible explanation of why the moon always
presents the same aspect. -- Wayne Noss

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.