Calculating time difference (excluding weekends and holidays) (1 Viewer)

GLese · Feb 20, 2018

Okay, so I've dug around here and found several ways to do this, but none that meet my specific need, or the solution isn't clear enough for me as a new Access/VB user to understand where to implement it.

My dbase has a table to track samples that come into our chemical lab for QC approval. The main function of the dbase is to allow us to track KPIs, with one being Turn Around Time. We consider TAT (in hours (1.00))=Time Logged Out - Time Logged In. Simple enough.

The tables containing this data have a column DateTimeIn and DateTimeOut, each of these are filled in based on button clicks on a form. They are formatted as General Date, and populated with =Now().

I know I could use DateDiff with a result in minutes, then math it out to a result in hours. However, we do not include non-business hours (5:00pm-8:00am on weekdays, all hours on weekends, and company holidays) in our current calculation in the excel spreadsheet we currently use to log samples in.

Am I better off creating a VB script that then gets plugged into a query to calculate TAT?

Should I use a complex expression in a query field?

TL;DR: What's going to be the best way to get to a query result which gives me a calculated TAT based on our parameters for each sample entry?

(As a note, I'm not necessarily looking for a written out code unless someone has it, but if y'all can point me in the right direction, I am thoroughly enjoying the learning process I'm going through with Access/VB)

Thanks in advance y'all!

CJ_London · Feb 20, 2018

this question has been asked many times. Look to the bottom of this thread for the similar thread section - there are at least 4 that would appear to match your requirement. You will need a vba function to achieve what you want.

but none that meet my specific need

everybody says that but with your brief description it doesn't seem any different from the examples on this and other forums

plog · Feb 20, 2018

Yes, you should build a custom function--1 the expression would be too complex, 2--a function is easily reusable elsewhere (form, report, another query).

Search this forum and you will find code for this--I know I've seen many threads that are similar to this, if not identical. Or just wait 12 hours (at most) and a person (at least) will create the code for you in this thread.

GLese · Feb 21, 2018

CJ_London said:
this question has been asked many times. Look to the bottom of this thread for the similar thread section - there are at least 4 that would appear to match your requirement. You will need a vba function to achieve what you want.

everybody says that but with your brief description it doesn't seem any different from the examples on this and other forums

Thanks! I'll start digging through those

GLese · Feb 21, 2018

plog said:
Yes, you should build a custom function--1 the expression would be too complex, 2--a function is easily reusable elsewhere (form, report, another query).

Search this forum and you will find code for this--I know I've seen many threads that are similar to this, if not identical. Or just wait 12 hours (at most) and a person (at least) will create the code for you in this thread.

Thank you for the simple explanation on why code will be better. I'll start digging around and see what I can find

arnelgp · Feb 21, 2018

how many days or hours, is the maximum that you encounter?

arnelgp · Feb 21, 2018

Code:

Option Compare Database
Option Explicit

Private mdlTotalHrs As Double

Public Function fncCalcTAT(dtIn As Date, dtOut As Date, _
                                                    Optional HolidayTable As String,  _
                                                    Optional HolidayDateFieldName As String)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' fncCalcTAT
'
' purpose calculate TAT hours
'
' parameters:
'
' dtIn              = start date (date)
' dtOut             = end date (date)
' HolidayTable      = name of holiday table (string, optional)
' HolidayDateField  = date field name on Holiday table (string, optional)
'                     if HolidayTable is specified and this parameter is not,
'                     then it will use [Date] as field name (so you must
'                     explicitly declare the name to avoid error.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim dtLoop As Date
    mdlTotalHrs = 0
    For dtLoop = dtIn To dtOut Step #1:00:00 AM#
        If Weekday(dtLoop, vbSunday) = 1 Or Weekday(dtLoop, vbSunday) = 7 Then
            ' do nothing, weekends not included
        Else
            If HolidayTable <> "" Then
                If HolidayDateFieldName = "" Then HolidayDateFieldName = "[Date]"
                If DCount("*", HolidayTable, HolidayDateFieldName & "=" & Format(dtLoop, "mm/dd/yyyy")) <> 0 Then
                    ' do nothing date is in holiday table
                Else
                    Call calcHours(dtLoop)
                End If
            Else
                Call calcHours(dtLoop)
            End If
        End If
    Next
    
    fncCalcTAT = mdlTotalHrs
    
End Function


Private Function calcHours(dt As Date)
    If (TimeValue(dt & "") > #4:59:59 PM#) Or _
        (TimeValue(dt & "") < #9:00:00 AM#) Then
        ' still do nothing 5am to 8:59:59am not included
    Else
        mdlTotalHrs = mdlTotalHrs + 1
    End If

End Function

GLese · Feb 21, 2018

arnelgp said:
how many days or hours, is the maximum that you encounter?

We can get values that range anywhere from 0.00 (less than one minute by our current system) to something that may span 3 days. We are always looking for something in hours down to 0.01

reggiejdean · Aug 2, 2019

Hi arnelgp,

This function looks terrific. How can I modify it to calculate each and every minute, based on the code that you have here? (I still want to exclude weekends, holidays and the start/end times) Thank you, Reggie

Calculating time difference (excluding weekends and holidays) (1 Viewer)

GLese

Registered User.

CJ_London

Super Moderator

plog

Banishment Pending

GLese

Registered User.

GLese

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

GLese

Registered User.

reggiejdean

New member

Similar threads

Users who are viewing this thread