Results 1 to 4 of 4
  1. #1
    Dave_01 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    7

    Convert VB code from Date Range to Date/TimeStamp Range

    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
    Last edited by RuralGuy; 01-26-2016 at 11:01 AM. Reason: Added indenting to code

  2. #2
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,898
    Let's try something:
    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] = #" & DateValue(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
    It sure would be nice to have a db to play with.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Dave_01 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Posts
    7
    RuralGuy,

    Wow, I do believe this is a fix!! Once I added the modification, it instantly came up with the correct results for the test data. I've been testing lots of other data with different date frame scenarios (with and without holiday time frames) and each one so far looks to fit the calculation.

    This is a big help for me. I don't have that much VB experience and couldn't put my finger on how to do what you showed me.

    I really appreciate you looking into this and coming up with what looks to be a solution!! I'm going to do some more testing but this looks great so far.

  4. #4
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,898
    Excellent! All I did was strip the time component from the StartDate. I'm glad it worked. If it checks out, go ahead and mark this thread (and probably the other thread you have) as Solved.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Replies: 4
    Last Post: 04-25-2015, 04:17 PM
  3. Replies: 3
    Last Post: 09-11-2013, 09:49 AM
  4. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM
  5. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums