G
Guest
I am using this function to count the number working days (excluding
weekends and public holidays).
The "startdate" and "enddate" are formatted as dd/mm/yyyy hh:mm:ss
How can I change the function to calculate parts of days as well as whole
days. I appreciate the answer might be obvious (even to me) but I would like
to check.
_________________________
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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
weekends and public holidays).
The "startdate" and "enddate" are formatted as dd/mm/yyyy hh:mm:ss
How can I change the function to calculate parts of days as well as whole
days. I appreciate the answer might be obvious (even to me) but I would like
to check.
_________________________
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
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] = #" & Format(StartDate, "mm-dd-yyyy") & "#"
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