Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete scripts | Excel Worksheet Functions | |||
Forumlas or scripts? | Excel Discussion (Misc queries) | |||
SQL Scripts in Excel | Excel Discussion (Misc queries) | |||
need networkdays to calculate for all users. | Setting up and Configuration of Excel | |||
Macintosh and VB Scripts | Excel Programming |