Arbeitszeiterfassung mit Excel / 6 Feiertagsliste für alle Bundesländer

Das Zeitkonto wäre nicht sehr funktionell, wenn es nicht automatisch die Feiertage im Kalender berücksichtigen würde. Ein Feiertagsrechner stellt im Tabellenblatt Feiertage die Liste der Feiertage für das Auswertungsjahr bereit. Die neu hinzugekommenen Feiertage wie der Internationale Frauentag für Berlin und der Reformationstag für die nördlichen Bundesländer werden automatisch berücksichtigt (s. Abb. 4).

Abb. 4: Nach der Auswahl des Bundeslands werden die entsprechenden Feiertage angezeigt und berücksichtigt.

In der Überschrift ist der Text "Feiertage" mit dem Auswertungsjahr verknüpft:

="Feiertage" & EJAHR

Ihr gewünschtes Bundesland wählen Sie über die Optionsfelder aus. Mit der Auswahl wird die Nummer des Bundeslandes in die mit allen Optionsfeldern verknüpfte Zelle E8 übertragen. Diese Nummer steuert alle Berechnungen, z. B. die Überschrift der Feiertagsliste, in der die Funktion BEREICH.VERSCHIEBEN() den Namen des Bundeslandes ermittelt:

I8: ="Feiertage" & JAHR&" "&BEREICH.VERSCHIEBEN($F$9;E8-1;0)

Berechnung der Feiertage

Die Datumswerte der Feiertage werden mithilfe einer Zahlenkette berechnet, die zu jedem Bundesland in der (ausgeblendeten) Spalte G angelegt ist. Der Bereichsname FTINDEX verweist auf die Zelle, in der die aktuelle Zahlenreihe des Bundeslandes abgebildet ist. Auch hier sorgt die Funktion BEREICH.VERSCHIEBEN() für die Verknüpfung mit dem gewählten Bundesland:

G8: =BEREICH.VERSCHIEBEN($G$9;E8-1;0)

Die einzelnen Feiertage werden über Formeln berechnet. Mit der Funktion DATUM() entsteht ein vom Erfassungsjahr abhängiger Datumswert, der mit der Feiertagsziffer der Zahlenkette multipliziert wird. Damit wird je nach Wert (0 oder 1) das Datum erzeugt bzw. unterdrückt.

Der einzig wichtige bewegliche Feiertag im Jahr ist der Ostersonntag, dessen Datum sich über die Gaußsche Osterformel berechnen lässt. Nach dieser Formel fällt Ostern auf den ersten Sonntag nach dem Frühlingsvollmond (vgl. http://de.wikipedia.org, Stichwort "Osterformel"). Die Excel-Formel dafür sieht so aus:

=DATUM(EJAHR;3;28)+REST(24-REST(EJAHR;19)*10,63;29)-REST(KÜRZEN(EJAHR*5/4)+REST(24-REST(EJAHR;19)*10,63;29)+1;7)

Von diesem Datum können die davon abhängigen beweglichen Feiertage wie Pfingsten und Himmelfahrt abgeleitet werden.

Die Liste enthält auch 3 nicht-offizielle Feiertage (Rosenmontag, Faschingsdienstag, Heiligabend), die mit einem Ankreuzkästchen aktivierbar sind (s. Abb. 5). Die Formel prüft den Status der verknüpften Zelle ab und trägt das Datum ein, wenn das Kästchen angekreuzt ist:

I27: =WENN(H27;I12-48;)

Abb. 5: Drei nicht-offizielle Feiertage können bei Bedarf gewählt werden.

Hinweis

Feiertagsliste ergänzen

Die Feiertagsliste kann nach unten durch weitere Einträge, etwa für betriebsfreie Tage, ergänzt werden. Achten Sie aber darauf, dass sich die Datumswerte immer auf das Erfassungsjahr beziehen müssen. Verwenden Sie dazu im Spalte I immer die Funktion DATUM(). Ist z. B. der 21. Mai 2019 ein betriebsfreier Tag, lautet der Eintrag: =DATUM(EJAHR;5;21)

Bereichsnamen in der Feiertagsliste

Um die Feiertagsliste in den Zeitkontenblättern nutzen zu können, wurden Bereichsnamen erstellt, die sich über den Namens-Manager (Register Formeln) prüfen und anpassen lassen (s. Abb. 6).

Abb. 6: Die Bereichsnamen können über den Namens-Manager eingesehen werden.

Anzahl Feiertage ermitteln

Die Anzahl der Feiertage für das gewählte Bundesland ermittelt eine Formel mit der Funktion ZÄHLENWENN(). Im ersten Argument wird die einspaltige Liste der Datumswerte angegeben, das zweite Argument enthält das Filterkriterium:

J35: =ZÄHLENWENN(FLISTE_D;">0")

Feiertage eintragen

Die Spalte Kennung im Zeitkontenblatt wird über eine Formel so präpariert, dass automatisch alle auf die Datumswerte fallenden Feiertage angezeigt werden (s. Abb. 7):

E13: =WENN(ISTNV(SVERWEIS(D13;Feiertage;2;0));“ “;SVERWEIS(D13;Feiertage;2;0))

Abb. 7: Mit SVERWEIS werden die Feiertage eingetragen.

Zur Erklärung: Die Funktion SVERWEIS findet in der Liste der Feiertage den Datumswert in der Nachbarzelle und gibt den Text aus der Spalte aus, die im dritten Argument bezeichnet ist (Spalte 2). Das letzte Argument (0) sorgt dafür, dass der Wert exakt stimmen muss und nicht der nächstkleinere Datumswert akzeptiert wird.

=SVERWEIS(Suchwert;Suchmatrix;Spaltenindex;Bereichsverweis)

Damit der Feiertag nur eingetragen wird, wenn auch zu finden ist, verpacken Sie die Funktion in eine WENN-Abfrage, die über ISTNV noch abfragt, ob der zu einem Fehler führen würde. In diesem Fall wird die Zelle leer bleiben (Argument “ “).

Hinweis

Alternative Funktion WENNFEHLER()

Sie können mit SVERWEIS auch die einfachere Variante mit der Funktion WENNFEHLER() einsetzen. Die Formel lautet dann: WENNFEHLER(SVERWEIS(D13;Feiertage;2;0);“ “)