I have a database which records the time when a call came in and when someone closed (or responded) to that call and I need to to work out the time that has elapsed between the two times in hours. My problem is that I need to take business hours into consideration (i.e. stop the clock between 6.30pm and 7.30am Monday to Friday and exclude weekends).
First, I assumed you knew how to create a VBA module and pasted the code I gave you earlier. If you did, skip down to the next step where it says "Open a query in design view...". If you didn't (or weren't sure), follow these steps:
Open the DBA window for the database. Click "Module", then "New". A new instance of a VBA module will open up.
Erase everything on the module page and paste the following code:
Code:
'*********** Code Start **************
Option Compare Database
Option Explicit
'constants that one can use in time calculations
Public Const c_dblHour As Double = 4.16666666666667E-02 ' = (1/24)
Public Const c_dblMinute As Double = 6.94444444444444E-04 ' = ((1/24/60)
Public Const c_dblSecond As Double = 1.15740740740741E-05 ' = (((1/24)/60)/60)
'-----------------------------------------------------------------------------------------
' Procedure : fncReturnHours
' Created : Jan 11 2005 07:43
' Reference : fncReturnHours*
' Author : Michael Reese
' Input(s) : request date and time, response date and time, business open, business close
' Output(s) : hours between request and response, business hours only
' Purpose : figure out business hours between request and response time
' Qualifier : 1. The request and response dates are both workdays
' Qualifier : 2. There are no holidays to factor in
' Qualifier : 3. Request and response date/times occur during business hours
'-----------------------------------------------------------------------------------------
Public Function fncReturnHours(ByVal datRequest As Date, _
ByVal datResponse As Date, _
ByVal datDayStart As Date, _
ByVal datDayEnd As Date) _
As Double
'|<------ 90-character width -------------------------------- 90-character width ------->|
PROC_DECLARATIONS:
Dim dblBusinessHours As Double 'Fraction of day for business hours
Dim dblStartHours As Double 'Time difference between request time and end of business day
Dim dblStopHours As Double 'Time difference between response time and start of business day
Dim dblReturnHours As Double 'Total time elapsed; assing to function output
Dim lngDayCounter As Long
PROC_START:
On Error GoTo PROC_ERROR
dblReturnHours = 0
PROC_MAIN:
'determine amount of time for business hours
dblBusinessHours = datDayEnd - datDayStart
'calculate amount of time from request time to end of first day
dblStartHours = (Int(datResponse) + datDayEnd) - datResponse
'calculate amount of time from start of last day to response time
dblStopHours = datRequest - (Int(datRequest)) - datDayStart
'determine if request and response occured on same day
If Int(datRequest) = Int(datResponse) Then
'same day, calculate time period only
dblReturnHours = datResponse - datRequest
Else
'use loop; int function creates integer values out of dates
For lngDayCounter = CLng(Int(datRequest)) To CLng(Int(datResponse))
If lngDayCounter = Int(datRequest) Then
dblReturnHours = dblReturnHours + dblStartHours
ElseIf lngDayCounter = Int(datResponse) Then
dblReturnHours = dblReturnHours + dblStopHours
Else
If Weekday(lngDayCounter, vbSaturday) > 2 Then
dblReturnHours = dblReturnHours + dblBusinessHours
End If
End If
Next
End If
'convert days to hours
dblReturnHours = (dblReturnHours * 24)
PROC_EXIT:
fncReturnHours = dblReturnHours
Exit Function
PROC_ERROR:
MsgBox "Error " & Err.Number & " (" & _
Err.Description & ")" & vbCrLf & vbCrLf & _
"Procedure: fncReturnHours" & vbCrLf & _
"Module: Module1"
GoTo PROC_EXIT
End Function
'*********** Code End **************
Click the Save button, you can use the default Module1.bas if you want. Close the VBA module.
Open up a query in design view. When it asks for a table, just click Close without adding any. Open the query in SQL view. Paste the following SQL statement into the text area:
Code:
SELECT tblTimes.RequestTime, tblTimes.ResponseTime, fncReturnHours([tblTimes.RequestTime],[tblTimes.ResponseTime],#7:30#,#18:30#) AS TotalHours
FROM tblTimes;
NOW THIS IS IMPORTANT. Look for any instance of "tblTimes" and replace it with the EXACT name of the table that contains the fields RequestTime and ResponseTime. (I assume you use the field names RequestTime and ResponseTime as well, they need to match exactly the name you have for them in your table). If you DON'T use the EXACT names, the query will not work and you will get an error.
The query output field TotalHours will give you the total hours calculated as described in the function you created in a module to two decimal places. For instance, a difference of 4 hours and 15 minutes will give you 4.25 total hours.
I realize you would have liked a form as an example, but time constraints prevent me from creating one.
One more thing: To get the Minutes, multiply the TotalHours by 60. You can use an Iif function (check Access help) to determine if the TotalHours returned iis under an hour, if so, multiply the result, probably using the CLng function to cut off the unneeded decimal amount (again, check Access Help...ain't learning about Access coding fun!
Incidentally, all date fields will work accordingly to your regional settings. No queries nor functions have to be changed. As long as the time elements in your database refer to a Date/Time field, you will have no problems no matter the format, be it US, UK, or other custom format.
Hope this helps. By the way, I can't give any more help on this project (other than ironing out the glitches to get this particular function working).
This module is really good as it fits with my need to work out the number of days a request takes to complete. In the query, I edited the times to fit with our business hours. Additionally I remmed out the calculation to convert to hours so I kept the days. This has solved my initial problem but the next is a bit harder. (For those in the UK working in the Public Sector this relates to the FOI Act)
The requests have a finite timescale of 20 working days to respond but with our requests, it is possible that it would be suspended pending further information from the client. It would only restart when the client responded. The revised timescale would be 20 days less the number of days that had elapsed before writing to the cleint.
I.e. a client writes to us on 1/2/05. The initial target completion date would be 1/3/05. However we write to the client on 4/2/05 and the request is halted. The client responds on 10/2/05 and therefore the new completion date is 7/3/05 (3 days btw 1/2/05 & 4/2/05 then 17 days from 10/2/05).
How can I write a query or module that can account for these options? As we are a public authority, we have to able to report on our performance and so we must be able to quote both request time and also overall time.
I'm new to this forum and not much of a Access programmer. I can do this in Excel (after a fashion) but I need to know how to do this in Access.
I know this post is really old now but for anybody else stumbling accross this trying to find the same solution for working out working hours between request and response times this is a great bit of code, kudos to mresann for coming up with it. However he did make a small error in the code which makes the calcualtions incorrect.
The section that reads
'calculate amount of time from request time to end of first day
dblStartHours = (Int(datResponse) + datDayEnd) - datResponse
'calculate amount of time from start of last day to response time
dblStopHours = datRequest - (Int(datRequest)) - datDayStart
SHOULD read:
'calculate amount of time from request time to end of first day
dblStartHours = (Int(datRequest) + datDayEnd) - datRequest
'calculate amount of time from start of last day to response time
dblStopHours = datResponse - (Int(datResponse)) - datDayStart
took me a little de-bugging to spot what was hapenning but got it in the end!
The following code incorporates prior comments and can handle night shifts which span over a single day (for instance: someone works from 2pm to 2am the next morning).
Another modification is that this function will return the result in minutes, not hours.
'*********** Code Start **************
Option Compare Database
Option Explicit
'constants that one can use in time calculations
Public Const c_dblHour As Double = 4.16666666666667E-02 ' = (1/24)
Public Const c_dblMinute As Double = 6.94444444444444E-04 ' = ((1/24/60)
Public Const c_dblSecond As Double = 1.15740740740741E-05 ' = (((1/24)/60)/60)
'-----------------------------------------------------------------------------------------
' Procedure : fncReturnMinutes
' Created : Jan 11 2005 07:43
' Reference : fncReturnHours*
' Author : Michael Reese
' Input(s) : request date and time, response date and time, business open, business close
' Output(s) : hours between request and response, business hours only
' Purpose : figure out business hours between request and response time
' Qualifier : 1. The request and response dates are both workdays
' Qualifier : 2. There are no holidays to factor in
' Qualifier : 3. Request and response date/times occur during business hours
'-----------------------------------------------------------------------------------------
Public Function fncReturnMinutes(ByVal datRequest As Date, _
ByVal datResponse As Date, _
ByVal datDayStart As Date, _
ByVal datDayEnd As Date) _
As Double
'|<------ 90-character width -------------------------------- 90-character width ------->|
PROC_DECLARATIONS:
Dim dblBusinessHours As Double 'Fraction of day for business hours
Dim dblStartHours As Double 'Time difference between request time and end of business day
Dim dblStopHours As Double 'Time difference between response time and start of business day
Dim dblReturnHours As Double 'Total time elapsed; assing to function output
Dim lngDayCounter As Long
PROC_START:
On Error GoTo PROC_ERROR
dblReturnHours = 0
PROC_MAIN:
'determine amount of time for business hours
dblBusinessHours = Abs(datDayEnd - datDayStart)
'calculate amount of time from request time to end of first day
dblStartHours = (Int(datRequest) + datDayEnd) - datRequest
If dblStartHours < 0 Then dblStartHours = (DateAdd("d", 1, Int(datRequest)) + datDayEnd) - datRequest
'calculate amount of time from start of last day to response time
dblStopHours = datResponse - (Int(datResponse)) - datDayStart
If dblStopHours < 0 Then dblStopHours = datResponse - DateAdd("d", -1, (Int(datResponse))) - datDayStart
'determine if request and response occured on same day
If Int(datRequest) = Int(datResponse) Then
'same day, calculate time period only
dblReturnHours = datResponse - datRequest
Else
'use loop; int function creates integer values out of dates
For lngDayCounter = CLng(Int(datRequest)) To CLng(Int(datResponse))
If lngDayCounter = Int(datRequest) Then
dblReturnHours = dblReturnHours + dblStartHours
ElseIf lngDayCounter = Int(datResponse) Then
dblReturnHours = dblReturnHours + dblStopHours
Else
If Weekday(lngDayCounter, vbSaturday) > 2 Then
dblReturnHours = dblReturnHours + dblBusinessHours
End If
End If
Next
End If
'convert days to minutes
If datDayEnd < datDayStart Then
dblReturnHours = ((dblReturnHours * 24 - 12) * 60)
Else
dblReturnHours = (dblReturnHours * 24 * 60)
End If
PROC_EXIT:
fncReturnMinutes = Round(dblReturnHours, 1)
Exit Function
PROC_ERROR:
MsgBox "Error " & Err.Number & " (" & _
Err.Description & ")" & vbCrLf & vbCrLf & _
"Procedure: fncReturnHours" & vbCrLf & _
"Module: Module1"
GoTo PROC_EXIT
End Function
First, I assumed you knew how to create a VBA module and pasted the code I gave you earlier. If you did, skip down to the next step where it says "Open a query in design view...". If you didn't (or weren't sure), follow these steps:
Open the DBA window for the database. Click "Module", then "New". A new instance of a VBA module will open up.
Erase everything on the module page and paste the following code:
Code:
'*********** Code Start **************
Option Compare Database
Option Explicit
'constants that one can use in time calculations
Public Const c_dblHour As Double = 4.16666666666667E-02 ' = (1/24)
Public Const c_dblMinute As Double = 6.94444444444444E-04 ' = ((1/24/60)
Public Const c_dblSecond As Double = 1.15740740740741E-05 ' = (((1/24)/60)/60)
'-----------------------------------------------------------------------------------------
' Procedure : fncReturnHours
' Created : Jan 11 2005 07:43
' Reference : fncReturnHours*
' Author : Michael Reese
' Input(s) : request date and time, response date and time, business open, business close
' Output(s) : hours between request and response, business hours only
' Purpose : figure out business hours between request and response time
' Qualifier : 1. The request and response dates are both workdays
' Qualifier : 2. There are no holidays to factor in
' Qualifier : 3. Request and response date/times occur during business hours
'-----------------------------------------------------------------------------------------
Public Function fncReturnHours(ByVal datRequest As Date, _
ByVal datResponse As Date, _
ByVal datDayStart As Date, _
ByVal datDayEnd As Date) _
As Double
'|<------ 90-character width -------------------------------- 90-character width ------->|
PROC_DECLARATIONS:
Dim dblBusinessHours As Double 'Fraction of day for business hours
Dim dblStartHours As Double 'Time difference between request time and end of business day
Dim dblStopHours As Double 'Time difference between response time and start of business day
Dim dblReturnHours As Double 'Total time elapsed; assing to function output
Dim lngDayCounter As Long
PROC_START:
On Error GoTo PROC_ERROR
dblReturnHours = 0
PROC_MAIN:
'determine amount of time for business hours
dblBusinessHours = datDayEnd - datDayStart
'calculate amount of time from request time to end of first day
dblStartHours = (Int(datResponse) + datDayEnd) - datResponse
'calculate amount of time from start of last day to response time
dblStopHours = datRequest - (Int(datRequest)) - datDayStart
'determine if request and response occured on same day
If Int(datRequest) = Int(datResponse) Then
'same day, calculate time period only
dblReturnHours = datResponse - datRequest
Else
'use loop; int function creates integer values out of dates
For lngDayCounter = CLng(Int(datRequest)) To CLng(Int(datResponse))
If lngDayCounter = Int(datRequest) Then
dblReturnHours = dblReturnHours + dblStartHours
ElseIf lngDayCounter = Int(datResponse) Then
dblReturnHours = dblReturnHours + dblStopHours
Else
If Weekday(lngDayCounter, vbSaturday) > 2 Then
dblReturnHours = dblReturnHours + dblBusinessHours
End If
End If
Next
End If
'convert days to hours
dblReturnHours = (dblReturnHours * 24)
PROC_EXIT:
fncReturnHours = dblReturnHours
Exit Function
PROC_ERROR:
MsgBox "Error " & Err.Number & " (" & _
Err.Description & ")" & vbCrLf & vbCrLf & _
"Procedure: fncReturnHours" & vbCrLf & _
"Module: Module1"
GoTo PROC_EXIT
End Function
'*********** Code End **************
Click the Save button, you can use the default Module1.bas if you want. Close the VBA module.
Open up a query in design view. When it asks for a table, just click Close without adding any. Open the query in SQL view. Paste the following SQL statement into the text area:
Code:
SELECT tblTimes.RequestTime, tblTimes.ResponseTime, fncReturnHours([tblTimes.RequestTime],[tblTimes.ResponseTime],#7:30#,#18:30#) AS TotalHours
FROM tblTimes;
NOW THIS IS IMPORTANT. Look for any instance of "tblTimes" and replace it with the EXACT name of the table that contains the fields RequestTime and ResponseTime. (I assume you use the field names RequestTime and ResponseTime as well, they need to match exactly the name you have for them in your table). If you DON'T use the EXACT names, the query will not work and you will get an error.
The query output field TotalHours will give you the total hours calculated as described in the function you created in a module to two decimal places. For instance, a difference of 4 hours and 15 minutes will give you 4.25 total hours.
I realize you would have liked a form as an example, but time constraints prevent me from creating one.
One more thing: To get the Minutes, multiply the TotalHours by 60. You can use an Iif function (check Access help) to determine if the TotalHours returned iis under an hour, if so, multiply the result, probably using the CLng function to cut off the unneeded decimal amount (again, check Access Help...ain't learning about Access coding fun!
Incidentally, all date fields will work accordingly to your regional settings. No queries nor functions have to be changed. As long as the time elements in your database refer to a Date/Time field, you will have no problems no matter the format, be it US, UK, or other custom format.
Hope this helps. By the way, I can't give any more help on this project (other than ironing out the glitches to get this particular function working).