Unanswered: Calculate working minutes between two date/time fields
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??)
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
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
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