Microsoft KB Archive/210562

= ACC2000: How to Find Number of Working Days Between Two Dates =

Article ID: 210562

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210562



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
This article shows you how to calculate the number of working days between two dates.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Microsoft Access does not have a built-in function to determine the number of working days between two 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/99 and 02/03/99 equals one. Create a module and type the following line in the Declarations section if it is not already there: Option Explicit Type the following procedure: 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 On Error GoTo Err_Work_Days

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

Exit Function

Err_Work_Days:

' If either BegDate or EndDate is Null, return a zero ' to indicate that no workdays passed between the two dates. '

If Err.Number = 94 Then Work_Days = 0 Exit Function Else ' If some other error occurs, provide a message. MsgBox "Error " & Err.Number & ": " & Err.Description End If

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 Immediate window: ?Work_Days("01/01/1996", "12/31/1999") The date range above returns 1045. ?Work_Days(#03/05/1999#, #04/06/1999#) The date range above returns 23.

Additional query words: inf modules day/time workday determine weekdays week days

Keywords: kbhowto kbprogramming KB210562

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.