Jonathan:
While James has got his thinking cap on let me have a stab at this:
First create a calendar table of all working days (Mon to Fri) by using the
following function:
Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmdate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set dbs = CurrentDb
' does table exist? If so get user confirmation to delete it
For Each tdf In dbs.TableDefs
If tdf.Name = strtable Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Exit For
Else
Exit Function
End If
End If
Next tdf
' create new table
strSQL = "CREATE TABLE " & strtable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL
' refresh database window
Application.RefreshDatabaseWindow
If varDays(0) = 0 Then
' fill table with all dates
For dtmdate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
"VALUES(#" & Format(dtmdate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
Next dtmdate
Else
' fill table with dates of selected days of week only
For dtmdate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmdate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
"VALUES(#" & Format(dtmdate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmdate
End If
End Function
Call it like so to make a calendar table called WorkDaysCalendar for the next
10 years:
MakeCalendar_DAO "WorkDaysCalendar", #2009-01-01#,#2019-12-31#,2,3,4,5,6
As and when you know them delete any rows from the table for public or
concessionary holidays so these aren't counted as work days.
You can now create a function to return the response time in minutes:
Public Function ResponseTime(dtmCallReceived As Date, dtmResponded As Date)
As Long
Const IN_TIME = 600
Dim lngMinutes As Long
Dim dtmDateReceived As Date
Dim dtmDateResponded As Date
dtmDateReceived = DateValue(dtmCallReceived)
dtmDateResponded = DateValue(dtmResponded)
If dtmDateResponded = dtmDateReceived Then
' response on same day so simple time difference
lngMinutes = DateDiff("n", dtmCallReceived, dtmResponded)
Else
' resonse on later day so get minutes until 18:00 on day 1
lngMinutes = _
DateDiff("n", dtmCallReceived, DateAdd("h", 18, dtmDateReceived))
' add number of minutes in whole working days before response
lngMinutes = lngMinutes + _
DCount("*", "WorkDaysCalendar", "calDate > #" & _
Format(dtmDateReceived, "yyyy-mm-dd") & "# And " & _
"calDate < #" & Format(dtmDateResponded, "yyyy-mm-dd") & "#") *
IN_TIME
' add minutes after 8:00 on date of response
lngMinutes = lngMinutes + _
DateDiff("n", DateAdd("h", 8, dtmDateResponded), dtmResponded)
End If
ResponseTime = lngMinutes
End Function
The function can then be called like so:
ResponseTime(#2009-10-16 14:30#,#2009-10-19 17:40#)
which returns 790, the response time in minutes.
If you want to convert this to hours and minutes you can do so like this:
Dim lngResponseMinutes As Long
Dim strResponse As String
lngResponseMinutes = _
ResponseTime(#2009-10-16 14:30#,#2009-10-19 17:40#)
strResponse = lngResponseMinutes \ 60 & ":" & _
Format(lngResponseMinutes Mod 60, "00")
This will assign a value 13:10 to the strResponse variable. I've
deliberately used an unrealistically long response time here to illustrate it.
In fact the function should be able to cope with a response from virtually
instant to many days. You'd pass the call time and contact time into it
rather than literal date times of course. I'm assuming that these are
entered as full date/time values, not just as times.
I've tested the above with a few dates and times, and it seems to give the
right results, but you'll appreciate that it has been dashed off quickly so
should be thoroughly tested before being used.
The function could be made more generic by allowing the start and end times
for the working day to be passed into it as arguments as well as the call
time and contact time, in which case the IN_TIME could then be computed from
these times as a variable rather than a constant, and similarly variables
would be used in pace of the constants 8 and 18 when calling the DateAdd
function.
Ken Sheridan
Stafford, England
I am trying to do a time calculation. Here is the scenario. A call comes in
at 4pm and there is a 4 hr turnaround time to respond, business hours. The
Work hrs are from 8am-6pm. I need to show the 2 hrs from day one and the
number of hours on day two. I have a [calltime] field and a [contacttime]
field. Also, we have to take into account if the call came in on Friday so
the remaining hrs should be reflected for Monday. Can anyone help me with
this?
--
Message posted via AccessMonster.com
.
