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

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

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

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

the weekday example above excludes 2004 UK public holidays

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

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

Doh!  I was so excited I didnt realise URL is only for Crystal Reports...

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

/gustav

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

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