|
Die Funktion NETTOARBEITSTAGE() kann ich nicht benutzen, da in meinem Gewerbe nicht der Samstag und Sonntag, sondern der Sonntag und Montag als Wochenendtag gelten.
Ich habe davon erfahren, das es in Excel 2010 möglich ist mit der Funktion NETTOARBEITSTAGE.INTL() die Wochenendtage variabel einzurichten. Kann man das nicht auch irgendwie in Excel 2003 oder Excel 2007 hinbekommen?
Die Funktion NETTOARBEITSTAGE.INTL() aus Excel 2010 kann in einer vereinfachten Version als Matrixformel oder mit einer benutzerdefinierten Funktion nachgebildet werden.
1.) Als Matrixformel
Die folgende Matrixformel berechnet die Arbeitstage zwischen (incl.) einem Anfangsdatum und Enddatum. Sie rechnet dabei angebbare Wochenendtage aus der ermittelten Tagesanzahl heraus.
Die Formel erwartet in Zelle A1 das Anfangsdatum, in Zelle B1 das Enddatum und in Zelle C1 eine Ziffernfolge die angibt, welche Wochentage als Wochenendtage herausgerechnet werden sollen. Eine Liste der freien Tage wird in dieser Formel nicht berücksichtigt (4. Argument der Funktion NETTOARBEITSTAGE.INTL()).
Anfangsdatum ist ein gültiges Excel-Datum.
Enddatum ist ein gültiges Excel-Datum.
Zeichenfolge ist eine Ziffernfolge aus den Ziffern 1 bis 7 (Mo = 1, Di = 2, ..., So = 7). Die Ziffernfolge 17 kennzeichnet den Montag(1) und den Sonntag(7) als Wochenendtag. Diese beiden Wochentage werden bei der Zählung der Tage zwischen dem Anfangsdatum und dem Enddatum nicht berücksichtigt.
Die Matrixformel lautet:
| =(ABS(B1-A1)+1)-SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(A1&":"&B1));2)=MTRANS(WERT(TEIL(C1;ZEILE(INDIREKT("1:"&LÄNGE(C1)));1))))) |
Schließen Sie die Eingabe der Matrixformel mit der Tastenkombination Strg+Umsch+Enter ab. Excel umschließt die Formel mit geschweiften Klammern und kennzeichnet sie damit als Matrixformel.
Beispiel:
A1: 23.02.2011
B1: 01.04.2011
C1 (Mo =1, So = 7): 17
Ergebnis: 28; es befinden sich zwischen (incl.) dem Anfangsdatum und dem Enddatum 28 Arbeitstage.
Geben Sie den relevanten Zellen einen Namen, ist die folgende Matrixformel universell, ohne Adressanpassung, einsetzbar:
| =(ABS(Enddatum-Anfangsdatum)+1)-SUMMENPRODUKT(--(WOCHENTAG(ZEILE(INDIREKT(Anfangsdatum&":"&Enddatum));2)=MTRANS(WERT(TEIL(Zeichenfolge;ZEILE(INDIREKT("1:"&LÄNGE(Zeichenfolge)));1))))) |
Schließen Sie die Eingabe der Matrixformel mit der Tastenkombination Strg+Umsch+Enter ab. Excel umschließt die Formel mit geschweiften Klammern und kennzeichnet sie damit als Matrixformel.
2.) Als benutzerdefinierte Funktion
Die folgende benutzerdefinierte Funktion ist eine vereinfachte Adaption der Tabellenblattfunktion NETTOARBEITSTAGE.INTL() aus Excel 2010. Sie kann in allen Versionen ab Excel 2003 eingesetzt werden:
Option Base 1
Function NETTOARBEITSTAGE_INTL(Ausgangsdatum As Long, _
Enddatum As Long, _
Optional Zeichenfolge As String = "0000011", _
Optional Freie_Tage As Range) As Variant
'********************************************************************************
'*
'* Copyright 2011 by Frank Arendt-Theilen, Hameln
'*
'* Diese Funktion ist eine vereinfachte Adaption der Tabellenblattfunktion
'* NETTOARBEITSTAGE.INTL aus Excel 2010. Sie kann in allen Versionen ab Excel 2003
'* eingesetzt werden.
'*
'* Syntax: =NETTOARBEITSTAGE_INTL(Ausgangsdatum;Enddatum[;Zeichenfolge][;Freie_Tage])
'*
'********************************************************************************
Dim lngI As Long
Dim arrZeichenfolge(7)
Dim rngCell As Range
'das Ausgangsdatum muss kleiner als Enddatum sein, sonst Fehlerwert #ZAHL!
If Enddatum < Ausgangsdatum Then
NETTOARBEITSTAGE_INTL = CVErr(xlErrNum)
Exit Function
End If
'die Länge von Zeichenfolge muss sieben Zeichen betragen, sonst Fehlerwert #WERT!
If Len(Zeichenfolge) <> 7 Then
NETTOARBEITSTAGE_INTL = CVErr(xlErrValue)
Exit Function
End If
For lngI = 1 To 7
'Zeichenfolge darf nur 0 und 1 enthalten, sonst Fehlerwert #WERT!
If Mid(Zeichenfolge, lngI, 1) Like "[0-1]" Then
arrZeichenfolge(lngI) = Mid(Zeichenfolge, lngI, 1) * lngI
Else
NETTOARBEITSTAGE_INTL = CVErr(xlErrValue)
Exit Function
End If
Next
For lngI = Ausgangsdatum To Enddatum
If Application.WorksheetFunction.Weekday(lngI, 2) <> _
arrZeichenfolge(Application.WorksheetFunction.Weekday(lngI, 2)) Then
NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL + 1
Else
'falls das Datum in der Zeichenfolge als ein freier Tag markiert
'und gleichzeitig in der Liste der freien Tage enthalten ist
If Not Freie_Tage Is Nothing Then
For Each rngCell In Freie_Tage.Cells
If rngCell.Value = lngI Then
NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL + 1
End If
Next
End If
End If
If Not Freie_Tage Is Nothing Then
For Each rngCell In Freie_Tage.Cells
If rngCell.Value = lngI Then
NETTOARBEITSTAGE_INTL = NETTOARBEITSTAGE_INTL - 1
End If
Next
End If
Next
End Function |
Anwendung
Syntax: =NETTOARBEITSTAGE_INTL(Ausgangsdatum;Enddatum[;Zeichenfolge][;Freie_Tage])
Ausgangswert (erforderlich). Ausgangsdatum ist ein gültiges Datum in Excel. Dieses Datum muss vor dem Enddatum liegen, sonst gibt die Funktion den Fehlerwert #ZAHL! zurück.
Enddatum (erforderlich). Enddatum ist ein gültiges Datum in Excel. Dieses Datum muss nach dem Anfangsdatum liegen, sonst gibt die Funktion den Fehlerwert #ZAHL! zurück.
Zeichenfolge (optional). Zeichenfolge ist eine Folge von sieben Zeichen, entweder eine 0 (Null) für einen Arbeitstag oder eine 1 (Eins) für einen freien Tag, Bsp: 0001001. Das linke Zeichen steht für den Montag, das zweite für den Dienstag, ..., das siebente für den Sonntag. In dem Beispiel bedeutet die Zeichenfolge, das am Donnerstag und Sonntag ein freier Tag ist, alle anderen Tage sind Arbeitstage.
Wird die Zeichenfolge nicht angegeben, werden der Samstag und Sonntag automatisch als Wochenendtage berücksichtigt.
Hat die Zeichenfolge nicht genau sieben Zeichen oder andere Zeichen als 0 und 1, gibt die Funktion den Fehlerwert #WERT! zurück.
Freie_Tage (optional). Freie_Tage ist der Bereich einer Liste mit Datumswerten für freie Tage.
Wichtig: Die Funktion benötigt im Modulkopf die Option: Option Base 1
Beispiel:
A1 Ausgangsdatum: 01.01.2011
A2 Enddatum: 10.01.2011
A3 Zeichenfolge: '0001001 --> das Apostroph sorgt dafür, das die führenden Nullen erhalten bleiben
B1 Datum in Liste der freien Tage: 05.01.2011
B2 Datum in Liste der freien Tage: 06.01.2011
Formel: =NETTOERBEITSTAGE_INTL(A1;A2;A3;$B$1:$B$2)
Ergebnis: 6
|
