Nur Sa, So und Feiertage in eine Liste eintragen
Wohl kaum jemand reißt sich um einen Dienst an Wochenenden oder Feiertagen. Aber in manchen Berufen ist es einfach erforderlich. Und um das einigermaßen gerecht zu verteilen, bietet sich eine Liste an, in welcher nur diese Tage aufgeführt sind. In A1 soll entweder das Jahr als Zahl oder das Datum des 1.Januar des entsprechenden Jahres stehen, formatiert als JJJJ. Beginnend in A3 der 1.1. (es ist ja in jedem Fall ein Feiertag) und darunter jeweils die Wochenenden und die weiteren Feiertage für das restliche Jahr.
Das Ganze geht natürlich von Hand, also mit reinen Excel-Bordmitteln ohne Programmierung. Unser Vorschlag dazu sieht so aus:
- Tragen Sie in A1 die Jahreszahl oder das Datum ein.
- In A3 kommt auf jeden Fall das Datum des 1. Januar.
- Falls der Jahresbeginn auf einen Samstag fällt, gehört in A4 der 2. Januar, weil es ja ein Sonntag ist. Ansonsten kommt in A4 der kommende Samstag.
- Und in A5 kommt entweder der Sonntag nach dem Jahresbeginn oder erst einmal gar nichts.
Jetzt stehen entweder in A3:A4 oder in A4:A5 der erste Samstag und Sonntag des Jahres. In jedem Fall gehört in die nächste freie Zelle (z.B. F5) die Formel mit einem Verweis auf den darüber stehenden Samstag, beispielsweise =F4+7. In die folgende Zeile kommt dann der Verweis auf die darüber liegende Zeile, also beispielsweise =F5+7. Diese letzte Zeile können Sie nun so weit nach unten ziehen, bis das Jahresende erreicht ist. Anschließend tragen Sie von Hand alle Feiertage des Jahres ein oder lassen Sie sich diese berechnen, wie hier als Muster vorgestellt. Zum Schluss sortieren Sie den Bereich mit den kalendarischen Daten, damit die Feiertage an die korrekte Position kommen. Das hört sich gewiss schwieriger an, als es ist. Aber es bedeutet Aufwand.
Prinzipiell komplett anders läuft das, wenn Sie das ganze per VBA erledigen lassen. Sie tragen auch in A1 das Datum oder das Jahr ein und dann rufen Sie (beispielsweise) in dieser Datei das einzig vorhandene Makro NurWochenendeUndFeiertage beispielsweise mit auf. AltF8 auf. Alternativ haben Sie es selbst in Ihre Datei eingefügt, der Code steht dann im Modul DieseArbeitsmappe. Ruck zuck wird nach dem Aufruf im aktuellen Blatt ab Zelle A3 jedes Wochenende eingetragen und auch gleich in korrekter zeitlicher Reihenfolge die Feiertage des Jahres.
Einige Anmerkungen sind hier sehr wichtig. Eine Schleife ist selten die schnellste und damit auch so gut wie nie die beste Möglichkeit, hier aber ist sie der Einfachheit und der Transparenz des Codes geschuldet. Und bei diesen wenigen Durchläufen, es sind ja höchstens 366 Tage, ist die etwas geringere Geschwindigkeit nicht spürbar. Sehen Sie selber den Code:
Option Explicit Sub NurWochenendeUndFeiertage() Dim fDay As Date, lDay As Date Dim Jahr As Integer, Tag As Date Dim Zeile As Integer On Error GoTo ErrorHandler Application.ScreenUpdating = False With ActiveSheet If IsDate(Cells(1, 1)) Then Jahr = Year(Cells(1, 1)) Else Jahr = Cells(1, 1) End If fDay = DateSerial(Jahr, 1, 1) lDay = DateSerial(Jahr, 12, 31) Zeile = 3 For Tag = fDay To lDay If Weekday(Tag, 2) > 5 Or IstFeiertag(Tag) Then Cells(Zeile, 1) = CDate(Tag) ' If IstFeiertag(Tag) Then ' With Cells(Zeile, 1).Font ' .Color = -11489280 ' .Bold = True ' End With ' End If Zeile = Zeile + 1 End If Next Tag End With ErrorHandler: Application.ScreenUpdating = True End Sub
Und einige weitere kurze Erklärungen dazu: Die Bildschirmaktualisierung wird aus Gründen der Geschwindigkeit und zur Vermeidung eines flimmernden Bildschirms ausgeschaltet. Danach wird der Variablen Jahr die Jahreszahl zugewiesen. Nachdem der Zeitbereich mit fDay und lDay (firstDay und lastDay) fest gelegt worden ist, wird für jeden Tag des Jahres geprüft, ob es ein Wochenende oder ein Feiertag ist. Wenn dieses der Fall ist, wird der Wert in die entsprechende Zeile geschrieben.
Der folgende Block ist erst einmal auskommentiert. Wenn Sie diese Zeilen aktivieren, dann wird automatisch ein Feiertag in grüner Schrift und der Formatierung „Fett” dargestellt. – Die Funktion IstFeiertag() ist kein Teil des Excel sondern eine selbst definierte Funktion, welche im Modul1 abgelegt ist. Hier werden Sie bei Bedarf den einen oder anderen Feiertag aktivieren oder deaktivieren. Der Code sollte so transparent sein, dass auch bei geringer Grundkenntnis die Änderungen vorgenommen werden können. Falls Sie nicht auf die hier angebotene Datei zurück greifen und den Code selbst einfügen wollen, dann werden Sie in diesem Beitrag fündig.
Für beide Versionen ist es nun möglich, mittels bedingter Formatierung die Feiertage nach eigenem Geschmack hervorzuheben. Das hat den Vorteil, dass Sie einerseits die Gestaltung selbst bestimmen können und andrerseits genügt es, in A1 das Jahr zu ändern, und automatisch werden die Feiertage korrekt markiert.
Etwas anders läuft das, wenn Sie die Formatierung durch den Code festlegen. Dann ist es durchaus wahrscheinlich, dass die bisherige Formatierung in der Zelle verbleibt und nun auch normale Wochenenden farblich hervorgehoben werden. Der Vorteil der per Code erzeugten Formatierung liegt darin, dass kein unbedarfter User „versehentlich” Änderungen daran vornimmt. Manche Leute haben ja einen farblichen Geschmack, dass die Kollegen Augenkrebs davon bekommen. 😈
Bei einem neuen Jahr wäre es bei dieser Form der Formatierung am einfachsten, die gesamten Zeilen ab dem 1. Januar zu löschen und dann per Makro neu aufbauen zu lassen. Oder Sie bauen das (auch wegen der Schaltjahre) fest in die Routine (Sub) mit ein.
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. 2,00 € freuen … (← Klick mich!)