448,878 Members | 1,341 Online
Need help? Post your question and get tips & solutions from a community of 448,878 IT Pros & Developers. It's quick & easy.

# Workdays function with Australian Dates

 P: n/a I am trying to calculate the number of workdays between two dates with regards to holidays as well. I have used Arvin Meyer's code on the Access Web, but as I am in Australia and my date format is dd/mm/yyyy, I have found that the dates I put in my holidays table are reversed into American dates. So, the wrong holiday dates are subtracted from the total workdays between the start and end dates. Is there an easy fix for this? ******Code follows****** Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate <= EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function******End Code****** Mar 15 '06 #1
12 Replies

 P: n/a Format you date so - Format(StartDate, "mm/dd/yyyy") wherever you use a date in a query for comparison, and add a day so DateAdd("d",1, StartDate) This should help. Jeff Pritchard ________________ Asken Research Pty. Ltd. Access Database Developers http://www.asken.com.au "Dixie" wrote in message news:12************@corp.supernews.com...I am trying to calculate the number of workdays between two dates withregards to holidays as well. I have used Arvin Meyer's code on the AccessWeb, but as I am in Australia and my date format is dd/mm/yyyy, I havefound that the dates I put in my holidays table are reversed into Americandates. So, the wrong holiday dates are subtracted from the total workdaysbetween the start and end dates. Is there an easy fix for this? ******Code follows****** Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate <= EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function******End Code****** Mar 15 '06 #2

 P: n/a It is the holiday dates in the table that are causing the problem. They are not used in a query. The problem is that if 11th March 2006 is a holiday, in my table of holidays, I have 11/03/2006. When the code runs that deducts the holidays from the list of workdays, it interprets this date as 3rd November 2006 - 3/11/2006. I guess I could enter all the holiday dates in American format, but this will inevitably cause problems when others try to enter them as they are not used to dates in the American format. dixie "Jeff" wrote in message news:44***********************@per-qv1-newsreader-01.iinet.net.au... Format you date so - Format(StartDate, "mm/dd/yyyy") wherever you use a date in a query for comparison, and add a day so DateAdd("d",1, StartDate) This should help. Jeff Pritchard ________________ Asken Research Pty. Ltd. Access Database Developers http://www.asken.com.au "Dixie" wrote in message news:12************@corp.supernews.com...I am trying to calculate the number of workdays between two dates withregards to holidays as well. I have used Arvin Meyer's code on the AccessWeb, but as I am in Australia and my date format is dd/mm/yyyy, I havefound that the dates I put in my holidays table are reversed into Americandates. So, the wrong holiday dates are subtracted from the total workdaysbetween the start and end dates. Is there an easy fix for this? ******Code follows****** Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate <= EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function******End Code****** Mar 15 '06 #3

 P: n/a Your system Regional settings should determine the form in which dates are interpreted. I haven't looked at Arvin's code, but it sounds as if some date is in _string_ form. There is only one Date/Time field and it is not even in month/day/year form, but stored as the number of seconds since a particular starting date (Dec. 30, 1899, if you must know). The Regional settings only apply to input and display of the date/time data. Obviously, if you have dates in string form in code, the Regional settings aren't going to affect them. Larry Linson Microsoft Access MVP "Dixie" wrote in message news:12*************@corp.supernews.com... It is the holiday dates in the table that are causing the problem. They are not used in a query. The problem is that if 11th March 2006 is a holiday, in my table of holidays, I have 11/03/2006. When the code runs that deducts the holidays from the list of workdays, it interprets this date as 3rd November 2006 - 3/11/2006. I guess I could enter all the holiday dates in American format, but this will inevitably cause problems when others try to enter them as they are not used to dates in the American format. dixie "Jeff" wrote in message news:44***********************@per-qv1-newsreader-01.iinet.net.au... Format you date so - Format(StartDate, "mm/dd/yyyy") wherever you use a date in a query for comparison, and add a day so DateAdd("d",1, StartDate) This should help. Jeff Pritchard ________________ Asken Research Pty. Ltd. Access Database Developers http://www.asken.com.au "Dixie" wrote in message news:12************@corp.supernews.com...I am trying to calculate the number of workdays between two dates withregards to holidays as well. I have used Arvin Meyer's code on theAccess Web, but as I am in Australia and my date format is dd/mm/yyyy, Ihave found that the dates I put in my holidays table are reversed intoAmerican dates. So, the wrong holiday dates are subtracted from the totalworkdays between the start and end dates. Is there an easy fix for this? ******Code follows****** Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate <= EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function******End Code****** Mar 16 '06 #4

 P: n/a Dates are Dates; there are no Australian dates, or American dates (although Americans invented the notion of dates, along with everything else). There are just dates. This line rst.FindFirst "[HolidayDate] = #" & StartDate & "#" specifies a string for a DAO recordset FindFirst. How does VBA coerce StartDate to be part of the string? TTBOMK it uses your Regional ShortDate settings. So the Find String becomes "[HolidayDate] = #dd/mm/yyyy#" (But for me it gives "[HolidayDate] = #yyyy-mm-dd#" which all technologies I have used understand). I predict the line will work correctly if it is changed to rst.FindFirst "[HolidayDate] = " & Format(StartDate,"\#mm\/dd\/yyyy\#") For StartDate being today, this will result in: [HolidayDate] = #03/16/2006# When using the string structure #Date# one must always be aware of this possible problem. Perhaps it is used in ohter places in your work on this particular issue. I keep a function around in my module of general functions [in every technology] to make it easy not to get burned. In VBA it looks like this: Public Function SQLDateString(ByVal d As Date) As String SQLDateString = Format(d, "\#mm\/dd\/yyyy\#") End Function Mar 16 '06 #5

 P: n/a I am living in Holland. We also use dd/mm/yyyy So I *always* use the lngvalue of dates. ==>>Never have any problems again. I did have similar issues indeed... So StartDate would become Clng(StartDate) and so on. HTH Arno R "Dixie" schreef in bericht news:12*************@corp.supernews.com... It is the holiday dates in the table that are causing the problem. They are not used in a query. The problem is that if 11th March 2006 is a holiday, in my table of holidays, I have 11/03/2006. When the code runs that deducts the holidays from the list of workdays, it interprets this date as 3rd November 2006 - 3/11/2006. I guess I could enter all the holiday dates in American format, but this will inevitably cause problems when others try to enter them as they are not used to dates in the American format. dixie "Jeff" wrote in message news:44***********************@per-qv1-newsreader-01.iinet.net.au... Format you date so - Format(StartDate, "mm/dd/yyyy") wherever you use a date in a query for comparison, and add a day so DateAdd("d",1, StartDate) This should help. Jeff Pritchard ________________ Asken Research Pty. Ltd. Access Database Developers http://www.asken.com.au "Dixie" wrote in message news:12************@corp.supernews.com...I am trying to calculate the number of workdays between two dates with regards to holidays as well. I have used Arvin Meyer's code on the Access Web, but as I am in Australia and my date format is dd/mm/yyyy, I have found that the dates I put in my holidays table are reversed into American dates. So, the wrong holiday dates are subtracted from the total workdays between the start and end dates. Is there an easy fix for this? ******Code follows****** Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate <= EndDate rst.FindFirst "[HolidayDate] = #" & StartDate & "#" If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function******End Code****** Mar 16 '06 #6

 P: n/a Arno R wrote in message <44***********************@text.nova.planet.nl> : So StartDate would become Clng(StartDate) and so on. Arno, have you tried that number trick on SQL server using ADO? My guess would be that there would be a difference of a couple of days when executing something like that on a separate connection to the server vs on linked tables. Then, what about upsizing your db to other platforms ...? -- Roy-Vidar Mar 16 '06 #7

 P: n/a "RoyVidar" schreef in bericht news:mn***********************@yahoo.no... Arno R wrote in message <44***********************@text.nova.planet.nl> : So StartDate would become Clng(StartDate) and so on. Arno, have you tried that number trick on SQL server using ADO? My guess would be that there would be a difference of a couple of days when executing something like that on a separate connection to the server vs on linked tables. Then, what about upsizing your db to other platforms ...? -- Roy-Vidar To be honest I never tried that with ADO. But I did try that with DAO and ODBC-connections to SQL-server. Why would ADO make a difference here? The date-portion of a date-field date is just an integer value isn't it?? Is this not so when using ADO ??? Arno R Mar 16 '06 #8

 P: n/a Arno The base date in MS-SQL is January 1, 1900, The base date in the JET expression service (and VBA), is December 30, 1899. I haven't tested this but it seems to me (and, I think, Roy) that if you send MS-SQL the long, zero (0), it will treat this as January 1, 1900, while you may be expecting it to be treated as December 30, 1899. Of course, this offset will be perpetuated throughout the range of longs that may be coerced to dates. IMO, it always good form to use date functions and date variables only in code, and in SQL to use the string format #yyyy-mm-dd# or #mm/dd/yyyy# only. I have tested none of this and there may be some MS magic that makes what I surmise entirely worng. Mar 17 '06 #9

 P: n/a "Lyle Fairfield" schreef in bericht news:11**********************@j33g2000cwa.googlegr oups.com... Arno The base date in MS-SQL is January 1, 1900, The base date in the JET expression service (and VBA), is December 30, 1899. I haven't tested this but it seems to me (and, I think, Roy) that if you send MS-SQL the long, zero (0), it will treat this as January 1, 1900, while you may be expecting it to be treated as December 30, 1899. Of course, this offset will be perpetuated throughout the range of longs that may be coerced to dates. IMO, it always good form to use date functions and date variables only in code, and in SQL to use the string format #yyyy-mm-dd# or #mm/dd/yyyy# only. I have tested none of this and there may be some MS magic that makes what I surmise entirely worng. Hmmm, never paid any attention to this difference in base date between SQl and Jet. :-( As I said: I have a few apps with a SQL-backend (upsized from a mdb backend during the lifetime of the app) I use ODBC-connections to SQL-server and never noticed a problem using Clng(Date). Also I have an app like that where I am using Jet-temptables (with lots of date-math) in a separate Temp.mdb. So I have ODBC-links to SQL and links to the temptables. No problem until now. Al least no problems are reported with this app (a 7x24 multi-user app with lots of scheduling) This Clng-'trick' I learned years ago (Access 2.0 days) and I am (or was!) very happy with using the 'trick'. However I understand what you (and Roy) are saying ... There are no Australian Dates (as posted in this thread) but SQL-Dates and Jet-Dates are different animals... I will do some tests to see if I need to be worried. My guess is the following: When I use query's, even query's with both jet-tables and SQL-tables either way Jet does the job, or SQL-server does the job, and I guess both will treat the Clng(Date)-values 'right'. Or ... am I saying something very stupid here ?? Arno R Mar 17 '06 #10