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

#### subhanak

##### Board Regular
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

Last edited:

#### jackd

##### Well-known Member
Here is a function and sql that should be close to what you are seeking,

VBA Code:
``````' ----------------------------------------------------------------
' 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;

VBA Code:
``````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

IDStartDateEndDateWeekEndDaysDiff
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​

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Replies
3
Views
180
Replies
6
Views
217
Replies
4
Views
317
Replies
0
Views
99
Replies
3
Views
170

Threads
1,127,919
Messages
5,627,624
Members
416,257
Latest member
salomon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back