# Query Help: Calculate minutes between two times EXCLUDING weekends and ONLY inlcuding Core Business Hours

Hi Experts

I can use DateDiff to calculate the number of minutes between two times [Date_Time_Customer_Call_Logged]  and  [Time_Created]

both of these fields include date AND time (sorry the names are not very clear)

my query so far is this:

----------------------------
DateDiff("n",[Date_Time_Customer_Call_Logged],[Time_Created])
----------------------------

but how do I change it so to ensure that it ONLY includes weekdays BUT it excludes times outside of 09:00hrs - 17:00hrs????

e.g. at present my query will count all minutes between 01-01-2000 16:55hrs and 02-01-2000 09:05hrs.  I need it to only count the 10 minutes.

###### Who is Participating?

Commented:
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Commented:
you will need to create a specialized function of your own to do that calcualtion, as DateDiff will ALWAYS compte the totla difference between the two supplkied dates.

AW
0

Commented:
Your function would accept the two dataes, and the interval type, as DATEDIFF does, but would then determine if there were weekends between the two dates, and also would exclude the time between 0900 and 1700 for each day that was to be counted in the time period.

AW
0

Author Commented:
Ok so assuming I already have the function to calculate weekdays (see below) how would it be modified to ONLY include 09:00hrs to 17:00hrs.

That is exactly where I need the help.....

-----------------------------
Public Function CountWeekDays(SLADate As Date, DevDate As Date) As Integer

' count the number of week days between two dates

Dim CheckDate As Date

CountWeekDays = 0

Do Until CheckDate > DevDate

If Weekday(CheckDate) <> 1 And Weekday(CheckDate) <> 7 Then

CountWeekDays = CountWeekDays + 1

End If

If CheckDate = DateSerial(2004, 1, 1) Or CheckDate = DateSerial(2004, 1, 2) Or CheckDate = DateSerial(2004, 4, 9) Or CheckDate = DateSerial(2004, 4, 12) Or CheckDate = DateSerial(2004, 5, 3) Or CheckDate = DateSerial(2004, 5, 31) Or CheckDate = DateSerial(2004, 8, 30) Or CheckDate = DateSerial(2004, 12, 27) Or CheckDate = DateSerial(2004, 12, 28) Then

CountWeekDays = CountWeekDays - 1

End If

CheckDate = CheckDate + 1

Loop

End Function
-----------------------------
0

Author Commented:
the weekday example above excludes 2004 UK public holidays
0

CIOCommented:
Here is a function covering a similar application.
You should be able to modify it to suit your need:

Public Function GetWorkTime( _
ByVal PersonID As Long, _
ByVal CheckDate As Date) _
As Long

Dim dbs           As DAO.Database
Dim rst           As DAO.Recordset

Dim strSQL        As String
Dim lngMinutes    As Long
Dim datCheckIn    As Date
Dim datCheckOut   As Date
Dim booCheckedIn  As Boolean
Dim booCheckedOut As Boolean

strSQL = "Select * From tblWorkTime " & _
" Where PersonID = " & PersonID & _
" And Int(CheckTime) = " & Format(CheckDate, "0") & _
" Order By CheckTime;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

datCheckIn = #12:00:00 AM# 'Int(CheckDate)
With rst
While Not .EOF
If !CheckOut.value = False Then
If booCheckedIn = False Then
datCheckIn = !CheckTime.value
booCheckedIn = True
booCheckedOut = False
End If
Else
If booCheckedOut = False Then
' Count minutes.
datCheckOut = !CheckTime.value
lngMinutes = lngMinutes + DateDiff("n", datCheckIn, datCheckOut)
booCheckedOut = True
booCheckedIn = False
End If
End If
.MoveNext
Wend
.Close
End With
If booCheckedIn = True And booCheckedOut = False Then
lngMinutes = lngMinutes + DateDiff("n", datCheckIn, 1)
End If

Set rst = Nothing
Set dbs = Nothing

GetWorkTime = lngMinutes

End Function

/gustav
0

Author Commented:
thanx cactus but I dont fully understand the example you have provided.  I think the following URL has given me both the weekday and working hours caclulations that I need.   i just need to implement and test it.

http://www.tek-tips.com/faqs.cfm?fid=5430
0

Author Commented:
Doh!  I was so excited I didnt realise URL is only for Crystal Reports...
0

CIOCommented:
To me it looks like that URL gives exactly what you need.

/gustav
0

Author Commented:
OK I have resolved this with the following function.  I will modify it later to take holidays into account.

--------------------------
Public Function CountWorkMinutes(Starttime As Date, endtime As Date) As Integer
' count the number of working minutes between two dates/times

Dim checktime As Date

CountWorkMinutes = 0

checktime = Starttime

Do Until checktime > endtime

If Weekday(checktime) <> 1 And Weekday(checktime) <> 7 Then

If Hour(checktime) > 8 And Hour(checktime) < 17 Then
CountWorkMinutes = CountWorkMinutes + 1
End If

End If

checktime = checktime + (1 / 1440)

Loop

End Function
--------------------------

Thanks to all that helped anyway.  PLEASE REFUND MY POINTS
0

Commented:
manwestermo>>post a 0 point request to Community Support asking that they PAQ this question and refund your points, as you resolved the issue on your own, with assistance from any of the Experts.

AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.