Re: Workdays function with Australian Dates

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" <dixie@xxxxxxxxxxx> wrote in message
news:121grcql52u11d@xxxxxxxxxxxxxxxxxxxxx
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: 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)
  • Re: weekends and holidays!
    ... Modify the function to FORCE the AMERICAN format ... So, again, enter the date in YOUR preferred format, and FORCE the ... Do While StartDate < EndDate ... Dim intCount As Integer ...
    (microsoft.public.access.queries)