Using MS Access/MS Access 2010
I am trying to build an expression or function in Access 2010 to calculate the number of work days between two (2) dates. I found the below function that you had posted a few years ago, but not sure exactly what I need to do as I am a fairly new Access user. Here is the function you had posted:
This is the function I use to calculate net workdays between two dates:
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If WeekDay(dteCurrDate, vbMonday) < 6 Then
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
NetWorkdays = NetWorkdays + 1
This requires a table that lists holidays.
Can you provide me the specifics on how I build this in Access 2010, specifically the start of the process and the steps. Also, I understand I need to build a table for the "Holidays", which I presume I name the table "Holidays" and the column should be named "Holdate". If that is not correct, can you tell me exactly what I need to do.
As a side note, the two (2) dates I have in my database is Date Packet Received and Date Packet Completed. I want to know how many workdays / time it has taken to complete the packet.
I thank you in advance for any help provided.
Correct, you need to build a table name it Holidays, with 2 fields; Holdate (as the Primary Key) and Holiday as the text description of the holiday. Next step is to copy the function into a global module If you don't have a module already created, then open the VBE (Visual Basic Environment) by pressing Ctrl+G. On the icon bar is a use the icon second from the left to create a new module. When it comes time to name it, name it something like modUDFs. Do not name it the same as the function. The next step is to paste the function into that module.
Once that is done, you can use the function as you would any Built-in function. For example, if you want to display the # of days in a control on a form, you would just enter:
as the Controlsource of the control. You would reference the actual controlnames for your startdate and enddate.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA