Working Hours problem

T

Thepieman

Registered User.
Local time
Today, 04:25
Joined
Dec 16, 2004
Messages
11
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).

Can anyone help ?
 
**Edited out; see later posts for all code and comments**
 
Last edited:
mresann

Do you have an example of how this code in implemented in a form. I am not sure about how to use the code.

Thanks

misslee1888
 
Also does this work using the UK date format and can mins diff under 60 be includes in the results.
 
Thepieman said:
A couple of weeks ago I posted the attached thread

http://www.access-programmers.co.uk/forums/showthread.php?t=79282

which you were kind enough to reply to. I apologise but can't get the code to work in a query. I am quite new to access -can you offer any more help?

Thanks in advance for any help you can offer
Click to expand...

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! :eek:

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).
 
Last edited:
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.

With thanks

David :confused:
 
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!
 
Function needs repair...

it does not handle well night shifts. If a day starts at 2PM and ends at 2AM the next day the function returns negative values
 
Modified to allow calculation of night shifts

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.

Tiran Dagan
http://www.tirandagan.com

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 : 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
 
eccluding holydays

Can anybody help me with the code belove?

I need the function to exclude also holyday the i want to store in table called table holiday.

best regards,
Nicholas

mresann said:
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! :eek:

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).
Click to expand...
 
You must log in or register to reply here.

Similar threads

Technics
Add clock values to report query
2 3 4
Replies
61
Views
2,355
Gasman
Gasman
X
When was my Worker's Last Day Off?
Replies
3
Views
664
MarkK
MarkK
Uncle Gizmo
A Paradigm Shift in Efficiency - Claude Sonnet 4 + Gemini CLI
Replies
15
Views
1,156
Pat Hartman
P
S
Hours
Replies
5
Views
654
June7
June7
E
Help query Count
Replies
6
Views
615
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom