none
Diff2Dates function - Business hours RRS feed

  • Question

  • Hi.

    I am using the custom Diff2Dates function created by Graham Seach and Douglas J. Steele.  This works great in most respects but now I need to calculate business hours between 2 dates and times.

    Is it possible the function can do this and how?  I want to hardcode holiday dates as I am using a large dataset and don't want to slow processing down with constant hits on a table.

    Any help would be appreciated,

    Thanks.

    Monday, June 26, 2017 10:51 AM

Answers


  • It's working out the actual number of hours between the Start and End times, based on a working day where I am struggling.  A working day is currently set as 09:00 to 17:00

    Why would you need to do that if you have a fixed working day of 8 hours?  As you say, you can just multiply the number of working days returned by the function by 8, though the latter value should be stored as a value in a column in a table and looked up at runtime, not hard coded when the function is called, which would violate Codd's Information Rule.

    You'll find other functions for doing workdays arithmetic illustrated in Workdays.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes functions for use in a single country with a single set of public holidays, or in multiple countries with separate sets of public holidays, as here in the 3 countries and the province of Northern Ireland which make up the UK.

    For a more flexible solution you can use the following function which not only takes the start and end date/times as its arguments, but also the start and end times of the working day, and of a lunch break, and the days in the working weeks as a parameter array, e.g. 2,3,4,5,6 for a working week of Monday to Friday.  The function returns the duration in minutes, which can easily be converted to hours:minutes by means of integer division and the Mod operator:

    NumberOfMinutes\60 & ":" Format(NumberOfMinutes Mod 60,"00")

    The code for the function is:

    Public Function MinutesWorked(StartDateTime As Date, _
                    EndDateTime As Date, _
                    DayStarts As Date, _
                    DayEnds As Date, _
                    LunchStarts As Date, _
                    LunchEnds As Date, _
                    ParamArray WorkDays() As Variant) As Long

        Dim varDay As Variant
        Dim dtmDay As Date
        Dim intDayCount As Integer
        Dim lngMinutes As Long
        
        ' adjust start and end days values to working day
        'EndDateTime = EndDateTime + DayEnds
        'StartDateTime = StartDateTime + DayStarts
        
        ' get number of workdays
        For dtmDay = DateValue(StartDateTime) To DateValue(EndDateTime)
           For Each varDay In WorkDays
               If Weekday(dtmDay, vbSunday) = varDay _
                   And IsNull(DLookup("HolDate", "Holidays", _
                   "HolDate = #" & Format(dtmDay, "yyyy-mm-dd") & "#")) Then
                   intDayCount = intDayCount + 1
                   Exit For
               End If
           Next varDay
        Next dtmDay
        
        ' get total minutes for all workdays
        lngMinutes = (DateDiff("n", DayStarts, DayEnds) - DateDiff("n", LunchStarts, LunchEnds)) * intDayCount
        
        For Each varDay In WorkDays
            ' subtract unworked time on first day if a worked day
            If Weekday(StartDateTime) = varDay _
                And IsNull(DLookup("HolDate", "Holidays", _
                "HolDate = #" & Format(StartDateTime, "yyyy-mm-dd") & "#")) Then
                
                lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartDateTime))
                ' ignore lunch break if work started after lunch on first day
                If TimeValue(StartDateTime) >= LunchEnds Then
                    lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                End If
            End If
        
         ' subtract unworked time on last day if a worked day
            If Weekday(EndDateTime) = varDay _
                And IsNull(DLookup("HolDate", "Holidays", _
                "HolDate = #" & Format(EndDateTime, "yyyy-mm-dd") & "#")) Then
                
                lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndDateTime), DayEnds)
                ' ignore lunch break if work ended before lunch on last day
                If TimeValue(EndDateTime) <= LunchStarts Then
                lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                End If
            End If
        Next varDay

        MinutesWorked = lngMinutes
        
    End Function


    You can see how it works in the debug window by calling the function with some literal values:

    ? MinutesWorked(#2017-06-22 09:30#,#2017-06-27 17:00#,#09:00#,#17:30#,#12:30#,#13:00#,2,3,4,5,6)
     1860

    If the minutes are then converted to hours:minutes we get:

    ? 1860\60 & ":" & Format(1860 Mod 60,"00")
    31:00

    As the date range spans a weekend and the working week is defined by the parameter array values 2,3,4,5,6 as Monday to Friday the number of working days is 4.  The length of the working day is 8.5 hours, from which 30 minutes lunch break each day is subtracted, giving the hours worked each day as 8.  So over the 4 days the total hours worked would be 32, but the job has been started 30 minutes after the start time of 09:00 on the first day and finished 30 minutes before the end time of 17:30 on the last day, so one hour must be subtracted from the 32 hours, leaving us with 31 hours as above.


    Ken Sheridan, Stafford, England

    • Marked as answer by fbxiii Thursday, June 29, 2017 11:26 AM
    Tuesday, June 27, 2017 12:31 PM

All replies

  • Hi,

    It should be possible to modify the function to count business hours much like other functions counting only work days.

    As for the Holidays, if there's not too many, I guess you can hard code them. Otherwise, you might use a collection to store the dates in memory to help with the speed.

    Just my 2 cents...

    Monday, June 26, 2017 2:34 PM
  • I'm not sure how I can change that function, I am currently battling with a function of my own.

    I am storing 8 bank holiday dates as constants and these will need to be maintained.

    Monday, June 26, 2017 3:11 PM
  • Here is my code. 

    For some reason, I am getting -2 hours for StartDate 02/06/2017 15:40:06 and EndDate 05/06/2017 13:40:06. 

    And 24 hours for StartDate 07/06/2017 18:00:05 and EndDate 09/06/2017 17:00:00

    It is very much in testing at the moment but I am getting close!

    Public Function Calculate_Elapsed_Business_Hours(dt_StartDate As Date, dt_EndDate As Date) As Double
    ' Function to calculate the number of business hours between 2 dates.  Returns Date and Time.

        Dim dt_Date1 As Date
        Dim dt_Date2 As Date
        Dim int_Hours_Elapsed As Integer  ' Stores the number of Business hours
         
        Dim bln_Swapped As Boolean
       
        ' Store the Time in a temporary variable
        tmp_Time = Format(dt_StartDate, "hh:nn:ss")
       
        ' Check if Startdate is before the Enddate and swap them if necessary
        If dt_StartDate < dt_EndDate Then
            tmp_Date = Format(dt_StartDate, "dd/mm/yyyy")
            dt_Date2 = Format(dt_EndDate, "dd/mm/yyyy")
        Else
            tmp_Date = Format(dt_EndDate, "dd/mm/yyyy")
            dt_Date2 = Format(dt_StartDate, "dd/mm/yyyy")
            bln_Swapped = True
        End If
       
       
       
        Do Until tmp_Date = dt_Date2
       
            ' Check the Start Time and adjust accordingly
            If tmp_Time < dt_Business_Day_Start Then
               
                ' Set the time to the Business Day start
                tmp_Time = dt_Business_Day_Start
               
                Non_Business_Day_Check
               
            ElseIf tmp_Time > dt_Business_Day_End Then
               
                ' Add a day and set the Start time
                tmp_Date = tmp_Date + 1
               
                tmp_Time = dt_Business_Day_Start
               
                ' Check if the new day is bank holiday
                Non_Business_Day_Check
               
            Else
           
               
               
                ' Add a day and check for it being a non-business day
                tmp_Date = tmp_Date + 1
               
               
               
            End If
           
            If Non_Business_Day_Check = False Then
                int_Hours_Elapsed = int_Hours_Elapsed + 8
            End If
               
        Loop
       
        If tmp_Time < dt_Business_Day_Start Then tmp_Time = dt_Business_Day_Start
       
        ' Now to work out how many business hours are between the 2 time
        Calculate_Elapsed_Business_Hours = (CDate(Format(int_Hours_Elapsed / 24, "hh:mm:ss")) + CDate(Format(dt_EndDate, "hh:mm:ss")) - tmp_Time) * 24
           
           
        'Check if the dates were swapped and add a Minus if they were
       ' If bln_Swapped = True Then Calculate_Elapsed_Business_Hours = "-" & Calculate_Elapsed_Business_Hours
       
    End Function


    Public Function Non_Business_Day_Check(Optional Change_Time As Boolean) As Boolean

        '---------------------------------------------------------------------------------------------------------
        ' Function to check if the tmp_Date from function Calculate_SLA_Expiry is a weekend or bank holiday
        ' When checking the initial Created date, the tmp_Time is changed to Business start time and the remainder is set to 00:00
        '---------------------------------------------------------------------------------------------------------
           
        ' First check if the Created Date falls on a weekend
        If Weekday(tmp_Date) = 7 Then
                       
            ' Saturday. Add 2 days to tmp_Date and change the time to the business start time
            tmp_Date = tmp_Date + 2
           
            ' Check if the Change Time flag is set and reset the time to the Business day start time
            If Change_Time = True Then
                tmp_Remainder = Format("00:00", "nn:ss")
                tmp_Time = dt_Business_Day_Start
            End If
           
            Non_Business_Day_Check = True
           
        ElseIf Weekday(tmp_Date) = 1 Then
                   
            ' Sunday.  Add 1 day to tmp_Date and change the time to the business start time
            tmp_Date = tmp_Date + 1
           
            ' Check if the Change Time flag is set and reset the time to the Business day start time
            If Change_Time = True Then
                tmp_Remainder = Format("00:00", "nn:ss")
                tmp_Time = dt_Business_Day_Start
            End If
           
            Non_Business_Day_Check = True
           
        End If
                     
        ' Now to check if the date is a Bank Holiday.
       
        If tmp_Date = dt_Bank_Holiday_1 Or _
            tmp_Date = dt_Bank_Holiday_2 Or _
            tmp_Date = dt_Bank_Holiday_3 Or _
            tmp_Date = dt_Bank_Holiday_4 Or _
            tmp_Date = dt_Bank_Holiday_5 Or _
            tmp_Date = dt_Bank_Holiday_6 Or _
            tmp_Date = dt_Bank_Holiday_7 Or _
            tmp_Date = dt_Bank_Holiday_8 Then
                           
            ' tmp_Date is a Bank holiday, add a day and set the time to the Business day start time
            tmp_Date = tmp_Date + 1
                  
            ' Check if the Change Time flag is set and reset the time to the Business day start time
            If Change_Time = True Then
                tmp_Remainder = Format("00:00", "nn:ss")
                tmp_Time = dt_Business_Day_Start
            End If
           
            Non_Business_Day_Check = True
         
        End If
        
        If Non_Business_Day_Check <> True Then Non_Business_Day_Check = False
       
    End Function


    • Edited by fbxiii Monday, June 26, 2017 3:15 PM
    Monday, June 26, 2017 3:15 PM
  • Hi,

    Just curious... Have you tried stepping through the code to see why you're getting the wrong result?

    Monday, June 26, 2017 3:41 PM
  • Yes.  The issue appears to be around the int_Hours_Elapsed not populating in all cases.  I'm not 100% sure I am approaching it in the right way although some results are as expected.
    Monday, June 26, 2017 3:45 PM
  • Hi,

    Tracing the code you posted above, I see you declare int_Hours_Elapsed but not assign any value to it until the check for a non-business day, where you then add 8 hours to it. Later, you divide the value by 24 to format the result. So, if the value is empty or zero because the dates you passed to the function are business days, you might be getting a wrong value when dividing zero by 24.

    Just a thought...

    Monday, June 26, 2017 4:15 PM
  • That was along the lines of what I was thinking.

    I am wondering if there is a simpler way.  I have a piece of code to calculate the number of working days between 2 dates, which could be multiplied by the number of Business hours in a day which is currently set to be 8.

    It's working out the actual number of hours between the Start and End times, based on a working day where I am struggling.  A working day is currently set as 09:00 to 17:00

    Tuesday, June 27, 2017 8:57 AM

  • It's working out the actual number of hours between the Start and End times, based on a working day where I am struggling.  A working day is currently set as 09:00 to 17:00

    Why would you need to do that if you have a fixed working day of 8 hours?  As you say, you can just multiply the number of working days returned by the function by 8, though the latter value should be stored as a value in a column in a table and looked up at runtime, not hard coded when the function is called, which would violate Codd's Information Rule.

    You'll find other functions for doing workdays arithmetic illustrated in Workdays.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes functions for use in a single country with a single set of public holidays, or in multiple countries with separate sets of public holidays, as here in the 3 countries and the province of Northern Ireland which make up the UK.

    For a more flexible solution you can use the following function which not only takes the start and end date/times as its arguments, but also the start and end times of the working day, and of a lunch break, and the days in the working weeks as a parameter array, e.g. 2,3,4,5,6 for a working week of Monday to Friday.  The function returns the duration in minutes, which can easily be converted to hours:minutes by means of integer division and the Mod operator:

    NumberOfMinutes\60 & ":" Format(NumberOfMinutes Mod 60,"00")

    The code for the function is:

    Public Function MinutesWorked(StartDateTime As Date, _
                    EndDateTime As Date, _
                    DayStarts As Date, _
                    DayEnds As Date, _
                    LunchStarts As Date, _
                    LunchEnds As Date, _
                    ParamArray WorkDays() As Variant) As Long

        Dim varDay As Variant
        Dim dtmDay As Date
        Dim intDayCount As Integer
        Dim lngMinutes As Long
        
        ' adjust start and end days values to working day
        'EndDateTime = EndDateTime + DayEnds
        'StartDateTime = StartDateTime + DayStarts
        
        ' get number of workdays
        For dtmDay = DateValue(StartDateTime) To DateValue(EndDateTime)
           For Each varDay In WorkDays
               If Weekday(dtmDay, vbSunday) = varDay _
                   And IsNull(DLookup("HolDate", "Holidays", _
                   "HolDate = #" & Format(dtmDay, "yyyy-mm-dd") & "#")) Then
                   intDayCount = intDayCount + 1
                   Exit For
               End If
           Next varDay
        Next dtmDay
        
        ' get total minutes for all workdays
        lngMinutes = (DateDiff("n", DayStarts, DayEnds) - DateDiff("n", LunchStarts, LunchEnds)) * intDayCount
        
        For Each varDay In WorkDays
            ' subtract unworked time on first day if a worked day
            If Weekday(StartDateTime) = varDay _
                And IsNull(DLookup("HolDate", "Holidays", _
                "HolDate = #" & Format(StartDateTime, "yyyy-mm-dd") & "#")) Then
                
                lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartDateTime))
                ' ignore lunch break if work started after lunch on first day
                If TimeValue(StartDateTime) >= LunchEnds Then
                    lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                End If
            End If
        
         ' subtract unworked time on last day if a worked day
            If Weekday(EndDateTime) = varDay _
                And IsNull(DLookup("HolDate", "Holidays", _
                "HolDate = #" & Format(EndDateTime, "yyyy-mm-dd") & "#")) Then
                
                lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndDateTime), DayEnds)
                ' ignore lunch break if work ended before lunch on last day
                If TimeValue(EndDateTime) <= LunchStarts Then
                lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                End If
            End If
        Next varDay

        MinutesWorked = lngMinutes
        
    End Function


    You can see how it works in the debug window by calling the function with some literal values:

    ? MinutesWorked(#2017-06-22 09:30#,#2017-06-27 17:00#,#09:00#,#17:30#,#12:30#,#13:00#,2,3,4,5,6)
     1860

    If the minutes are then converted to hours:minutes we get:

    ? 1860\60 & ":" & Format(1860 Mod 60,"00")
    31:00

    As the date range spans a weekend and the working week is defined by the parameter array values 2,3,4,5,6 as Monday to Friday the number of working days is 4.  The length of the working day is 8.5 hours, from which 30 minutes lunch break each day is subtracted, giving the hours worked each day as 8.  So over the 4 days the total hours worked would be 32, but the job has been started 30 minutes after the start time of 09:00 on the first day and finished 30 minutes before the end time of 17:30 on the last day, so one hour must be subtracted from the 32 hours, leaving us with 31 hours as above.


    Ken Sheridan, Stafford, England

    • Marked as answer by fbxiii Thursday, June 29, 2017 11:26 AM
    Tuesday, June 27, 2017 12:31 PM
  • Thanks for the response Ken.  I will take some time later to read it in detail.

    I am trying to create a query that tracks time elapsed against a pre-calculated SLA Expiry date/time.  We have varying amounts of time to clear queries down, some same day, others can span 2 or 3 days.  The aim was to be able to pass Now() to the function to get a 'Live' view of how long we have left and how many queries there are too clear to ensure we are meeting targets.

    Tuesday, June 27, 2017 3:00 PM
  • To compare date/time values you could call a function like mine to return the elapsed working time in minutes from the start date time until the current date/time returned by the Now() function, which you'd pass into the function as the end date/time argument.  If you don't need to allow for lunch breaks when doing the computation you can just pass zeros into the function as the values for the start and end of the lunch breaks, e.g. with my previous example:

    MinutesWorked(#2017-06-22 09:30#,#2017-06-27 17:00#,#09:00#,#17:30#, 0,0, 2,3,4,5,6)

    The functions return value could then be compared with the deadline date/time value by means of the DateDiff function, with "n" as its Interval argument.  That would give you the absolute time remaining.  If you wanted to know the working time remaining then you could call my function once with the Now() function as the end date/time, and again with the deadline as the end date/time.  Subtracting the return value of the former from that of the latter would give you the working time in minutes remaining until the deadline.

    Ken Sheridan, Stafford, England

    Tuesday, June 27, 2017 4:00 PM
  • Hi Ken.

    I have tried the code out but it is returning 23 Hours with dates used below.  But the Result should be 24 Hours.

    The 24 hours equate to 3 full business days which is the result I was after in this instance.  I will try some more combinations tomorrow morning and report back.

    ? MinutesWorked(#2017-06-22 17:30#,#2017-06-27 17:00#,#09:00#,#17:00#,0,0,2,3,4,5,6)
     1410

    Apart from that, I think this may be just what I was after :)

    Wednesday, June 28, 2017 4:38 PM
  • You are right about the wrong result, though it's 23:30 hours, not 23:00

    ? 1410\60 & ":" & 1410 Mod 60
    23:30

    It's because the function was designed for costing purposes in the context of a fixed working day, and therefore assumes that the start and end times are within the defined pre- and post-lunch hours of the working day.  If the start and end times differ from the start and end times of these periods, it therefore works on the basis that the worked time in the period on the first or last day of the job in this situation will be less than the standard period and subtracts the differential.

    In your case the start time is 30 minutes after the end time of the working day, so the differential of 30 minutes is subtracted, giving the result of 23:30.

    On reflection, therefore, the function as it stands is not suitable for your purposes.  I'll need to add some validation code so that, what in the context of the function's purpose, invalid start or end times can't be entered.  I'm grateful to you for flagging this up.

    I'm sure the basic methodology used in the function could be tailored to suit your requirements.  The provision for lunch breaks could of course be ditched completely.  It would then be a matter of amending the code so that, with a start time after or before the start or end of the working day it's adjusted, i.e. in the case of the start time it's moved forward to the next valid start date/time, and in the case of the end time it's moved back to the previous valid end date/time. I don't have the time to make the amendments myself, I'm afraid, but feel free to have a stab at it yourself.


    Ken Sheridan, Stafford, England

    Wednesday, June 28, 2017 5:18 PM
  • Yes, in the case where a query is received after the End of the day, it should move to the start of the next working day.  And the expiry also needs to be within a working day.  The expiry is precalculated when the data is pulled in on a daily basis though.

    The intention is to pass the SLA expiry as the Start time and Now() as the end time.  I think as this will also be within a Business day, I may not actually encounter the issue.

    Your code is a lot cleaner than mine, I will see what I can do with it and keep you posted.

    Thanks for all of your help.


    • Edited by fbxiii Thursday, June 29, 2017 8:52 AM
    Thursday, June 29, 2017 8:48 AM
  • Hi Ken.

    That codes work perfectly for my needs.  All I had to do was swap dates when the expiry hadn't yet been reached then add a - to it at the end.

    Thanks, this has saved me from banging my head off the desk :)

    Slightly amended code below:

    Public Function MinutesWorked(StartDateTime As Date, _
                     EndDateTime As Date, _
                     DayStarts As Date, _
                     DayEnds As Date, _
                     LunchStarts As Date, _
                     LunchEnds As Date, _
                     ParamArray WorkDays() As Variant) As Long

         Dim varDay As Variant
         Dim dtmDay As Date
         Dim intDayCount As Integer
         Dim lngMinutes As Long
        
        Dim tmpDate As Date
        Dim blnSwapped As Boolean
       
        If StartDateTime > EndDateTime Then
           
            tmpDate = StartDateTime
            StartDateTime = EndDateTime
            EndDateTime = tmpDate
           
            blnSwapped = True
           
        End If
       
       
        
         ' adjust start and end days values to working day
         'EndDateTime = EndDateTime + DayEnds
         'StartDateTime = StartDateTime + DayStarts
        
         ' get number of workdays
         For dtmDay = DateValue(StartDateTime) To DateValue(EndDateTime)
            For Each varDay In WorkDays
                If Weekday(dtmDay, vbSunday) = varDay _
                    And IsNull(DLookup("HolDate", "Holidays", _
                    "HolDate = #" & Format(dtmDay, "yyyy-mm-dd") & "#")) Then
                    intDayCount = intDayCount + 1
                    Exit For
                End If
            Next varDay
         Next dtmDay
        
         ' get total minutes for all workdays
         lngMinutes = (DateDiff("n", DayStarts, DayEnds) - DateDiff("n", LunchStarts, LunchEnds)) * intDayCount
        
         For Each varDay In WorkDays
             ' subtract unworked time on first day if a worked day
             If Weekday(StartDateTime) = varDay _
                 And IsNull(DLookup("HolDate", "Holidays", _
                 "HolDate = #" & Format(StartDateTime, "yyyy-mm-dd") & "#")) Then
                
                 lngMinutes = lngMinutes - DateDiff("n", DayStarts, TimeValue(StartDateTime))
                 ' ignore lunch break if work started after lunch on first day
                 If TimeValue(StartDateTime) >= LunchEnds Then
                     lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                 End If
             End If
        
          ' subtract unworked time on last day if a worked day
             If Weekday(EndDateTime) = varDay _
                 And IsNull(DLookup("HolDate", "Holidays", _
                 "HolDate = #" & Format(EndDateTime, "yyyy-mm-dd") & "#")) Then
                
                 lngMinutes = lngMinutes - DateDiff("n", TimeValue(EndDateTime), DayEnds)
                 ' ignore lunch break if work ended before lunch on last day
                 If TimeValue(EndDateTime) <= LunchStarts Then
                 lngMinutes = lngMinutes + DateDiff("n", LunchStarts, LunchEnds)
                 End If
             End If
         Next varDay

        If blnSwapped = False Then
         MinutesWorked = lngMinutes
        Else
         MinutesWorked = "-" & lngMinutes
        
        End If
       
     End Function

    Monday, July 3, 2017 1:10 PM