Remove Holiday's and Weekend Involving Time Stamps

[Dave_01]

Hi,



Needing help please in possibly correcting the VB code below to remove holidays and weekends for time stamp data. I use a table called tblHolidays (column name 'HolidayDate') with the Function below to remove holidays 12/24/15 and 12/25/15 out of the calculation. However the results seems to calculate 1 day less than what it should. Basically if someone started a task on 12/21/15 11:15 AM and finished on 12/28/15 11:17 AM - I would think that the result should be three days instead of two - given that there were 2 corporate holidays and 2 weekend days and then subtracted from a total of 7 days.

For the second row, I can see the code reducing by one day compared to the first row - since it wasn't quite a full 7 days (6.99), but the second row seems like it should be 2 days removing 4 days of holidays and weekends.

Any help is appreciated. Thanks!


Query:
Time_Minus_Holidays_Minus_Weekends: WorkingDays2([Begin_Date_Time],[End_Date_Time])

Result: Begin_Date_Time End_Date_Time Time_Minus_Holidays_Minus_Weekends Real_Time_No_Holidays_No_Weekends 12/21/15 11:15 AM 12/28/15 11:17 AM 2 7.001 12/21/15 11:20 AM 12/28/15 11:18 AM 1 6.999

Option Compare Database
Option Explicit

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
   intCount = 0
   Do While StartDate <= EndDate
      'rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
      rst.FindFirst "[HolidayDate] >= #" & StartDate _
                    & "# AND [HolidayDate] <= #" & EndDate & "#"
      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

[RuralGuy]

Here's a link to what a lot of us use:
http://access.mvps.org/access/datetime/date0006.htm

[Micron]

I'd start with the Holiday table if you copied or downloaded, rather than create it, to ensure there isn't a holiday in there that you don't need if the variance is 1. However, I'm guessing you want the Real_Time calculation to be 4 but it is 7? Perhaps it's because you altered [HolidayDate] to be >= from the original (=) and added an AND clause. I think the Do While negates the need for the <= part. Did it ever work as originally written?

[Dave_01]

Hi RuralGuy,

Thanks so much for your reply.

I put in the codes you sent me and did a comparison. I get a variation using a timestamp vs. date without a timestamp. I'm thinking of course that it's because of the dates without a timestamp default to 12:00:00 AM.

Although the dates by themselves look more realistic, it seems like the timestamp rows should be closer to 3 days for the first one and 2 days for the second. Unfortunately our company has to use the timestamp format and for some reason those formulas seem to work better for a simple date range.

I put in both formulas and using the Function WorkingDays, I get the results immediately below.

Then using WorkingDays2, I get the second result below. I'm just not sure how to get it work better for our timestamp data.

WorkingDays
Begin_Date_Time	End_Date_Time	Time_Minus_Holidays_Minus_Weekends
12/21/15 11:15 AM	12/28/15 11:17 AM	5
12/21/2015	12/28/2015	5
12/21/15 11:20 AM	12/28/15 11:18 AM	4
12/21/2015	12/28/2015	5
WorkingDays2
Begin_Date_Time	End_Date_Time	Time_Minus_Holidays_Minus_Weekends
12/21/15 11:15 AM	12/28/15 11:17 AM	6
12/21/2015	12/28/2015	4
12/21/15 11:20 AM	12/28/15 11:18 AM	5
12/21/2015	12/28/2015	4

Again, I really appreciate your reply.

[Dave_01]

Hi Micron,

Thanks for your help on this. I checked the dates in the Holiday table and using only our corporate holidays. Thanks for the suggestion on that.

I had modified the original formula because the first one (where I commented out) calculated the data correctly only if it was just a date range as opposed a time stamp. When I added the modification, it calculated most of the time stamp data correctly, but during the holidays, it doesn't do as well. It calculates too little time.

I used the real time calculation as a starting point and then subtracted 4 days from 7 to get 3 days, but winding up with only 2 days after running the query.

I did change the <= back to the original = and then it calculated 5 days for the first row and 4 days for the second - so it went a different direction. Now, when I remove the time stamp out of the rows, I do get 3 so it's better with what you suggested. Unfortunately the formula seems to only work with a date frame only.

If I remove the AND clause, the timestamp rows only calculates the weekends and not the holidays. So the first clause seems to work only for the single date ranges.

Bottom line is: The first clause works only for single date ranges and the modification works for timestamp, but not calculating enough time during the holidays. I'm just not sure what else to change to make it work right.

Thanks for looking into this.

[Micron]

I presumed all of your records included a time stamp. I've seen this sort of thing before when using the BETWEEN operator. You are correct that the default time is 00:00:00, so you willl not get anything on the last day of a date range when the field has date AND time and time is not provided. So you have some options and decisions to make. Using the DateAdd function, do you run a routine that adds 23 hours and 59 seconds to a date where the time is 00:00:00 and ensure the time is always part of the record after that? Maybe in your code above, look for values that have no time component and add the 23:59? Or write a routine that strips the time component from the record and you just add 1 day to the end date that is chosen by the user? Or migrate the end date values to a new field and strip the time component? In any case where there is no time component, you will have to use DateAdd in your end date criteria or add the extra day by default. I think the former would be more reliable, but your main problem is the inconsistency of the data. It should all have a proper time stamp, or none at all.

[Dave_01]

Hi,

I was only using the dates as a comparison basically to show the calculated difference. I only receive date/timestamp data and since it's always to the second - I never do any modifications. Forgive me - I'm not very good at code so my understanding in limited in changing VB. If I change the query to convert to a date only range - I'm afraid of the query calculating a full day when typically it calculates 0 for any activity performed in less than 24 hours.

Sorry if I didn't answer all your questions. I'm trying to not do too much more than allow for holidays.

Thanks

[Micron]

Then I can only add two options: put a break on the code and step through it, and monitor the values being assigned to the variables. One thing you can do is, in the "immediate window" type a queston mark and the variable name (e.g. ?StartDate) and hit return to get the value, or mouse over the variable in code. A line that is highlighted in yellow has not been processed yet, so the values you see may not have been assigned. You'd have to hit F8 again to step to the next line. I'm wondering if your problem might be due to passing incomplete values to the function.

The other is to post a stripped down and zipped copy of your db for analysis so we can see the data and the code execution.

[Dave_01]

Micron,

I changed the code back to the original - in another test scenario and posted from a different angle. I received the code modification below for the original and it seems to be calculating correctly.

What you've provided could may very well solve the issue. It's definitely way above my limited skill level in coding. I'm going to do some more testing and mark complete if all pans out.

Thank you for your help. I really appreciate it!

rst.FindFirst "[HolidayDate] = #" & DateValue(StartDate) & "#"