networkdays

[rossgcook]

There is a function in XL called networkdays, that dertermines the number of workings between two dates. The calculation also caters for the exclusion of holidays.

The nearest thing that l can locate in MS Access is datediff.

Is there a networkdays function in MS Acess?

Thanks in Advance
Ross

 

[THWatson]

Ross
Here's something that I downloaded from somewhere (don't know where) recently.

It may help point to something for you.

Tom

CALCULATING WORK DAYS: (Place these in a module)
Function CalcWkDays(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays(#01/01/01#, #07/01/01#)
'output: 129
'
Dim X As Integer
'
X = DateDiff("d", dteStartDate, dteEndDate) - 2 * DateDiff("ww", dteStartDate, dteEndDate) _
+ IIf(WeekDay([dteStartDate]) = 7, 1, 0)
CalcWkDays = X
End Function
'
Function CalcWkDays2(dteStartDate As Date, dteEndDate As Date) As Integer
'input: (from debug window) ? CalcWkDays2(#01/01/01#, #07/01/01#)
'output: 129
'NOTE:As written,this counts both start and end dates.
'
Dim n As Integer
n = 0
'
Do While dteStartDate <= dteEndDate
'17 in the following expression represents the numeric days of week for Sunday(1) and Saturday (7). It can
'be modified to exclude any specified days of the week.
n = n + IIf(InStr(&quot;17&quot;, WeekDay(dteStartDate)) = 0, 1, 0)
'
dteStartDate = dteStartDate + 1
'
Loop
'
CalcWkDays2 = n
End Function

 

[MichaelRed]

faq181-261





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PaulBricker]

I couldn't find a built-in Excel function call networkdays but if there is one you can use it in Access this way(this one calls the RoundUp() function from Excel.

Public Function xlRoundUp(Num as Double, Digits as Double) As Double
Dim xlApp as Object
Set xlApp = CreateObject ("Excel.Application")    
xlRoundUp = xlApp.WorksheetFunction.RoundUp(Num, Digits)
End Function

You would need to change the function and arguments for the NetWorkDays function but you can call it from a query and pass the two dates to the function to return your values.

Paul

 

[MichaelRed]

NetWorkDays (Excel) is an &quot;addIn&quot; function. Not incl in the default installation. You have to specify it as an optional 'package?'.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places