Function to calculate # of weekdays in a date range, not concerned with holidays

[grrlyracer]

Hello Everyone,



Access 2010. This is my first post here so pardon my lingo lol.

I am new to Access VBA and have only used query design and built-in functions. But after reading some posts on this site, I understand that I need to build a custom function in a module and am having some difficulty getting sample codes to run. Also, when I test any sample codes in the Immediate Window I keep getting the error "Compile Error: Expected: line number or label or statement or end of statement".

I need a function to calculate the number of weekdays in a given date range (Mon-Fri only) and am not concerned with holidays at all. However, I do need to use the Date() arguement within the function. Similar to the networkdays function in Excel, I could enter =networkdays(10/17/2013,Date()) so it would calculate 3 businsess days. Of course in Access the "Start date" would be a field we call [Date_Last_Updated] so the formula would be more like
=networkdays([Date_Last_Updated,Date()) so I can calculate the # of days since an item was last updated to today's date.

Any help is GREATLY appreciated!

[June7]

You are missing ending ] for the field name.

[grrlyracer]

Oops, my mistake. But that is not the issue, i just retyped it wrong. The main issue, is that Access does not have a built-in "networkdays" function, so I need a VBA code so I can create a custom function in a module or even an alternative function that will calculate the count of days with dates that fall between Monday and Friday. The formula I entered was just an example of the value I was trying to obtain.

I have found a ton of "networkdays" VBA codes in this forum, but they don't work either because they are factoring in a "holidays" table or other unknown issues.

[June7]

Adapt code to accommodate your situation, remove or comment code concerning holidays. What other 'unknown' issues? Post your attempted code for analysis. Have you step debugged? Review link at bottom of my post for debugging guidelines.

This code doesn't deal with holidays http://www.devhut.net/2010/10/22/ms-...een-two-dates/

[grrlyracer]

Thank You, June7, for working with me : ) I appreciate your patience in dealing with my beginner-level knowledge! I have never done any custom VBA before, unfortunately my determination to figure this out persists.

Here is the most successful code that I tried. Yes, I did the debug and compile and received no errors. However, on Friday when I tested the function in the "Immediate Window" I received a value of "0" networkdays between 10/1/2013 and 10/18/2013. And now today when I test I am getting the error "Compile Error: Expected: line number or label or statement or end of statement." Perhaps, I have recreated the module incorrectly today. But still the code did not work on Friday.

Also, I am very new to this, so I attempted to remove the portion pertaining to holidays but then it failed all together.

[code]
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
End If
End If
Next i

End Function
[code]

[June7]

If you don't want holidays considered then remove the If Then lines that reference them. This works:

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbMonday) < 6 Then
NetWorkdays = NetWorkdays + 1
End If
Next i

Call the function from immediate window with:

?NetWorkdays(#10/1/2013#,#10/18/2013#)

[grrlyracer]

June7, you are a lifesaver! It worked perfectly! I tried deleting the holiday portion but I realize now that I deleted too much. In the sample below, I thought all of the code in red was related to the Holidays. Thanks again for your help today!!

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
End If
End If
Next i