Hi everyone, im hoping this is an easy question to answer.
I found some code online for add or subtracting working days and excluding holidays utilizing a table to list the bank holidays etc.
the function seems to be switching the dd/mm if its possible,
i have a holiday date 10/04/2020 (10-april-2020) and the function is not excluding this, instead it seems to read it as 04/10/2020 (04/Oct-2020)
if the date cannot be read in this way the function works fine,
Holiday date 13/04/2020 (13-april-2020) it excludes it.
here is a screenshot example:
Table:
Query Results:
As you can see, if its possible to swap the mix the dd & mm on the date the function seems to
The 5th lines is an example to demonstrate that the function has read the 08/05/2020 from the table as (05-Aug-2020) when it should not be.
Here is the code i have copied:
The other function for Counting working days between 2 dates has the same problem.
To cut a very long story short, my question is
Is there something i can add to the module to specify it should read the HolidayDate from TblHolidays in the dd/mm/yyy format?
Thanks in advance for any help
I found some code online for add or subtracting working days and excluding holidays utilizing a table to list the bank holidays etc.
the function seems to be switching the dd/mm if its possible,
i have a holiday date 10/04/2020 (10-april-2020) and the function is not excluding this, instead it seems to read it as 04/10/2020 (04/Oct-2020)
if the date cannot be read in this way the function works fine,
Holiday date 13/04/2020 (13-april-2020) it excludes it.
here is a screenshot example:
Table:
Query Results:
As you can see, if its possible to swap the mix the dd & mm on the date the function seems to
The 5th lines is an example to demonstrate that the function has read the 08/05/2020 from the table as (05-Aug-2020) when it should not be.
Here is the code i have copied:
Code:
Function aWorkdays(startDate As Date, _
NumBusinessDays As Long, _
Optional Adding As Boolean = True, _
Optional ShowDebug As Boolean = False) As Date
Dim Enddate As Date
Dim TempDate As Date
Dim numWorkDays As Long
Dim BusinessDays As Integer
Dim i As Integer
Dim isWeekend As Boolean 'True if the date is a weekend day
Dim isHoliday As Boolean 'True if the date is a Holiday
10 On Error GoTo aWorkdays_Error
20 numWorkDays = NumBusinessDays
30 TempDate = startDate
Dim TWeekDay As Integer
40 For i = 1 To 9999 'just a large number
'reset the Holiday and weekendday flags
50 isHoliday = False
60 isWeekend = False
70 If ShowDebug Then Debug.Print "Tempdate is " & TempDate
'Get some facts
'is this date a holiday
80 If DCount("*", "tblHolidays", "HolidayDate=#" & TempDate & "#") = 1 Then
90 isHoliday = True
100 If ShowDebug Then Debug.Print TempDate & " is a Holiday--------H"
110 End If
'is this date a weekend
120 TWeekDay = Weekday(TempDate)
130 Select Case TWeekDay
Case 1, 7 'is weekend not a business/work day
140 isWeekend = True
150 If ShowDebug Then Debug.Print TempDate & " is a weekend day----W"
160 Case Else
170 End Select
'Count the Business day
180 If Not isHoliday And Not isWeekend Then
190 BusinessDays = BusinessDays + 1
200 If ShowDebug Then Debug.Print TempDate & " is a Business Day"
210 End If
'check if we have found the end date
220 If BusinessDays = numWorkDays Then
230 GoTo Finished
240 ElseIf Adding Then 'this handles Adding
250 TempDate = TempDate + 1
260 Else
270 TempDate = TempDate - 1 'this does the subtracting
280 End If
290 Next i
Finished:
300 If ShowDebug Then
310 If Adding Then
320 Debug.Print "Adding " & numWorkDays & " business days to " & startDate & " is " & TempDate
330 Else
340 Debug.Print "Subtracting " & numWorkDays & " business days from " & startDate & " is " & TempDate
350 End If
360 End If
370 aWorkdays = TempDate
380 On Error GoTo 0
390 Exit Function
aWorkdays_Error:
400 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure aWorkdays of Module mellonAccessRelated"
End Function
The other function for Counting working days between 2 dates has the same problem.
To cut a very long story short, my question is
Is there something i can add to the module to specify it should read the HolidayDate from TblHolidays in the dd/mm/yyy format?
Thanks in advance for any help