Arthur Fuller
artful at rogers.com
Mon Sep 19 01:00:11 CDT 2005
This code may work for some specific country, but woe to the person who must internationalize it! There is no shortcut to the solution (IMO) other than creating a Holidays table and entering therein the holidays that are particular to your nation, or the user's nation, should you happen to have transnational marketing ambitions. July 1 in Canada is important, and July 4 in USA is important. Neither nation considers the other date important. We (in Canada) regard July 4 as just another working, lest it fall upon a Saturday or Sunday, and the converse holds in the USA regarding July 1. Further, and again this comes from a Canadian perspective, there are holidays in various provinces that are not recognized as such in other provinces, so even if your market is Canada-only, you have to graduate beyond this algorithm. Suppose you have a table called Holidays. Then you take the Start and Stop days and count the Holidays between (noting those occasions when a holiday happens to fall on a Saturday or Sunday. Then you can apply the rest of the logic. But even then, who is to say that Saturday and Sunday are not working days? I can go to many stores on both days and find them open. And for some reason that I have never nailed down, numerous East Indian restaurants in this 'hood close on Mondays. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Klos, Susan Sent: September 16, 2005 11:49 AM To: accessd at databaseadvisors.com Subject: [AccessD] Finding weekdays within a date range I have actually been able to find the weekdays. However, I need to set some parameters and I can't seem to do that. I borrowed the following code from someone's web site. It works perfectly for some of what I need. Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer '.................................................................... ' Name: WorkingDays ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: February 19, 1997 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function does not account for holidays. '.................................................................... On Error GoTo Err_WorkingDays Dim intCount As Integer 'StartDate = StartDate + 1 'If you want to count the day of StartDate as the 1st day 'Comment out the line above intCount = 0 Do While StartDate <= EndDate 'Make the above < and not <= to not count the EndDate Select Case Weekday(StartDate) Case Is = 1, 7 intCount = intCount Case Is = 2, 3, 4, 5, 6 intCount = intCount + 1 End Select StartDate = StartDate + 1 Loop WorkingDays = intCount Exit_WorkingDays: Exit Function Err_WorkingDays: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays End Select End Function I need to be able to modify this to find the weekdays under the following criteria 1) if the inServiceBegin is after FCATWeek (a single date) then exit the function. 2) if the inServiceBegin is before SchoolStart (a single date) then use SchoolStart and the startdate. 3) if the inServiceEnd is after FCATWeek then use FCATWeek as the enddate. 4) if the inServiceBegin is not null but the inServiceEnd is null (inservice is only one day long) then 1. 5) if the inServiceBegin and inServiceEnd are before SchoolStart then exit the function. I think I have considered all the possibilities. I am trying to calculate how many weekdays fall within the range of inservice days and these must fall on or after the start of school and on or before the students take the test (FCATWeek). I can use whatever help you can give me. Thanks. Susan Klos Senior Database Analyst Evaluation and Reporting Florida Department of Education 850-245-0708 sc 205-0708 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com