Nacht-, Sonntags- und Feiertagszuschläge in Excel berechnen

Zuerst: Anspruch und Steuerfreiheit sind zwei verschiedene Dinge

Die häufigste Verwechslung im Dienstplan ist die zwischen „Zuschlag muss gezahlt werden“ und „Zuschlag ist steuerfrei“. Beides folgt unterschiedlichen Regeln.

Ein Anspruch auf Zuschläge ergibt sich in der Regel aus Tarifvertrag, Betriebsvereinbarung oder Arbeitsvertrag. Nur für Nachtarbeit gibt es eine gesetzliche Auffangregel: Fehlt eine tarifliche Ausgleichsregelung, hat der Nachtarbeitnehmer nach § 6 Abs. 5 ArbZG Anspruch auf eine angemessene Zahl bezahlter freier Tage oder einen angemessenen Zuschlag. Das Bundesarbeitsgericht hält 25 Prozent des Bruttostundenlohns regelmäßig für angemessen und bei Dauernachtarbeit regelmäßig 30 Prozent (Urteil vom 9. Dezember 2015 – 10 AZR 423/14). Für Sonntags- und Feiertagsarbeit existiert keine vergleichbare gesetzliche Anspruchsgrundlage – wer keinen Tarifvertrag und keine vertragliche Zusage hat, hat auch keinen Anspruch.

Die Steuerfreiheit regelt dagegen § 3b EStG, und zwar unabhängig davon, worauf der Zuschlag beruht. Sie greift nur für Zuschläge, die zusätzlich zum Grundlohn und für tatsächlich geleistete Arbeit in den begünstigten Zeiten gezahlt werden. Ein pauschaler Aufschlag ohne Bezug zu konkreten Stunden fällt nicht darunter – deshalb braucht der Dienstplan die Stunden ohnehin einzeln.

Die Sätze im Überblick

Drei Details entscheiden über die korrekte Zuordnung. Erstens ist der Grundlohn für die Steuerfreiheit auf 50 € je Stunde gedeckelt; wer mehr verdient, erhält den Zuschlag anteilig steuerfrei. Für die Beitragsfreiheit in der Sozialversicherung liegt die Grenze bei 25 € je Stunde – ein Zuschlag kann also steuerfrei und trotzdem beitragspflichtig sein. Zweitens gilt der 40-Prozent-Satz nur, wenn die Nachtarbeit vor Mitternacht begonnen hat; wer um 1 Uhr anfängt, bleibt bei 25 Prozent. Drittens treffen Sonntag und Nacht häufig zusammen: Beide Zuschläge dürfen nebeneinander steuerfrei bleiben, der Feiertagszuschlag verdrängt dagegen den Sonntagszuschlag, wenn beides auf denselben Tag fällt.

Das Excel-Problem: Nachtstunden über Mitternacht

Eine Schicht von 18:00 bis 2:00 Uhr enthält vier begünstigte Nachtstunden – von 20:00 bis 0:00 – plus zwei weitere von 0:00 bis 2:00, für die wegen des Beginns vor Mitternacht der höhere Satz greift. Rechnet man mit reinen Uhrzeiten, liegt das Ende rechnerisch vor dem Beginn, und jede Bedingung greift ins Leere.

Die saubere Lösung beginnt vor der Formel, in der Eingabe: Beginn und Ende gehören als Datum und Uhrzeit in die Zelle, etwa 11.08.2026 18:00 und 12.08.2026 02:00. Wer die Datumsspalte getrennt führt, setzt sie mit =Datum+Uhrzeit zusammen. Danach sind alle Zeiten monoton steigend, und es genügt eine einzige Formel für alle Fälle – die Überschneidung zweier Zeiträume:

Das Muster MAX(0;MIN(Ende;FensterEnde)-MAX(Beginn;FensterBeginn)) ist die eigentliche Antwort auf die Frage. Es funktioniert für jedes Zeitfenster – Nacht, Sonntag, Feiertag –, liefert nie einen negativen Wert und braucht keine Fallunterscheidung danach, ob die Schicht über Mitternacht läuft. Für einen langen Dienst, der zwei Nachtfenster berührt, addiert man schlicht zwei solche Terme. Wer die Grundlagen zur Zeitrechnung in Excel nachlesen will, findet sie im Beitrag Arbeitsstunden in Excel korrekt summieren.

Sonntage und Feiertage erkennen

Der Sonntag ist der einfache Fall: =WOCHENTAG(Beginn;2)=7 liefert WAHR für Sonntag. Zu beachten ist nur die Sonderregel des § 3b EStG, nach der zur Sonntagsarbeit auch die Zeit von 0 bis 4 Uhr des Montags zählt, wenn die Arbeit vor 0 Uhr aufgenommen wurde – dieselbe Logik wie beim 40-Prozent-Satz und mit derselben Überschneidungsformel abbildbar.

Feiertage kennt Excel nicht. Sie brauchen eine eigene Liste je Bundesland, sinnvollerweise auf einem separaten Tabellenblatt mit einem benannten Bereich Feiertage. Die Prüfung lautet dann =ZÄHLENWENN(Feiertage;GANZZAHL(Beginn))>0. Diese Liste ist der Punkt, an dem Vorlagen still veralten: Sie gilt für ein Kalenderjahr und für ein Bundesland, und sie wird bei der Wiederverwendung im Folgejahr regelmäßig vergessen. Ein Vermerk mit Jahr und Bundesland direkt neben der Liste kostet nichts und verhindert genau diesen Fehler.

Für die Monatssumme genügt anschließend SUMMEWENNS über die Zuschlagsspalten je Person. Wichtig bleibt das Zellformat: Summen über 24 Stunden brauchen [h]:mm, sonst zeigt Excel den Rest nach dem vollen Tag – ein Fehler, der bei Zuschlagsstunden besonders leicht übersehen wird, weil die Zahlen klein aussehen.

Ein durchgerechnetes Beispiel

Angenommen, ein Mitarbeiter mit einem Grundlohn von 20 € je Stunde arbeitet an einem Samstag von 18:00 bis 2:00 Uhr, mit 45 Minuten Pause von 21:00 bis 21:45. Die Zuschlagsstunden verteilen sich so:

Zwei Dinge fallen an diesem Beispiel auf. Erstens entscheidet die Lage der Pause mit darüber, welche Stunden begünstigt sind: Die 45 Minuten fallen hier in das Nachtfenster und kürzen genau dort die Zuschlagsstunden. Ein pauschaler Abzug am Schichtende würde stattdessen die höher begünstigten Stunden nach Mitternacht treffen und die Rechnung verfälschen. Zweitens summieren sich Nacht- und Sonntagszuschlag: 2 Stunden × 20 € × (40 % + 50 %) ergeben 36 €. Der Grundlohn von 20 € liegt unter beiden Deckelungen, sodass der gesamte Betrag steuer- und beitragsfrei bleibt.

Wo die Tabelle an ihre Grenze kommt

Die Zuschlagsrechnung ist in Excel machbar, aber sie ist die aufwendigste Konstruktion, die ein Dienstplan enthalten kann: fünf verschachtelte Zeitfenster, eine gepflegte Feiertagsliste je Bundesland, eine korrekte Pausenlage und am Ende ein Export, den die Lohnabrechnung verwerten kann. Jede dieser Stellen ist einzeln beherrschbar; zusammen entsteht eine Datei, die niemand außer der Person versteht, die sie gebaut hat.

Genau an diesem Punkt lohnt der Vergleich mit einer Dienstplan-App. Aplano rechnet Zuschlagszeiträume und Stunden aus denselben Buchungen, aus denen der Plan entsteht, und liefert Auswertungen als Export für die Lohnabrechnung; Planday, Shyftplan oder Factorial verfolgen einen ähnlichen Ansatz. Wann der Wechsel sinnvoll ist, beschreibt der Beitrag Excel-Vorlage oder App. Solange die Zuschlagslogik im Betrieb einfach bleibt – ein Nachtfenster, keine Feiertagsarbeit –, ist die Tabelle weiterhin die schlankere Lösung.

Weiterlesen

Die Zeitrechnung von Grund auf erklärt Arbeitsstunden in Excel korrekt summieren, die typischen Stolperfallen der Beitrag zu den häufigsten Fehlern in Excel-Dienstplänen. Eine geprüfte Struktur für den Schichtbetrieb bietet der Schichtplan für drei Schichten; wer ohne Formeln starten möchte, nutzt den Dienstplan-Generator.