Ersten/Letzten Arbeitstag im Monat berechnen
Der erste bzw. letzte Arbeitstag muss ja nicht unbedingt mit dem Monatsersten bzw. dem Ultimo zusammen fallen. Paradebeispiel ist gewiss der 1. Mai, der bei uns in Deutschland immer ein Feiertag ist. Und es ist gar nicht einmal so selten, dass ein Wochenende auf den ersten oder letzten Tag des Monats fällt. Daher muss ein Weg gefunden werden, diesen Tag für jeden beliebigen Monat zu berechnen.
Was Sie brauchen
Wegen der eben schon angesprochenen Feiertage brauchen Sie in irgendeiner Form eine Übersicht, eine Liste der Feiertage des entsprechenden Jahres. Und damit Sie die hier aufgeführten Beispiele besser nachvollziehen können, stellen wir Ihnen in dieser Mappe im Arbeitsblatt Feiertage_1 eine solche Liste zur Verfügung.
Es handelt sich in dieser Aufstellung um eine Intelligente Tabelle bzw. Liste. Das hat verschiedene Vorteile. Insbesondere, dass Sie einfach regionale Feiertage, Brückentage oder Ferientage anfügen können. Eine kalendarische Ordnung braucht dabei nicht beachtet zu werden. – Diese Tabelle den Bereichsnamen: tbl_Feiertage. Der wird bei einer Veränderung der Zeilenzahl automatisch angepasst, was recht praktisch ist.
Falls Ihre Excel-Version keine Listen unterstützt, dann passen Sie die Daten nach Ihren Wünschen an und ändern dann auch die Eckdaten des Bereichsnamens, damit alles korrekt ist. In dem Fall sollte zweckmäßigerweise nur der Bereich mit den kalendarischen Daten den Namen erhalten.
Basics
Für alle Excel-Versionen, ohne VBA
Auf der Basis eines beliebigen Datums soll der erste Arbeitstag berechnet werden. Dazu brauchen Sie zwei Excel-Funktionen und eine Liste der Feiertage. Im Arbeitsblatt Feiertage_1 der Muster-Mappe sind die Feiertage des jeweils aktuellen Jahres vermerkt, das Jahr kann jedoch per Hand (durch ersetzen der Formel) angepasst werden. Diese Tabelle umfasst die Feiertage exakt eines Jahres. Sie kann beliebig ergänzt oder verändert werden, es muss keine kalendarische Reihenfolge eingehalten werden. – Wird zur Berechnung ein Datum aus einem Jahr verwendet, welches nicht in Zelle A1 steht, dann ist eine falsche Berechnung des ersten bzw. letzten Arbeitstages möglich. Darum wird diese Liste vorzugsweise für Daten des aktuellen Jahres verwendet.
Im Arbeitsblatt Feiertage_2 sind alle Feiertage des Jahres 2000 bis 2014 aufgeführt. Auch dieses ist eine Intelligente Tabelle, allerdings umfasst die eigentliche Liste ausschließlich die Feiertags-Daten, nicht jedoch die Spalte 1 mit den Namen der Feiertage. Zur Trennung dient hier Spalte B, diese ist allerdings ausgeblendet. Der Liste, den Daten also, wurde der Bereichsname Feiertage2 vergeben. Auch hier gilt die Einschränkung, dass das entsprechende Jahr in der Tabelle aufgeführt sein muss, sonst kann es auch hier zu Fehlern kommen. – Wollen Sie nur für ein einzelnes oder wenige Jahre in die Zeit vor dem ersten Jahr die Feiertage berechnen, dann können Sie die Spalten vor Spalte C einfügen, den Daten-Bereich der ehemaligen Spalte C ab Zeile 1 markieren und anschließend das Ausfüllkästchen bis zur Spalte C ziehen. Die Daten werden automatisch berechnet. Gleiches gilt, wenn Sie die Daten über 2014 hinweg nach rechts auffüllen wollen:
Weitere Jahre hinzufügen
Weiterhin gibt es zwei UDFs, zwei selbst erstellte Funktionen. Den Quellcode finden Sie im Modul1 innerhalb des VBA-Editors. Da hier das Datum direkt an die Funktion übergeben wird, bedarf es keines Bereichs mit aufgelisteten Feiertagen. Diese werden in dem Modul durch weitere Funktionen berechnet. Dadurch sind Fehlergebnisse ausgeschlossen.
Im ersten Blatt (Tabelle1) steht in A1 ein beliebiges Datum des Monats, von dem der erste bzw. letzte Arbeitstag berechnet werden soll. Weiterhin finden Sie hier alle Beispiele zur unterschiedlichen Berechnung.
Erster Arbeitstag
Es sei noch einmal erwähnt, dass der erste Arbeitstag des Monats nicht immer der Monatserste ist. Darum zeigen wir Ihnen hier ja auch verschiedene Wege auf, den ersten Werktag des Monats aus der Zelle A1 zu berechnen.
Nur das aktuelle oder ein definiertes Jahr
Die erste Berechnung finden Sie in A4. Die Formel ist auf den ersten Blick nicht wirklich kurz, aber sie umfasst bekannte Elemente:
=ARBEITSTAG(DATUM(JAHR($A$1); MONAT($A$1); 0); 1; Tabelle1[Datum])
Die Funktion ARBEITSTAG() gibt es schon seit Excel 2007, sie ist also schon seit einigen Jahren verfügbar. Prinzipiell wird das Datum zurück gegeben, welches eine definierte Zahl von Tagen nach dem Ausgangsdatum liegt. Dabei werden nur Arbeitstage berechnet und optional auch benannte Feiertage. Mehr dazu finden Sie hier im Blog oder natürlich auch im Internet.
Als Ausgangsdatum wird per DATUM()-Funktion der Monatsletzte des Vormonats berechnet, anschließend innerhalb der ARBEITSTAG-Funktion 1 Tag dazu gerechnet. Damit wird indirekt der Erste des betroffenen Monats definiert. Probieren Sie gerne einmal aus, direkt den Ersten als Argument der DATUM-Funktion zu verwenden und 0 Tage dazu zu rechnen. Es geht daneben (mit Sicherheit im Januar 2016 gut zu erkennen). Schlussendlich wird als letztes Argument der Bereich vergeben, wo in der Liste mit dem Bereichsnamen Tabelle1, Spalte [Datum] alle Feiertage aufgelistet sind.
Die Rückgabe der Formel ist eine serielle Zahl. Die jeweilige Zelle sollte entsprechend als Datum im beliebigen Format dargestellt werden.
Speziell für das aktuelle Jahr sind in der Muster-Tabelle in Spalte E zwei alternative Formeln dargestellt. Die Systematik ist die gleiche, nur die Adressierung der Feiertage ist eine andere. Bei Größenveränderung der Liste ist eine Anpassung erforderlich.
Beliebiges Jahr eines definierten Bereichs
Im Arbeitsblatt Feiertage_2 sind alle Feiertage der Jahre 2000 bis 2014 in den einzelnen Zellen berechnet. Diese Liste/Intelligente Tabelle kann als Grundlage für die Berechnung eines Datums aus dem Zeitraum dienen. Weiter oben wurde ja schon beschrieben, wie Sie den Bereich der Jahre nach unten bzw. oben anpassen können.
Die verwendete Formel ist im Prinzip gleich aufgebaut wie die direkt hierüber beschriebene Formel. Der hauptsächliche Unterschied ist, dass ein klar definierter Bereichsname, hier: Feiertage2 verwendet wird. Auch hier gilt, dass der Bereichsname automatisch angepasst wird, wenn die Daten als Tabelle/Intelligente Liste verändert werden. Sollte eine Liste nicht möglich sein, dann muss der Bereichsname per Hand angepasst werden, wenn die die Koordinaten der Daten ändern. Die Formel finden Sie in der Mustermappe, Blatt Tabelle1, Zelle A8:A9.
Beliebiges Jahr berechnen
Es ist zwar möglich, für jedes Jahr und jeden auszuwertenden Tag einzeln per Formel zu berechnen, aber das wäre eine derartig unübersichtliche und fehleranfällige Formel, dass wir hier gar nicht erst den Versuch unternehmen, das in dieser Form zu realisieren. Wir ziehen in solchen Fällen eine Makro-Lösung vor, eine benutzerdefinierte Funktion (UDF). Genauer gesagt sind es zwei UDFs, für den ersten Arbeitstag: ErsterArbeitsTag() und den letzten Werktag des entsprechenden Monats: LetzterArbeitsTag(). Sie rufen diese Funktion auf und schreiben das Argument, ein beliebiges Datum des zu berechnenden Monats, in die Klammern. Die Funktion wird dann den entsprechenden Tag berechnen.
In der Muster-Mappe ist der Code für die Berechnung bereits eingebunden. Der Große Vorteil der UDF: Sie kann in praktisch allen, auch frühen Excel-Versionen verwendet werden. Die Version muss nur VBA unterstützen. Auch hier finden Sie ein Berechnungs-Beispiel in unserer Mappe.
Einige Worte zum Code: Der 3.10. ist als Feiertag fest verankert, falls Sie die Funktion für sehr frühe kalendarische Daten (vor 1990) verwenden, dann müssen Sie den Code entsprechend anpassen. – Der Code muss entweder in jede Mappe eingebunden werden, wo die Funktion verwendet wird oder Sie erstellen aus den UDFs ein Add-In, welches Sie dann aktivieren müssen. Für das einbinden des Codes können Sie beispielsweise hier im Blog einiges nachlesen.
Letzter Arbeitstag
Prinzipiell gilt äquivalent alles, was oben gesagt worden ist. Nur dass der letzte Arbeitstag des entsprechenden Monats berechnet wird. Die reine Excel-Formel allerdings ist etwas anders als zur Berechnung des ersten Arbeitstages:
=ARBEITSTAG(DATUM(JAHR($A$1); MONAT($A$1)+1; 1); -1; Tabelle1[Datum])
Hier gilt gleichermaßen, dass diese Form für die Verwendung einer Intelligenten Tabelle in den beiden Arbeitsblättern mit den Feiertagen gilt. Eventuell muss der Bereich (letztes Argument der Formel) als direkte Bereichsangabe in A1-Form angepasst werden. – Bei Verwendung der UDF brauchen Sie außer den oben genannten Bedingungen nichts zu beachten.
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 1,00 € freuen … (← Klick mich!)