?
Solved

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

Posted on 2005-05-13
12
Medium Priority
?
226 Views
Last Modified: 2008-03-17
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.

Please help - thank you in anticipation.





0
Comment
Question by:manwestermo
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13994692
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13994701
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 Comment

by:manwestermo
ID: 13994725
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

    CheckDate = SLADate

    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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:manwestermo
ID: 13994735
the weekday example above excludes 2004 UK public holidays
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13995809
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
    ' Add time til Midnight.
    lngMinutes = lngMinutes + DateDiff("n", datCheckIn, 1)
  End If
 
  Set rst = Nothing
  Set dbs = Nothing
 
  GetWorkTime = lngMinutes
 
End Function

/gustav
0
 

Author Comment

by:manwestermo
ID: 13996015
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 Comment

by:manwestermo
ID: 13996145
Doh!  I was so excited I didnt realise URL is only for Crystal Reports...
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13996598
To me it looks like that URL gives exactly what you need.

/gustav
0
 

Author Comment

by:manwestermo
ID: 14025397
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 14031104
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14508883
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question