MS Access (VBA) : Calculating the exact elapsed hours between two dates excluding weekends

[subhanak]

Hi,
I have an access table that includes fulfillment dates/time related to orders. I would like to calculate the exact elapsed hours between two dates excluding weekends. is there anyway to achieve such calculation ?
Please let me know.

StartDate	EndDate
06/13/2020 19:12:08​	06/14/2020 15:12:08​
06/12/2020 11:47:46​	06/15/2020 09:47:46​
06/11/2020 00:00:00​	06/16/2020 23:59:59​
06/11/2020 02:12:08​	06/11/2020 19:12:08​
06/14/2020 00:12:08​	06/15/2020 19:12:08​
06/12/2020 00:12:08​	06/13/2020 19:12:08​

Your assistance is appreciated.
Thanks,
Ben

 

[jackd]

Here is a function and sql that should be close to what you are seeking,

' ----------------------------------------------------------------
' Procedure Name: WeekEndDays
' Purpose: Routine to count the number of weekend days between Startdate
' and Enddate. This routine does not deal with Holidays.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter startdate (Date):
' Parameter enddate (Date):
' Return Type: Integer
' Author: Jack
' Date: 17-Jun-20
' ----------------------------------------------------------------
Function WeekEndDays(StartDate As Date, enddate As Date) As Integer
    On Error GoTo WeekEndDays_Error
          Dim HoldDate As Date
          Dim i As Integer
          Dim WEDayCount As Integer
10        WEDayCount = 0
20        If Not IsDate(StartDate) Then Exit Function
30        If Not IsDate(enddate) Then Exit Function
40        If enddate < StartDate Then  'reverse the dates supplied they're in wrong order
50            HoldDate = enddate
60            enddate = StartDate
70            StartDate = HoldDate
80        End If
90        Do While StartDate <= enddate  'iterate over the time period
100           Select Case Weekday(StartDate)
                  Case 1, 7                                                  'if it's a weekend day
110                   WEDayCount = WEDayCount + 1    'add 1 to the weekendday count
       
120               Case Else
130           End Select
140           StartDate = StartDate + 1      'move the start date 1 day forward
150       Loop
160       WeekEndDays = WEDayCount  'return the count of weekend days

    
    On Error GoTo 0
WeekEndDays_Exit:
    Exit Function

WeekEndDays_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure WeekEndDays, line " & Erl & "."
    GoTo WeekEndDays_Exit
End Function

And here is a query that uses the function;

SELECT TblBEN.ID
, TblBEN.StartDate
, TblBEN.EndDate
, weekenddays(startDate,endDate) as WeekEndDays
, DateDiff("h",[StartDate],[Enddate])  - iif(weekenddays(startDate,endDate)<2,0,weekenddays(startDate,endDate)) AS Diff
FROM TblBEN;

Here's a sample with some test data I mocked up. I added some comments in the function to help you with vba and the logic involved.
Good luck.

qryBenHours qryBenHours

ID	StartDate	EndDate	WeekEndDays	Diff
1​	13-Jun-20 7:12:08 PM​	13-Jun-20 11:12:08 PM​	1​	4​
2​	12-Jun-20 11:47:46 AM​	30-Jun-20 9:47:46 AM​	6​	424​
3​	12-Jun-20 11:00:00 AM​	15-Jun-20 9:00:00 AM​	2​	68​