September 1999

Calculate the Business Hours Between Two Dates

In addition to week-ending dates, databases often require you to calculate the business hours between two dates. For example, say you have a helpdesk database and you want to determine how many hours it takes to resolve each call. Theoretically, each call could take several days to complete. However, when you calculate a call's completion hours, you only want to include regular business hours. In this article, we'll show you a short, efficient, user-defined function that adds the total number of business hours between two dates, the results of which you can see in the query shown in Figure A.

Figure A: In this query, we used our custom function to calculate the total business hours between the two dates.

The DateDiff() function

When you want the difference between two date or time values, the DateDiff() function makes it easy to return the intervening hours, days, weeks, months, or years. We'll use several different variations of this function to determine the business hours. It follows the syntax

DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)

You indicate the time interval by which you want to increment the date in the interval argument. The time interval can be days, months, years, or any one of the other intervals shown in Table A. Enter the dates you want to compare in the date1 and date2 arguments. Access accepts real date values, variables, or date field names for date1 and date2. The last two optional, self-explanatory arguments let you alter the first day of the week and the first week of the year values.

Table A: DateDiff interval settings

Setting	Interval
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

So, to determine the number of days between #8/15/1999# and #8/21/1999#, you use an expression such as

DateDiff("d", #8/15/1999#, #8/21/1999#)

The UDF's technique

In its most basic form, our custom function determines the number of elapsed business days between two dates, and then multiplies that total by the number of hours in a work day. However, because the DateDiff() function treats any partial day as a full day, which would result in whole-day hourly figures, our custom procedure determines the business hours from three separate calculations: the partial hours in the first day, the hours for the intervening work days, and the partial hours for the last day. To determine the hours in the first day, the function subtracts the time of the initial call from the end of the work day (5:00 P.M.). To determine the intervening days, the function subtracts 1 from the total number of work days, and then multiplies the result by the total number of work hours in each day. (The DateDiff() function includes the last day in its results, but we only want the intervening days, so that's why the code subtracts 1.) Finally, to determine the hours in the last day, the function subtracts the time of call completion from the beginning of the work day (8:00A.M.). Listing A shows the completed function.

TIP: To determine the working days between two dates (not excluding holidays), use the following expression: =DateDiff("d", Date1, Date2) - _ (DateDiff("ww", Date1, Date2)*2)

Listing A: The BusinessHours custom function

Public Function BusinessHours(StartDate As Date, EndDate As Date) As Long

Dim intWkEndDays As Integer
Dim intWrkDays As Integer
Dim intFirstDayHrs As Integer
Dim intLastDayHrs As Integer
Dim lngInterDayHrs As Long
Dim intDaySpan as Integer

Const cGet_Lunch As Integer = 5 
'lunch break accrued after 5 hrs

Const cIn_Time As Date = #8:00:00 AM# 
'standard start of day

Const cOut_Time As Date = #5:00:00 PM# 
'standard end of day

Const cWork_Hours As Integer = 8 
'default working hours per day

Const cDays_Off As Integer = 2
'default number of week-end days

intWkEndDays = DateDiff("ww", StartDate, EndDate) * cDays_Off
intWrkDays = DateDiff("d", StartDate, EndDate) - intWkEndDays
intDaySpan = Abs((intWrkDays > 0))

intFirstDayHrs = DateDiff("h", TimeValue(StartDate), cOut_Time)
intFirstDayHrs = intFirstDayHrs + (intFirstDayHrs > cGet_Lunch)
'(intFirstDayHrs > cGet_Lunch) returns -1 (True) or 0 (False)
'For lunch periods less than 1hr, use
'IIF(intFirstDayHrs > cGetLunch, -.5, 0)

intLastDayHrs = DateDiff("h", cIn_Time, TimeValue(EndDate))
intLastDayHrs = (intLastDayHrs + (intLastDayHrs > cGet_Lunch)) _
	* intDaySpan
'intDaySpan prevents function from including intLastDayHours
'when StartDate & EndDate are same day.

lngInterDayHrs = (intWrkDays - (intDaySpan * 1)) * cWork_Hours
'intWrkDays calc includes last day as 1 full day.
'(intDaySpan * 1) removes this day when StartDate
'and EndDate span 2 or more days.

BusinessHours = intFirstDayHrs + lngInterDayHrs + intLastDayHrs
End Function

TIP: When you use constants in place of real values in a procedure, you'll save time later. If you need to change a value, such as cGet_Lunch in our code, you don't need to hunt down each time you've used the value--simply change the constant's value.

Use the BusinessHour function in a help-desk query

To use the function, we'll create a new module and enter the code from Listing A. Next, we'll create a table that contains helpdesk call records. Finally, we'll build the query from Figure A that uses the function to calculate elapsed business hours. To begin, open a new database, select the Modules tab from the Database window, and then click New. When Access opens the Module window, enter the code from Listing A at the insertion point. When you've finished, close and save the module as modBusinessCalcs.

We based our helpdesk query on the table shown in Figure B.

Figure B: We'll use our custom function to determine the business hours between the dates in this call transaction table.

As you can see, CallID is an AutoNumber field. We made this field the table's Primary Key. CallInitTime and CallFinTime are both Date/Time fields with "mm/dd/yyyy h:nn AM/PM" as their Format property. After you've created the table, name it tblCallTrans, and then switch to Datasheet view to enter the records shown in Figure B. After you've finished, we'll create the query.

To do so, with tblCallTrans still highlighted in the Database window, select Query from the New Object button's dropdown list. Double-click on Design View to open the QBE grid. Access automatically adds the tblCallTrans to the query, which is exactly what we want. Figure C shows qryCallTrans' completed design. When you run the query, it displays the results from
Figure A.

Figure C: The last column in this query passes CallIntTime and CallFinTime to the BusinessHours custom function.

Excluding holidays

You probably noticed that our UDF has one piece missing--it doesn't accommodate holidays. Fortunately, we can easily add that option into the existing calculations. To do so, we'll create a second function that determines the number of holidays between two dates, and then subtract that figure from the working days total. We'll use a second table to store the list of holidays, and then use DAO to access it.

Using the tblHoliDaze table

Figure D shows the table we created to hold our dates.

Figure D: We created a table of holidays to use in our custom function.

As you can see, it contains only a single field, with each date as a unique entry. We used a Date/Time field so that our function can manipulate this field as a date; however, each holiday's year is actually irrelevant. After you've created the table, save it as tblHoliDaze and enter the dates shown in Figure D. For simplicity, we only added four holidays to the list.

Next, in the modBusinessCalcs module, create a new function named HoliDaze. This function will loop through each date in tblHoliDaze and tally the number of holidays that fall between the StartDate and EndDate from the BusinessHours procedure. Listing B shows the completed code for the HoliDaze function.

Listing B: The HoliDaze function

Public Function HoliDaze(dtBeginDate As Date, dtEndDate As Date)

Dim db As Database
Dim rst As Recordset
Dim intDays As Integer
Dim dtTest As Date
Dim intTtlYears As Integer
Dim intStartYear As Integer
Dim x As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("tblHoliDaze")
intDays = 0
intStartYear = Year(dtBeginDate)
intTtlYears = Year(dtEndDate) - intStartYear

With rst
	.MoveFirst
	Do Until .EOF
		For x = 0 To intTtlYears
			dtTest = DateSerial(intStartYear + x, 
				Month(!HoliDate), _
				Day(!HoliDate))

			If dtTest >= dtBeginDate And dtTest 
				<= dtEndDate Then
				intDays = intDays + 1
			End If
		Next x
		.MoveNext
	Loop
	.Close
End With

HoliDaze = intDays

Set rst = Nothing
Set db = Nothing
        
End Function

To use this function in the BusinessHours procedure, change the line

intWrkDays = DateDiff("d", StartDate, EndDate) _
	- intWkEndDays 

intWrkDays = DateDiff("d", StartDate, EndDate) _
	- intWkEndDays - HoliDaze(StartDate, EndDate)

Conclusion

Most databases require some sort of date manipulation. While Access provides several date-manipulation functions, you'll often need to provide additional functionality. In this article, we've shown you one way to calculate the business hours between two dates.

Many thanks to Simon Ferris, who suggested the idea for this article. If you've got a topic that you'd like to see us cover, let us know. (microsoft_access@zdjournals.com) Of course, we can't always reply right away, but we'll do our best!

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.