434,871 Members | 2,368 Online
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
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 earthlink 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 wrote in message news:... -----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 earthlink 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:... 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:... 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:...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 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:... Trevor Best 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:... Trevor Best 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.