Workdays function with Australian Dates

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******


.

Relevant Pages

  • Re: Workdays function with Australian Dates
    ... 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 ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (comp.databases.ms-access)
  • Re: Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Workdays Function : Repost
    ... which includes provision for holidays (I also had to select Microsoft DAO 3.6 ... EndDate As Date) As Integer ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (microsoft.public.access.formscoding)
  • RE: Calculating Holidays
    ... in removing statutory holidays. ... DtmEnd As Date) As Integer ... StartDate ... Dim intCount As Integer ...
    (microsoft.public.access.modulesdaovba)
  • Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)