Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009

    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.


  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    hope this help

    See clear as mud

    StePhan McKillen
    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

  3. #3
    Join Date
    Aug 2009
    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
    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

    '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