Feiertage in Excel markieren — Liste, Regeln, Sollstunden

Warum Excel Feiertage nicht kennt

Excel rechnet mit Datumswerten, nicht mit Kalendern. Der Wochentag steckt im Datum selbst — WOCHENTAG liest ihn direkt ab. Ein Feiertag dagegen ist keine Eigenschaft des Datums, sondern eine Rechtsfolge aus einem Landesgesetz. Excel kann ihn nicht ableiten, weil er nicht in der Zahl steht.

Dazu kommt, dass die Frage „ist der 15. August ein Feiertag?“ in Deutschland keine eindeutige Antwort hat. Bundeseinheitlich sind nur neun Tage: Neujahr, Karfreitag, Ostermontag, der 1. Mai, Christi Himmelfahrt, Pfingstmontag, der Tag der Deutschen Einheit sowie der erste und zweite Weihnachtsfeiertag. Alles andere regeln die Länder — Fronleichnam gilt in einigen Bundesländern und in Teilen von Sachsen und Thüringen, Mariä Himmelfahrt im Saarland und in vielen bayerischen Gemeinden, das Augsburger Friedensfest nur im Stadtgebiet Augsburg.

Für einen Dienstplan heißt das: Die Feiertage müssen als Daten in der Datei stehen, nicht als Formel. Im Netz kursieren kompakte Formeln, die den Ostersonntag aus der Jahreszahl ableiten und daraus alle beweglichen Termine bauen. Sie sind elegant, aber sie lösen nur die halbe Aufgabe: Die Landesregelungen bleiben trotzdem Handarbeit, und niemand im Team kann eine solche Formel im Zweifel nachprüfen. Zwölf bis vierzehn Zeilen einmal im Jahr sind der ehrlichere Weg.

Schritt 1: Das Blatt „Feiertage“ anlegen

Die Liste gehört auf ein eigenes Blatt oder in den unteren Teil des Stammdatenblatts — nie in den Plan selbst, sonst wandert sie beim nächsten Monatswechsel mit. Vier Spalten reichen:

Die Spalte Gilt in wirkt überflüssig, solange der Betrieb einen Standort hat. Bei zwei Standorten in verschiedenen Bundesländern ist sie der Unterschied zwischen einer Datei und zwei Dateien: Die Regel filtert dann nicht auf die ganze Liste, sondern auf die Zeilen des jeweiligen Landes. Die Spalte Zuschlag greift den steuerfreien Rahmen des § 3b EStG auf — 125 Prozent des Grundlohns an gesetzlichen Feiertagen, 150 Prozent am 1. Mai sowie am 25. und 26. Dezember. Wie daraus eine Abrechnungsspalte wird, zeigt der Beitrag Zuschläge in Excel berechnen.

Wichtig für die Datumsspalte: Die Werte müssen echte Datumswerte sein, keine Texte. Ein sicherer Test ist die Rechtsbündigkeit — Excel schiebt Zahlen und Datumswerte nach rechts, Text bleibt links stehen. Linksbündige „Datumsangaben“ werden von ZÄHLENWENN nicht gefunden, und die Regel bleibt wirkungslos, ohne einen Fehler zu melden.

Schritt 2: Den Bereich benennen

Regeln der bedingten Formatierung lassen sich zwar auf andere Blätter beziehen, aber die Formel wird dadurch unleserlich und bricht, sobald jemand ein Blatt umbenennt. Ein benannter Bereich löst beides:

1. Die Datumsspalte markieren, etwa Feiertage!$A$2:$A$40 — bewusst mit Reserve nach unten.
2. Formeln → Namen definieren aufrufen.
3. Als Namen Feiertage eintragen, Bereich Arbeitsmappe lassen, bestätigen.

Eleganter ist die Variante über eine echte Tabelle: Den Listenbereich mit Start → Als Tabelle formatieren in eine Tabelle verwandeln, ihr im Reiter Tabellenentwurf den Namen tblFeiertage geben und anschließend einen Namen Feiertage definieren, der auf =tblFeiertage[Datum] verweist. Neue Zeilen sind dann automatisch Teil des Bereichs. Der Umweg über den Namen ist nötig, weil die bedingte Formatierung strukturierte Tabellenverweise in ihren Formeln nicht direkt annimmt — über einen definierten Namen funktionieren sie.

Schritt 3: Die beiden Regeln

Der Ablauf ist für beide Regeln gleich: Planbereich markieren — im Beispielaufbau C4:AG14, wobei Zeile 4 die Datumszeile ist — dann Start → Bedingte Formatierung → Neue Regel → Formel zur Ermittlung der zu formatierenden Zellen verwenden.

Zwei Details entscheiden über das Ergebnis. Erstens der gemischte Bezug C$4: Das Dollarzeichen fixiert die Zeile, die Spalte wandert mit. Nur so prüft jede Zelle einer Spalte dasselbe Datum, und die ganze Spalte färbt sich. Ohne das Dollarzeichen entsteht das bekannte Schachbrettmuster. Zweitens muss die markierte Zelle beim Anlegen der Regel die linke obere Zelle des Bereichs sein — Excel bezieht die Formel immer auf sie und rechnet für alle anderen Zellen relativ um.

Der Brückentag-Ausdruck in der letzten Zeile ist optional, aber im Frühjahr nützlich: Er markiert Werktage, die unmittelbar an einen Feiertag grenzen — also genau die Tage, an denen erfahrungsgemäß die meisten Urlaubsanträge eingehen.

Schritt 4: Die Reihenfolge der Regeln festlegen

Hier scheitern die meisten Aufbauten. Fällt ein Feiertag auf einen Samstag — 2027 etwa der 25. Dezember —, treffen beide Regeln zu. Excel arbeitet sie in der Reihenfolge der Regelliste von oben nach unten ab, und wenn beide dieselbe Eigenschaft setzen, gewinnt die obere.

Unter Start → Bedingte Formatierung → Regeln verwalten lässt sich die Reihenfolge mit den Pfeiltasten ändern. Die Feiertagsregel gehört nach oben. Zusätzlich sollte in ihrer Zeile das Häkchen Anhalten falls wahr gesetzt werden: Trifft sie zu, prüft Excel die darunter liegenden Regeln gar nicht mehr. Das ist auch dann sauber, wenn die Regeln unterschiedliche Formateigenschaften setzen — sonst mischt Excel graue Füllung und fette Schrift zu einem dritten Erscheinungsbild, das in der Legende nicht vorkommt.

Der zweite typische Fehler ist der zerfallene Anwendungsbereich. Wer Zeilen einfügt, kopiert oder Zellen verschiebt, zerlegt eine Regel oft in mehrere Fragmente mit unterschiedlichen Bereichen. Der Regelmanager zeigt das in der Spalte Wird angewendet auf: Stehen dort drei Einträge derselben Regel, sollten die Fragmente gelöscht und die Regel einmal sauber neu über den gesamten Bereich gelegt werden.

Schritt 5: Dieselbe Liste rechnet die Sollstunden

Die Feiertagsliste ist mehr wert als eine Farbe. Zwei Funktionen nehmen sie direkt als Argument entgegen:

Das dritte Argument ist der Wochenendcode: 1 steht für Samstag und Sonntag, 11 für „nur Sonntag frei“, 7 für „nur Freitag und Samstag frei“. Damit lässt sich dieselbe Formel für einen Bürobetrieb, einen Sechs-Tage-Betrieb im Einzelhandel und für abweichende Ruhetage verwenden, ohne die Feiertagsliste zu duplizieren. Für die Verrechnung mit den tatsächlich geleisteten Stunden gilt weiterhin das Zellformat [h]:mm — die Begründung steht im Beitrag Arbeitsstunden in Excel korrekt summieren.

Was die Datei damit immer noch nicht weiß

Die Farbe zeigt, dass ein Tag ein Feiertag ist. Sie sagt nichts darüber, ob an ihm gearbeitet werden darf und was daraus folgt. Nach § 9 ArbZG dürfen Arbeitnehmer an Sonn- und gesetzlichen Feiertagen von 0 bis 24 Uhr grundsätzlich nicht beschäftigt werden; § 10 ArbZG zählt die Ausnahmebereiche auf, von Not- und Rettungsdiensten über Gaststätten bis zu Verkehrsbetrieben.

Der praktisch wichtigste Punkt steht in § 11 Abs. 3 ArbZG: Wer an einem auf einen Werktag fallenden Feiertag beschäftigt wird, hat Anspruch auf einen Ersatzruhetag innerhalb von acht Wochen. Bei Sonntagsarbeit sind es zwei Wochen. Diese Frist läuft in der Tabelle nicht mit — sie muss von Hand nachgehalten werden, und genau daran scheitern Pläne im Dezember regelmäßig.

Ebenfalls außerhalb der Datei bleibt die Entgeltfrage. Fällt Arbeit wegen eines Feiertags aus, ist das Entgelt nach dem § 2 EntgFG fortzuzahlen — bei Schichtbetrieb und wechselnden Arbeitszeiten ist die Berechnung des Ausfalls die eigentliche Arbeit, nicht die Markierung im Plan.

Weiterlesen

Den kompletten Aufbau einer eigenen Datei beschreibt Dienstplan in Excel erstellen, die Zuschlagsrechnung Zuschläge in Excel berechnen. Welche Konstruktionsfehler sich am häufigsten einschleichen, sammelt der Beitrag zu den häufigsten Fehlern in Excel-Dienstplänen. Fertige Raster stehen in der Vorlagenübersicht und im Monatsplan als Excel-Vorlage.