VB scripts to calculate as NetWorkdays
 

I tried to write a macro by using VB Editor. I need to calculate the total
working days (same as the NetWorkdays function in the Excel). I will
appreciate if you have any sampel code or any hit on how...

VB scripts to calculate as NetWorkdays
 

?application.run("networkdays",date,date + 20)

gives 15

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
"ciccia" wrote in message
...
I tried to write a macro by using VB Editor. I need to calculate the total
working days (same as the NetWorkdays function in the Excel). I will
appreciate if you have any sampel code or any hit on how...

VB scripts to calculate as NetWorkdays
 

Hi,

what about this example:

Function Networkdays2(start_date As Date, end_date As Date, _
Optional holidays As Variant) As Long
Dim ToSun As Long, FromSun As Long, Days As Long
Dim i As Long, n As Long
Dim varHolidays As Variant, v As Variant
Dim x As Date, a() As Date

On Error GoTo ErrorHandler

If start_date end_date Then GoTo ErrorHandler

ToSun = 7 - Weekday(start_date + 5)
FromSun = Weekday(end_date) - 1
Days = Int((end_date - (start_date - 1) - ToSun) / 7) * 5
If ToSun 2 Then ToSun = ToSun - 2 Else ToSun = 0
If FromSun 5 Then FromSun = 5
Days = Days + ToSun + FromSun

If Not IsMissing(holidays) Then
If TypeName(holidays) = "Range" Then
Set varHolidays = holidays
ElseIf IsArray(holidays) Then
varHolidays = holidays
Else
varHolidays = Array(holidays)
End If
For Each v In varHolidays
If IsDate(v) Then
x = CDate(v)
For i = 1 To n
If a(i) = x Then Exit For
Next
If i n Then
n = n + 1
ReDim Preserve a(1 To n)
a(n) = x
If x = start_date And x <= end_date And _
Weekday(x + 1) 2 Then Days = Days - 1
End If
End If
Next
End If
Networkdays2 = Days
Exit Function

ErrorHandler:
Networkdays2 = -1
Exit Function
End Function

--
HTH,

okaizawa


ciccia wrote:
I tried to write a macro by using VB Editor. I need to calculate the total
working days (same as the NetWorkdays function in the Excel). I will
appreciate if you have any sampel code or any hit on how...