I'm trying to count the number of workdays in a month, excluding weekends and holidays as listed in a separate table.
In order to do this I'm using the function developed by Arvin Meyer:
Simple...as long as the computer's regional settings are set to US.
The problem is my Access dates are in British format (dd/mm/yyyy) but the DAO Recordset retrieves the holiday dates in the US format (mm/dd/yyyy).
How do I resolve this (without changing the regional settings on the computer to conform to US settings)?
Any help would be appreciated. Thanks.
In order to do this I'm using the function developed by Arvin Meyer:
Code:
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
Simple...as long as the computer's regional settings are set to US.
The problem is my Access dates are in British format (dd/mm/yyyy) but the DAO Recordset retrieves the holiday dates in the US format (mm/dd/yyyy).
How do I resolve this (without changing the regional settings on the computer to conform to US settings)?
Any help would be appreciated. Thanks.