Autor: Klaus-Dieter Oppermann  --- Aus Excel Standard - Gruppe: Häufige Fragen

Arbeitstage und Enddatum berechnen

Autor: Klaus-Dieter Oppermann - Erstellt: 2013-12-13      - Letzte Revision: ?

Anzahl von Arbeitstagen berechnen

- Grundsätzliches

Seit Excel 2007 gehören zwei interessante Funktionen zum Standardfunktionsumfang. Es sind die Funktionen =ARBEITSTAG und =NETTOARBEITSTAGE. In den älteren Excelversionen musste das AddIn Analysefunktionen aktiviert sein, damit diese Funktionen zur Verfügung standen.

Das war mit dem Nachteil verbunden, dass diese Funktionen, wenn die Arbeitsmappe weiter gegeben wurde, auf dem anderen Rechner nicht zur Verfügung standen. Zumindest dann nicht, wenn das Add-In dort nicht auch installiert war.

- Die Funktionen

Die Funktion Arbeitstag liefert das Datum, das sich aus einer in der Formel einzugebenden Anzahl von Arbeitstagen, ausgehend von einem Startdatum ergibt.
Die Funktion Nettoarbeitstag liefert die Anzahl der Arbeitstage, die zwischen zwei in der Formel festzulegenden Tagen liegen.
Das möchte ich mit dem dem folgenden Tabellenausschnitt verdeutlichen.

Beispiele:

 ABCDEFG
1Datum      
2Mo. 22.12.2008 1 Formel Arbeitstag:Mo. 05.01.2009 
3Di. 23.12.200812    
4Mi. 24.12.200823 Formel Nettoarbeitstage: 11 
5Do. 25.12.200834    
6Fr. 26.12.200845    
7Sa. 27.12.2008      
8So. 28.12.2008      
9Mo. 29.12.200856    
10Di. 30.12.200867    
11Mi. 31.12.200878    
12Do. 01.01.200989    
13Fr. 02.01.2009910    
14Sa. 03.01.2009      
15So. 04.01.2009      
16Mo. 05.01.20091011    
17Di. 06.01.2009      

Formeln der Tabelle
ZelleFormel
F2=ARBEITSTAG(A2;10)
F4=NETTOARBEITSTAGE(A2;A16)


Welche Tage in die Berechnung einbezogen werden, habe ich rechts neben dem Datum kenntlich gemacht. Dabei fällt auf, dass beide Formeln die Wochenenden nicht berücksichtigen, da das üblicherweise keine Arbeitstage sind. Des weiteren zählt die Funktion Arbeitstag den Starttermin nicht mit.


- Berücksichtigen von Feiertagen

Nun hatten wir festgestellt, das die Funktionen die Wochenenden nicht mit berechnen. Wie wir aber im Beispiel oben sehen, werden die Feiertage sehr wohl als Arbeitstag angesehen. Das kann nicht in Ordnung sein. Deshalb haben beide Funktionen die Möglichkeit diese heraus zu rechnen.

Die Syntax beider Funktionen hat zu diesem Zweck die (optionale) Möglichkeit, die im Zeitraum liegenden freien Tage mit einzugeben:

ARBEITSTAG(Ausgangsdatum;Tage;Freie_Tage)
NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;Freie_Tage)

Wer nun aber glaubt, man müsse die Zahl der im Berechnungszeitraum liegenden Feiertage kennen, und nach diesem Muster: =ARBEITSTAG(A2;10;3) eintragen, wird erkennen, dass das der falsche Ansatz ist. Die Tage werden (so) nicht abgezogen.
Man kann die drei, im oberen Beispiel nicht berücksichtigten Feiertage, in die Formeln einfügen. Das sieht dann so aus:

 EF
2Formel Arbeitstag08.01.08
3  
4Nettoarbeitstag5

Formeln der Tabelle
ZelleFormel
F2=ARBEITSTAG(A2;8;{"25.12.07"."26.12.07"."01.01.08"})
F4=NETTOARBEITSTAGE(A2;A16;{"25.12.07"."26.12.07"."01.01.08"})


Hinweis: Eine Matrix wird innerhalb einer Funktion mit geschweiften Klammern eingegrenzt, diese muss ich selber schreiben.
(Bitte verwechseln Sie dies nicht mit Matrixformeln die geschweifte Klammern um die Formeln haben)

- Berücksichtigen von Feiertagen - über Liste festlegen

Man kann sich vorstellen, dass die oben beschriebene Methode, Feiertage und sonstige freie Tage aus der Berechnung auszunehmen, nicht sehr komfortabel ist. Muß doch jeder Tag einzeln in die Formel eingetragen werden. Excel wäre nicht, was es ist, wenn es da nicht noch einen anderen Weg gäbe.

Wir legen eine Liste nach dem Muster unten an, die Termine können als feste Werte eingetragen werden, aber auch wie in meinem Beispiel, aus Formeln berechnet werden. Dazu habe ich eine Formel von unserer Partnerseite Excel-Formeln verwendet, die den Ostersonntag, und damit auch die beweglichen Feiertage, die einen festen Abstand zum Ostersonntag haben, berechnet. Hinter der Osterformel müssen die Plus- und Minuswerte aus der Spalte Differenz angefügt werden. Die Feiertage mit einem festen Termin werden nach der zweiten Formel berechnet, wobei sich hier nur die Jahreszahl verändert.
Hinweis: Beide Formeln sind als Beispiel zu betrachten, man findet im Internet eine Fülle solcher Formeln.

- Die Osterformel: =RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6

Der Bereich in der Spalte (A) wird als benannter Bereich definiert. Ich habe den Namen freieTage gewählt.

 ABC
1 2013   
2    
3 DatumFeiertag Differenz
4 01.01.2013Neujahr 
5 11.02.2013Rosenmontag-48
6 29.03.2013 Karfreitag -2
7 31.03.2013Ostersonntag 
8 01.04.2013Ostermontag +1
9 01.05.2013Tag der Arbeit 
10 09.05.2013Himmelfahrt +39
11 19.05.2013Pfingstsonntag +49
12 20.05.2013Pfingstmontag +50
13 30.05.2013Fronleichnam +60
14 03.10.2013Tag der dt. Einheit 
15 01.11.2013Allerheiligen  
16 24.12.2013Heiliger Abend  
17 25.12.20131. Weihnachtstag  
18 26.12.20132. Weihnachtstag  
19 31.12.2013 Silvester 
20 01.01.2014Neujahr  

 
Formeln der Tabelle
ZelleFormel
A4=DATWERT("01.01."&A1)
A5=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6-48
A6=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6-2
A7=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6
A8=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6+1
A9=DATWERT("01.05."&A1)
A10=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6+39
A11=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6+49
A12=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6+50
A13=RUNDEN((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6+60
A14=DATWERT("03.10."&A1)
A15=DATWERT("01.11."&A1)
A16=DATWERT("24.12."&A1)
A17=DATWERT("25.12."&A1)
A18=DATWERT("26.12."&A1)
A19=DATWERT("31.12."&A1)
A20=DATWERT("01.01."&A1+1)


So stellen sich nun die Formeln und die Ergebnisse dar:
 EF
2Formel Arbeitstag08.01.08
3  
4Nettoarbeitstage5

Formeln der Tabelle
ZelleFormel
F2=ARBEITSTAG(A2;8;freieTage)
F4=NETTOARBEITSTAGE(A2;A16;freieTage)


Der Vorteil und die daraus resultierenden Möglichkeiten für Arbeitszeitberechnungen aller Art dürften auf der Hand liegen. Statt mühsam jeden einzelnen freien Tag in die Formel einzufügen, kann das so auf einen Rutsch geschehen. Die Liste ist jeder Zeit veränderbar, alle Formeln reagieren darauf, ohne das man sie einzeln anpassen müsste. Das beschränkt sich nicht nur auf Feiertage, auch Urlaubstage, Gleittage usw. können berücksichtigt werden.

Weitere Artikel der Gruppe: Häufige Fragen Aus Excel Standard
Nach oben
ToDo
Google Werbung