Exclude weekend in date field for time between calculation (1 Viewer)

bruceblack

Registered User.
Local time
Today, 12:38
Joined
Jun 30, 2017
Messages
119
Hi everyone!
Stuck here defining weekends en exluding them in my query.

I have 4 fields in my query:
inbounddate and outbounddate, and inboudtime and outboundtime.

I COMBINED these fields in my query:
combistart and combiend.

The formatting of these 2 fields looks like this:
5-1-2018 9:04:42

I would like to calculate how long a certain product has been waiting, before it got send. BUT, excluding saturday and sunday.
(so dont count the weekends)

But im having trouble to define what those days are.
As we are not working saturday and sunday, i also dont have data on those days, so i cant exlude them!

Im calculating the time difference like follows:

timediff: DateDiff("h";[combistart];[combiend])

Which works fine. But how to exclude the weekends?

Thank you guys so much in advance!!!!!!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:38
Joined
Feb 19, 2013
Messages
16,555
see the bottom of your thread for a number of solutions

typically you will need to use a vba function but there is one -called 'exclude weekend in query' which is done in the query.
 

plog

Banishment Pending
Local time
Today, 07:38
Joined
May 11, 2011
Messages
11,613
I have 4 fields in my query:
inbounddate and outbounddate, and inboudtime and outboundtime.

I would fix that at the table level. The correct type to use is Date/Time, which can hold both of those values in one field. Life will be easier if you put the corresponding date and time values into one field in the table.

From there, for this issue--search the forum. At least twice a month this is asked and answered on the forum. Use the Search function in the top tool bar, or scroll down on this very page to see the "Similar Threads" area.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:38
Joined
May 7, 2009
Messages
19,175
Copy and paste the code in a module:
Code:
Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long
Dim StartDate As Date, EndDate As Date, _
        WeekendDays As Long, i As Long
If Date1 > Date2 Then
    StartDate = Date2
    EndDate = Date1
Else
    StartDate = Date1
    EndDate = Date2
End If
WeekendDays = 0
For i = 0 To DateDiff("d", StartDate, EndDate)
    Select Case Weekday(DateAdd("d", i, StartDate))
        Case 1, 7
            WeekendDays = WeekendDays + 1
    End Select
Next
CountWeekendDays = WeekendDays
End Function

Now add this to youre query:

timediff: DateDiff("h";[combistart];[combiend])-(CountWeekendDays([combistart],[combiend])*24)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:38
Joined
Feb 19, 2002
Messages
42,981
Here's a sample of useful date utilities. The business days function should also exclude holidays. You just have to populate the holiday table with the holidays you want to exclude.
 

Attachments

  • UsefulDateFunctions180327.zip
    237.5 KB · Views: 38

Users who are viewing this thread

Top Bottom