Hermanifer
asked on
Calling up the Networkdays Excel functions to use in Access Query
I have seen that it is possible to call up Excel Functions in Access. I want to pull the Networkdays function is Access so I can use it in a query. I have tried several modules but still can't use the funcition in a query. Can someone guide me (step-by-step) on what to do?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When I wrote that function, there were no other responses.
FWIW: My solution doesn't do the loops suggested by Capricorn and TextReport, so no matter how far apart your dates are, it'll always take the same amount of time. I checked it against the EXCEL (NETWORKDAYS) function too.
The only trouble with it right now is that it doesn't handle the holidays. Let us know if that's a problem or not.
FWIW: My solution doesn't do the loops suggested by Capricorn and TextReport, so no matter how far apart your dates are, it'll always take the same amount of time. I checked it against the EXCEL (NETWORKDAYS) function too.
The only trouble with it right now is that it doesn't handle the holidays. Let us know if that's a problem or not.
Any luck with this yet?
Public Function NetworkDays(start_date As Date, end_date As Date, Optional holidays As Variant)
'Currently does nothing with the holidays
NetworkDays = DateDiff("d", start_date, end_date) + 1
NetworkDays = NetworkDays - Int(NetworkDays / 7) * 2
If DatePart("w", start_date) = vbSunday And DatePart("w", end_date) <> vbSaturday Then
'Start on Sunday but not ending on Saturday
NetworkDays = NetworkDays - 1
ElseIf DatePart("w", start_date) = vbSaturday And DatePart("w", end_date) <> vbFriday Then
'Start on Saturday and not ending on Friday
NetworkDays = NetworkDays - IIf(DatePart("w", end_date) = vbSaturday, 1, 2)
ElseIf DatePart("w", start_date) > vbSunday _
And DatePart("w", start_date) < vbSaturday _
And DatePart("w", end_date) = vbSaturday Then
'Start on a weekday and end on saturday
NetworkDays = NetworkDays - 1
End If
End Function