Nur Wochenenden und Feiertage

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.

▲ nach oben …

 

Dieser Beitrag wurde unter Datum und Zeit, Mit VBA/Makro, Musterlösungen, Ohne Makro/VBA, Tabelle und Zelle, Verschiedenes abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.