Hi,
I'm at my wits end trying to find a solution an issue removing weekends and holidays from a date/timestamp perspective. A commonly used code out there doesn't really work for an extended date/timestamp format. (example 12/21/2015 11:15:31 AM)
The VB coding used (in conjunction with my holidays table) seems to only remove the holidays from a 'Date' standpoint. (date/timestamp will not effectively work for holidays)
How can I adjust the VB code that I use for removing holidays and weekends to work for dates that include timestamps? The weekends doesn't seem to be an issue. Only the holidays.
The example below shows that 3 days on row 2 is what I should be calculating during the late December time frame with the 24th and 25th as corporate holidays (added in my holidays table). However if you notice the 'date /time stamp' rows, the VB code only accommodates the weekend but not the holidays.
The First row should calculate 3 days because it was a full 7 day interval to begin with. And remove 4 holiday/weekend days and the result should be 3 (the same result as row 2)
The Third row should calculate 2 days because it was only a full 6 days. (1 minute or so shy of being 7). Remove 4 holiday/weekend days and the result should be 2.
Is there a way to add in a formula to accept a date/timestamp format?
Thanks!!
Begin_Date_Time |
End_Date_Time |
Time_Minus_Holidays_Minus_Weekends |
12/21/2015 11:15:00 AM |
12/28/2015 11:17:00 AM |
5 |
12/21/2015 |
12/28/2015 |
3 |
12/21/2015 11:20:00 AM |
12/28/2015 11:18:00 AM |
4 |
12/21/2015 |
12/28/2015 |
3 |
Code:
Option Compare Database
Option Explicit
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