Answered by:
Diff2Dates function  Business hours
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.
Answers

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.
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
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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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(#20170622 09:30#,#20170627 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
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...


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 nonbusiness 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



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 nonbusiness 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...
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Tuesday, June 27, 2017 1:45 AM

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

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.
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
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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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(#20170622 09:30#,#20170627 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

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 precalculated 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.

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(#20170622 09:30#,#20170627 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

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(#20170622 17:30#,#20170627 17:00#,#09:00#,#17:00#,0,0,2,3,4,5,6)
1410Apart from that, I think this may be just what I was after :)

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 postlunch 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

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

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 LongDim 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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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, "yyyymmdd") & "#")) 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 varDayIf blnSwapped = False Then
MinutesWorked = lngMinutes
Else
MinutesWorked = "" & lngMinutes
End If
End Function