[AccessD] Needed: GetBusinessDays(Date1, Date2)

http://www.elementkjournals.com/premier/showArticle.asp?origSearchTerm=busin
ess%20days&aid=13136 

Unless you've got a sub, you can't get in though -- I don't usually do this,
but I couldn't find your email in the message header anywhere to send this
to you privately. 

Susan H. 

Don't get caught by surprise-calculate business days available to meet a
deadline
by Susan Sales Harkins and Doris Manning
Application(s): Microsoft Access 97/2000/2002
Operating System(s): <none>
Download: http://download.elementkjournals.com/access/200402/busdays.zip
Knowing the exact number of business days between two dates can be critical.
Certainly, it's one of those times when almost right isn't enough. An
accurate count can mean realizing an investment or bringing a project in on
time, so you need precision, not estimates. You may already have a function
that accurately returns the number of business days. However, it may be a
tad slow if it uses a loop to determine whether every day in the time period
is a weekday or a weekend day. If that's the case, you might want a faster
performing function. In this article, we'll show you a function that
accommodates holidays and quickly returns the exact number of business days
between two dates because there's no loop to slow things down.
Note: When we refer to work or business days in relation to our function, we
mean a standard Monday through Friday workweek-if your workweek includes
Saturday and Sunday, this function won't work for you.
Improving working conditions
The last thing you need is more work, but that's exactly what you'll get if
you're guessing at the number of workdays that you have to finish a project.
Our technique includes three objects: a simple table for storing your
holidays, a form that you'll use to enter both anchor dates, and one module.
By anchor dates, we mean the first and last dates in any given time period
for which you want to return just the business days, excluding weekend days
and holidays.
First, we'll create the table and enter the holidays you want to exclude
from the count. Then, we'll create a simple form that accepts two dates and
returns the exact number of business days between the two dates-minus any
holidays that may fall within the timeframe. In the final step, we'll open a
standard module and enter the function procedure that calculates the
business days.
Setting it up
The first thing you need is a simple table to store holidays. Create a new
table named tblHolidays. The table needs two fields: HolidayDate (a
Date/Time field) and HolidayDescription (a text field). (You can set a
primary key if you like, but one isn't required for this technique.) We'll
work with the small table of selected United States holidays shown in Figure
A. You can enter other holidays, but your results may differ from ours.
Figure A: Store a year's worth of holiday dates in the tblHolidays table. 

As you enter your own dates, don't include holidays that fall on a Saturday
or Sunday, since the function excludes all weekend days. For instance, July
4 falls on a Sunday in 2004. However, if your organization compensates for
such holidays by closing on the previous Friday or following Monday, you'll
want to include that date. Likewise, if you take off the Friday following
the Thanksgiving holiday, allow for both days.
Use the form shown in Figure B to pass two values to a function procedure
that performs the actual calculations. To create the form, open a blank form
and add three text box controls, a command button, and two label controls,
using Figure B as a guide. Note that since the label controls to the right
of the textbox controls are initially blank, you must enter a few space
characters in them. These labels really aren't critical to the success of
the function, but they'll display the day of the week for both of the two
anchor dates. Set the control captions as shown in Figure B, and then refer
to Table A for the remaining form and control properties. Finally, save the
form as frmBusinessDays.
Figure B: Use this form to gather and then pass both anchor dates to the
function procedure that returns the number of business days between the
dates. 

Table A: Form and control properties Object Property Setting 
Form Caption Calculate Business Days 
Scroll Bars Neither 
Record Selectors No 
Navigation Buttons No 
Textbox Name txtStart 
Format ShortDate 
Textbox Name txtEnd 
Format ShortDate 
Command Button Name cmdCalculate 
Caption Calculate 
Textbox Name txtBusinessDays 
Label Name lblStart 
Label Name lblEnd 

Now, you're ready to enter the code that runs the form. To do so, click the
Code button on the Form Design toolbar to launch the Visual Basic Editor
(VBE). Enter the event procedures shown in Listing A, and then close the
VBE. Save the form and close it.
Listing A: Control event procedures 
Private Sub cmdCalculate_Click()
Dim dteStart As Date
Dim dteEnd As Date
If IsNull(txtStart) Then
 MsgBox "Please enter a start date", vbOKOnly, "Error"
 Exit Sub
End If
If IsNull(txtEnd) Then
 MsgBox "Please enter an ending date", vbOKOnly, "Error"
 Exit Sub
End If
dteStart = txtStart
dteEnd = txtEnd
txtBusinessDays = BusinessDays(dteStart, dteEnd)
End Sub
Private Sub txtStart_LostFocus()
 lblStart.Caption = Format(txtStart, "dddd")
End Sub

Private Sub txtEnd_LostFocus()
 lblEnd.Caption = Format(txtEnd, "dddd")
End Sub
Creating BusinessDays()
Now, you're ready to add the function procedure that calculates the business
days between the two days you'll enter in the form you just created. Switch
to the Modules sheet of the Database window and choose Module from the
Insert menu. Next, enter the function in Listing B, and then save the module
as basBusinessDays. If you're using DAO, make the substitutions shown in
Table B. Finally, close the VBE.
Listing B: Function to return number of workdays 
Function BusinessDays(startdate As Date, enddate As Date) As Integer
Dim intHolidays As Integer
Dim intTotalDays As Integer
Dim intWeekendDays As Integer
Dim rst As New ADODB.Recordset
Dim strSQL As String
Select Case DatePart("w", startdate, vbMonday)
 Case Is = 6
  startdate = DateAdd("d", startdate, 2)
 Case Is = 7
  startdate = DateAdd("d", startdate, 1)
End Select
Select Case DatePart("w", enddate, vbMonday)
 Case Is = 6
  enddate = DateAdd("d", enddate, -1)
 Case Is = 7
  enddate = DateAdd("d", enddate, -2)
End Select
strSQL = "SELECT Count(*) " & _
 "FROM tblHolidays " & _
 "WHERE HolidayDate BETWEEN #" & startdate & _
 "# AND #" & enddate & "#"
rst.Open strSQL, CurrentProject.Connection
intHolidays = rst(0)
intTotalDays = DateDiff("d", startdate, enddate) + 1
intWeekendDays = DateDiff("ww", startdate, enddate, vbMonday) * 2
BusinessDays = intTotalDays - intWeekendDays - intHolidays
Set rst = Nothing
End Function
Table B: DAO substitutions ADO DAO 
Dim rst As New ADODB.Recordset Dim rst As DAO.Recordset 
rst.Open strSQL, _
CurrentProject.Connection Set rst = _
CurrentDb.OpenRecordset(strSQL) 

The function looks more complicated than it is. First, the function adjusts
the two passed dates if either falls on a Saturday or Sunday. Specifically,
if the starting date falls on a Saturday or Sunday, the first SELECT CASE
statement adds 1 or 2 days to startdate, respectively, to make sure
startdate is the following Monday. Similarly, if the ending date falls on a
Saturday or Sunday, the second SELECT CASE statement respectively subtracts
1 or 2 days, making enddate the previous Friday. Then, the function
determines the number of records in tblHolidays where HolidayDate falls
between the two passed dates, startdate and enddate. That value is stored in
the integer variable intHolidays.
At this point, the function uses the DateDiff() function to determine the
total number of days between the (possibly modified) startdate and enddate
values. Using the same startdate and enddate values, the function again uses
the DateDiff() function to determine the number of weekend days that fall in
the same time period. Finally, the function subtracts the number of weekend
days and holidays from the total number of days to determine the number of
business days.
Calculating business days
Now, you're ready to put everything to use, so open frmBusinessDays. To
determine the number of business days between two dates, simply enter the
first and last dates in the time period. For instance, enter 1/1/2004 and
1/10/2004 and then click Calculate. Figure C shows the result, which is 6.
Figure C: Click the Calculate button to return the number of business days
between the two dates. 

Let's take a look at everything that went on to return the value 6. After
you entered date values in the first two controls, the LostFocus event
procedures for those controls displayed the corresponding date's day of the
week value to the right (in the label controls). These values aren't used,
but it might be useful to know what day of the week each date represents.
Clicking the Calculate button executed that button's Click event procedure.
This procedure initially ensures that neither anchor date control is Null,
which would return an error. You don't need to check for actual date values
because both control's Format properties are set to Short Date. If you end
up eliminating that Format property, you'll want to add code that verifies
that the value is indeed a date. The event procedure then passed the two
date values to BusinessDays() and then displayed the result in
txtBusinessDays (the third text box).
In the above example, the event procedure passed 1/1/2004 and 1/10/2004. The
later date falls on a Saturday, so Access adjusted enddate by subtracting 1
day. As a result, enddate then equaled 1/9/2004. Then, the number of
holidays between 1/1/2004 and 1/9/2004 were returned to the variable
intHolidays. In this case, that value was 1.
The total number of days between the two dates was 9 and the total number of
weekend days was 2. Therefore, the final expression evaluated as follows:
BusinessDays = intTotalDays - intWeekendDays - intHolidays
BusinessDays = 9 - 2 - 1
BusinessDays = 6
A couple of points worth noting
It's worth pointing out that the BusinessDays() function currently allows
users to enter ending dates that fall before the start date. When this
occurs, the function result is a negative value. You may or may not want to
alter that behavior. If you want to prevent such cases, you might want to
add a simple If...Then statement that checks the values and returns a
message if the dates are inappropriate.
Take a holiday!
Sometimes you may feel that you spend more time keeping track of tasks and
deliverables than actually working on them, so using a tool like Access to
manage tasks can be a sanity saver. However, it's easy to get behind
schedule if you don't allocate an appropriate amount of time to complete the
tasks at the outset. Fortunately, you can use a custom function like the one
we created in this article to ensure that you set realistic due dates. Soon
you'll be able to take advantage of those paid holidays without worrying
that projects are off schedule! 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Christopher
Hawkins
Sent: Wednesday, March 31, 2004 12:34 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Needed: GetBusinessDays(Date1, Date2)

I am utterly shocked that a Google search on GetBusinessDays turned up
squat.

Anyways, I need an Access VBA function that will return the number of
business days (Mon - Fri) between two dates.  I know this is the type of
stuff that's supposed to be all over the net, but I am finding nothing.

Heeeeeeeeeeelp...

-Christoher Hawkins-


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com