ACC: How to Determine Number of Working Days Between Two Dates
ID: Q97757
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article shows you how to calculate the number of working days between
two dates.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
MORE INFORMATION
Microsoft Access does not have a function to determine the number of
working days between two given dates. To determine this, you need to call a
user-defined function. The following function includes the start date and
the end date; so, the number of days between 02/02/93 and 02/03/93 equals
one.
To create the function, follow these steps.
NOTE: In the following sample code, an underscore (_) is used as a line-
continuation character. Remove the underscore from the end of the line when
re-creating this code in Access Basic.
Option Explicit
Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
To call the function, you can pass either a valid string or an actual date
value. The following are two ways to call this function from the Debug
window (or Immediate window in Microsoft Access 2.0 or earlier):
?Work_Days("01/01/93", "12/31/96") (returns 1042)
?Work_Days(#03/05/93#, #04/06/93#) (returns 22
Additional query words:
modules day/time workday workdays weekday weekdays
Keywords : kbprg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto