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

[arjun5381]

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!

[orange]

A general query to find the difference in time (seconds) between 2 dates is

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

[arjun5381]

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.

[orange]

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.

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.