# Thread: Calculate working minutes between two date/time fields

1. Registered User
Join Date
Aug 2009
Posts
61

## Unanswered: Calculate working minutes between two date/time fields

Hi There,

I have a production database (access 2007) where the operators double clicks a start field when starting a work order and a finish field when there are finished. The fields are called [Start] and [Finish]. This information is stored in a table called "Data" along with the [operator] name field and other information.

I would like to query this table and figure out how long each work order is taking. The biggest problem I am having is discounting all of the nonwork hours, such as the evenings, the weekends, and the public holidays. Unfortunately the timer is often kept running during these times. The list of times that I need excluded are below.

Shift - 07:30 to 16:00 (any time outside of this should be excluded)
Weekends - Saturday and Sunday
Public Holidays - There are 11 set in Canada (I think I may need to create a table with these dates in??)

Any help on this would be really appreciated.

Thanks

Join Date
Feb 2004
Location
New Zealand
Posts
1,476

3. Registered User
Join Date
Aug 2009
Posts
61
Originally Posted by myle
Hi Myle,

I am just getting back to revisit this problem. I tried to follow the instructions on the thread, but I cannot get it to work for me. When I try and call the functuion it tells me "Undefined function 'CalcWorkDays' in expression." What have I done wrong? The module code is below:

Function CalcWorkDays(VacRequestSrtDate, VacRequestFinDate) As Integer

Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer

On Error GoTo Err_Execute

CalcWorkDays = 0

If IsDate(VacRequestSrtDate) And IsDate(VacRequestFinDate) Then
If VacRequestFinDate <= VacRequestSrtDate Then
CalcWorkDays = 0
Else
LTotalDays = DateDiff("d", VacRequestSrtDate - 1, VacRequestFinDate)
LSaturdays = DateDiff("ww", VacRequestSrtDate - 1, VacRequestFinDate, 7)
LSundays = DateDiff("ww", VacRequestSrtDate - 1, VacRequestFinDate, 1)

'Workdays is the elapsed days excluding Saturdays and Sundays
CalcWorkDays = LTotalDays - LSaturdays - LSundays

End If
End If

Exit Function

Err_Execute:
'If error occurs, return 0
CalcWorkDays = 0

End Function

Also, how would I now incorporate the holiday dates as well? I assume that i would need to create a holidays table and then use the dates from there.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•