Results 1 to 6 of 6

Calculate Business Hours over X amount of business days.

  1. #1
    gutarkomp is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    4

    Calculate Business Hours over X amount of business days.

    I have been searching for the correct wording here for this question but, haven't quite got it right. I am ok with Access but not very good with VB but, here goes. I am trying to create a status screen for a shipping dock which will show the amount of time lapsed from when a trailer started the unload process till the final pallet is removed. I wanted to be able to do this and have the calculation go across multiple days... problem is DateDiff counts ALL the hours. I only want to calculate 7:30 AM till 3:30 PM and nothing else. I would like to not count Saturdays, Sundays & Holidays but, I think the bulk of the solution I am looking for some insight in is the hours counting by business day only. We are trying to figure out how many hours it takes for us to get to get our dock to stock once a trailer has been at a loading dock so we can track efforts to get faster at this process. Solutions I have seen have been intense VBA modules and I am not the best with these... We want to "Start" the clock (I will probably use a Date(), Time(), & Now() to append to a table which will show up on a form that auto refreshes.) Once they finish the trailer another time will be appended and I can build a query that auto sends the results of that trailer to the appropriate person.




    Example:
    Start Time = 1/1/2017 10:30 AM
    End Time = 1/2/2017 9:30 AM
    Total Time = 23 Hours
    Business Hours = 7 Hours <-- A way to auto calculate this?
    If business hours are 7:30 AM 3:30 PM

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,550
    You need to look for a VBA function that is already set up for you without you having to create it from scratch.

    Such as this - format is the same as Now():
    Code:
    Public Function CalcHours(Started, Ended) As Double    
    
    Dim StartTime As Date, EndTime As Date, wrkDate As Date, wrkHours As Double
        
        If IsDate(Started) And IsDate(Ended) And Ended > Started Then
        Else
            Exit Function
        End If
        
        If CDate(Format(Started, "mm/dd/yyyy")) = CDate(Format(Ended, "mm/dd/yyyy")) Then
            CalcHours = DateDiff("h", Started, Ended)
        Else
            StartTime = CDate(Format(Started, "mm/dd/yyyy") & " 7:30 am")
            EndTime = CDate(Format(Ended, "mm/dd/yyyy") & " 3:30 pm")
            wrkDate = CDate(Format(Started, "mm/dd/yyyy")) + 1
            wrkHours = DateDiff("n", Started, CDate(Format(Started, "mm/dd/yyyy") & " " & Format(EndTime, "hh:mm ampm")))
        
            Do Until wrkDate >= CDate(Format(Ended, "mm/dd/yyyy"))
                If DatePart("w", wrkDate) = 1 Or DatePart("w", wrkDate) = 7 Then
                Else
                    wrkHours = wrkHours + 480
                End If
                wrkDate = wrkDate + 1
            Loop
        
            wrkHours = wrkHours + DateDiff("n", CDate(Format(Ended, "mm/dd/yyyy") & " " & Format(StartTime, "hh:mm ampm")), Ended)
            wrkHours = wrkHours / 60
            CalcHours = wrkHours
        End If
        
    End Function
    To utilize this function in a query, add a field such as this:
    NbrHrs: CalcHours([starttime],[endtime])

    To utilize it in VBA or on a form:
    NumHrs = CalcHours(Started, Ended)

    If you want to add holidays to the mix, there will have to be a table which contains all the holiday dates for your company.
    Last edited by aytee111; 05-16-2017 at 01:38 PM. Reason: fixed spacing

  3. #3
    gutarkomp is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    4
    When I use this module I get an error pop up that says...

    There was an error compiling this function.
    The Visual Basic module contains a syntax error.
    Check the code, and then recompile it.

    I get this error whenever I use date(), now() & Time() too in this particular DB. When I look at the VB code the very top line is Red.

  4. #4
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,369
    The first two lines should look like this:
    Code:
    Public Function CalcHours(Started, Ended) As Double
    Dim StartTime As Date, EndTime As Date, wrkDate As Date, wrkHours As Double
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    gutarkomp is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    4
    Thank you BOTH! worked great and the issue was with those forst two lines. There was 4 spaces between them which I deleted and the module worked. Love this FORUM!!

  6. #6
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,369
    Excellent! Thanks for posting back with your success and solution.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add or Subtract Business Days from a Given Date
    By orange in forum Code Repository
    Replies: 2
    Last Post: 05-15-2017, 11:45 AM
  2. Number of Calls during business hours
    By desireemm1 in forum Queries
    Replies: 20
    Last Post: 10-29-2014, 05:24 PM
  3. Replies: 3
    Last Post: 09-05-2014, 02:44 PM
  4. Replies: 3
    Last Post: 04-03-2013, 04:53 PM
  5. CRITERIA only looks at the LAST 20 BUSINESS DAYS
    By taimysho0 in forum Queries
    Replies: 3
    Last Post: 12-06-2011, 05:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums