Count Workdays in Access 2000

Of course you could turn this into a function.

Hi Idfoxx,

Microsoft Excel has an interesting function; NetworkDays(); which is used to calculate the number of working days between two dates. NetworkDays() is located in a Microsoft Office Add-In DLL, MSOWCF.DLL, which is located in the "C:\Program Files\Microsoft Office\Office" folder.

To use this functionality in Access, do the following:

1. In the MS Access VB Editor Window, click the Tools menu and select the References menu item. Now click the Browse button, change the Files of Type drop-down to "Executable Files (*.exe; *.dll) and locate the DLL mentioned above.

2. In the Modules pane of the Database window, create a new Module and enter a new Function as follows:

Public Function NetworkDays(dtStartDate As Date, dtEndDate As Date) As Variant

' create new OCATP instance
Dim objOCATP As New OCATP

NetworkDays = objOCATP.NetworkDays(dStartDate, dEndDate)

' release
Set objOCATP = Nothing

End Function

Since this function is Public, it will be available from within expression builder, query builder etc.

Hope this helps.

Regards.

John Mc Hale

I have my own version of NetWorkDays as in excel you can specify a range of dates to ignore. My function uses a Query based on a Table that list days to exclude.

Cheers, Andrew

Table: USystblBankHolidays
BHDate Date/Time
BHDesc Text

Query: USysqryBankHolidayCount
PARAMETERS pdteStartDate DateTime, pdteEndDate DateTime;
SELECT Count(USystblBankHolidays.BHDate) AS QTY
FROM USystblBankHolidays
WHERE (((USystblBankHolidays.BHDate) Between [pdteStartDate] And [pdteEndDate]))
WITH OWNERACCESS OPTION;

Paste the following code into a new module
Function MKC_LIB_NetWorkDays(BegDate As Variant, EndDate As Variant, Optional pbooIgnoreHolidays As Boolean) As Integer
Dim db As Database
Dim qd As QueryDef
Dim rst As Recordset

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim Holidays As Long

    BegDate = Int(CDate(BegDate))
    EndDate = Int(CDate(EndDate))
   
    If pbooIgnoreHolidays Then
       Holidays = 0
    Else
       Set db = CurrentDb()
       Set qd = db.QueryDefs("USysqryBankHolidayCount")
       qd.Parameters!pdteStartDate = BegDate 'Format(BegDate, "dd/mmmm/yyyy")
       qd.Parameters!pdteEndDate = EndDate 'Format(EndDate, "dd/mmmm/yyyy")
       Set rst = qd.OpenRecordset()
       Holidays = rst!qty
       rst.Close
    End If
   
    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

    MKC_LIB_NetWorkDays = WholeWeeks * 5 + EndDays - Holidays

End Function

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- points to twalgrave
Please leave any comments here within the
next seven days.

Finalized as proposed

modulo

Community Support Moderator
Experts Exchange