Werktage berechnen

1 1 1 1 1 1 1 1 1 1 Rating 3.67 (3 Votes)

Details: Kategorie: Codeschnipsel Datum/Zeit; Geschrieben von TommyK; Zugriffe: 9527

Problemstellung:

Für 32Bit und 64Bit Office Versionen

Wie berechnet man schnell mal die Anzahl von Werktagen
(5 oder 6 Tage Woche) ohne Berücksichtigung von Feiertagen?

Public Enum CalcType
    Tage_5 = 0
    Tage_6 = 1
End Enum

Public Function CalcDays(dtStart As Date, dtEnd As Date, _
    Optional Typ As CalcType = Tage_5) As Integer
    '------------------------------------------------------
    ' Procedure : CalcDays
    ' DateTime  : 04.03.2008 16:23
    ' Author    : TommyK
    '------------------------------------------------------
    Dim iDiff As Integer
    Dim i As Integer
    Dim iResult As Integer
    Dim dtTemp As Date
    On Error GoTo CalcDays_Error
    iDiff = DateDiff("d", dtStart, dtEnd)
    For i = 0 To iDiff
        dtTemp = DateAdd("d", i, dtStart)
        If Typ = Tage_5 Then
            If Weekday(dtTemp, vbMonday) <> 6 And _
            Weekday(dtTemp, vbMonday) <> 7 Then
                iResult = iResult + 1
            End If
        Else
            If Weekday(dtTemp, vbMonday) <> 7 Then iResult = iResult + 1
        End If
    Next i
    CalcDays = iResult
    On Error GoTo 0
    Exit Function
CalcDays_Error:
    Dim strErrString As String
    strErrString = "Error Information..." & vbCrLf
    strErrString = strErrString & "Error#: " & Err.Number & vbCrLf
    strErrString = strErrString & "in Zeile: " & Erl & vbCrLf
    strErrString = strErrString & "Description: " & Err.Description
    MsgBox strErrString, vbCritical + vbOKOnly, "Error in procedure CalcDays"
End Function

Aufruf:

Dim i As Integer, j As Integer
i = CalcDays("18.02.2008", "05.03.2008")
j = CalcDays("18.02.2008", "05.03.2008", Tage_6)

Ergebnis:
i wäre =13 und j wäre=15