Results 1 to 4 of 4
  1. #1
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12

    How To Get Elapsed Time Excluding Weekend, Holidays & Off Hours MS Access VBA

    Hi,
    I need MS Access VBA to calculate the time difference between 2 date/time columns. I need to exclude Weekends (Saturday, Sunday), Holidays & Off Hours (After 6:30 PM To 9:30 AM).
    If any Task Generated After 6:30 PM then it will consider for next working day.




    Please find the attached MS Access File for Your Reference. For Closed Status ElapsedTime Difference Between [Closed_Date] - [Open_Date] and For Pending Status ElapsedTime Difference Between Now() - [Open_Date]


    I Need your help to solve my problem........thanks in advance for all of your help!
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,728
    A general query to find the difference in time (seconds) between 2 dates is

    Code:
    SELECT SR_Data.SR_Number, SR_Data.Open_Date, SR_Data.Closed_Date, DateDiff("s",[Open_Date],[Closed_date]) AS ET_seconds
    FROM SR_Data
    WHERE (((SR_Data.Closed_Date) Is Not Null));
    You do not have a Holidays table. You will need one if you plan to account for Holidays.

    With ms Access Saturday is Weekday 7 and Sunday is Weekday 1, so if these are your weekend days you would have to ensure(account for) records where the date represents a weekend day.

    Good luck

  3. #3
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12
    Thanks a lot for answering but still my problem is not resolved, please find new attachment where Holidays Table is available.

    I want to exclude below 3 Things :

    1. Weekends (Saturday & Sunday)
    2. Holidays
    3. Off Time From 6:30 PM To 9:30 AM

    Please solve my problem, i need your help.
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,728
    Here are some things that may help you with your project.

    I have a function fworkingdays to determine business days between 2 dates. It accounts for weekends and holidays.
    It does NOT deal with Time, only Days/Business Days.

    I used the function, but had to rename your holiday table and the holiday date field.
    Here is the query I created--it does NOT deal with your OFF TIME. I'm sure you can work from this base.

    Code:
    SELECT SR_Data.SR_Number
    , SR_Data.Open_Date
    , SR_Data.Closed_Date
    , IIf(IsNull([closed_date]),"N/A",fworkingdays([open_date],[closed_date],"1,7")) AS busdays
    FROM SR_Data;
    Hopefully this will help you with your project.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-25-2014, 11:32 AM
  2. Replies: 5
    Last Post: 07-01-2014, 02:28 PM
  3. Replies: 14
    Last Post: 09-11-2013, 01:10 PM
  4. Replies: 1
    Last Post: 05-01-2013, 10:53 AM
  5. Replies: 8
    Last Post: 10-20-2012, 11:25 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
  •  
Other Forums: Microsoft Office Forums