Arbeitsstunden und Nachtzuschlag im Dienstplan mit Excel berechnen
Wer Dienstpläne in Excel führt, rechnet früher oder später Stunden aus: netto ohne Pause, über Mitternacht und mit Zuschlägen. Hier sind die Formeln, die tatsächlich funktionieren, samt typischer Fehler. Die Beispiele nutzen deutsche Funktionsnamen und Semikolons; in englischen Excel-Versionen heißt REST MOD und das Trennzeichen ist das Komma.
Grundlagen: Uhrzeiten sind Tagesbruchteile
Excel speichert 12:00 Uhr als 0,5, also als halben Tag. Eine Stunde ist 1/24. Deshalb ergibt eine Zeitdifferenz zunächst einen Bruchteil, den du mit 24 multiplizierst, um Dezimalstunden zu erhalten (8:30 Stunden = 8,5).
Netto-Arbeitszeit mit Pause
Trage Beginn in Spalte D, Ende in E und die Pause in F ein, die Pause als Uhrzeit (30 Minuten = 0:30). Dann gilt:
=(REST(E2-D2;1)-F2)*24REST(…;1) nimmt den Rest der Division durch einen Tag. Liegt das Ende nach Mitternacht (zum Beispiel 22:00 bis 06:00), wäre die Differenz negativ; REST macht daraus die richtige Dauer. Formatiere das Ergebnis als Zahl mit zwei Nachkommastellen, oder lass das Format [h]:mm, wenn du Stunden und Minuten sehen willst.
| Beginn | Ende | Pause | Netto-Stunden |
|---|---|---|---|
| 08:00 | 16:30 | 0:30 | 8,00 |
| 22:00 | 06:00 | 0:30 | 7,50 |
| 18:00 | 02:00 | 0:45 | 7,25 |
Nachtstunden: Überlappung mit dem Nachtfenster
Für die Nachtstunden prüfst du, wie weit die Schicht in das Fenster 20:00 bis 06:00 Uhr hineinreicht. Mit der Dauer L = REST(E2-D2;1) lautet die Formel für die Stunden im Nachtfenster:
=(MAX(0;MIN(D2+L;6/24)-D2)+MAX(0;MIN(D2+L;30/24)-MAX(D2;20/24))+MAX(0;MIN(D2+L;54/24)-MAX(D2;44/24)))*24Ersetze L durch REST(E2-D2;1) oder lege dafür eine Hilfsspalte an. Die drei Summanden decken die frühen Morgenstunden desselben Tages, die Nacht bis 06:00 Uhr des Folgetages und die Nacht des zweiten Tages bei sehr langen Schichten ab. Die Pause ist hier nicht abgezogen, weil sie meist nicht klar in der Nacht liegt; wer sie genau zuordnen will, braucht Pausenbeginn und -ende.
| Beginn | Ende | Stunden im Fenster 20–6 Uhr |
|---|---|---|
| 04:00 | 12:00 | 2,0 (04:00–06:00) |
| 18:00 | 02:00 | 6,0 (20:00–02:00) |
| 22:00 | 06:00 | 8,0 |
| 08:00 | 16:30 | 0,0 |
Welches Nachtfenster gilt?
- Arbeitszeitgesetz: Nachtzeit ist 23 bis 6 Uhr, Nachtarbeit liegt bei mehr als zwei Stunden darin vor (Nachtarbeit im Kundenservice).
- Steuerrecht (§ 3b EStG): Nachtarbeit im Sinne der Steuerfreiheit ist die Zeit von 20 bis 6 Uhr.
- Tarifvertrag oder Vertrag: Sie legen oft eigene Zeiten und Prozentsätze für den Zuschlag fest. Verwende das Fenster, das für deinen Zuschlag gilt, und passe die Zahlen 6/24, 20/24 usw. entsprechend an.
Zuschlag berechnen
Der Zuschlag ergibt sich aus Zuschlagsstunden × Stundenlohn × Prozentsatz. Beispiel: 6 Nachtstunden bei 20 € Stundenlohn und 25 % Zuschlag sind 6 × 20 € × 0,25 = 30 €. Gesetzlich verlangt das Arbeitszeitgesetz für Nachtarbeit nur einen angemessenen Ausgleich durch bezahlte freie Tage oder einen angemessenen Zuschlag (§ 6 Abs. 5 ArbZG); die Höhe regeln Tarif oder Vertrag.
Steuerfreie Zuschläge nach § 3b EStG
Zuschläge für tatsächlich geleistete Arbeit zu ungünstigen Zeiten sind bis zu folgenden Prozentsätzen des Grundlohns steuerfrei (Grundlohn höchstens 50 € pro Stunde):
| Zeit | Steuerfrei bis |
|---|---|
| Nacht (20–6 Uhr) | 25 % |
| Nacht 0–4 Uhr, wenn die Arbeit vor Mitternacht begann | 40 % |
| Sonntag | 50 % |
| Gesetzlicher Feiertag, 31.12. ab 14 Uhr | 125 % |
| 24.12. ab 14 Uhr, 25. und 26.12., 1. Mai | 150 % |
Höhere Zuschläge dürfen vereinbart werden; der übersteigende Teil ist dann steuer- und beitragspflichtig. Pauschale Zuschläge ohne tatsächlich geleistete Arbeit sind nicht steuerfrei. Für die Lohnabrechnung ist die Lohnbuchhaltung zuständig; die Angaben hier sind eine Orientierung (Quelle: Gesetzestext, z. B. auf buzer.de).
Typische Excel-Fehler
- Pause als Zahl: 30 statt 0:30. Dann musst du durch 1440 teilen (Minuten pro Tag).
- Uhrzeiten als Text: Ein Eintrag wird links ausgerichtet und rechnet nicht. Zelle als „Uhrzeit“ formatieren und neu eingeben.
- Summen über 24 Stunden: Mit dem Format [h]:mm statt h:mm, sonst springt die Anzeige zurück.
- ####-Anzeige: Eine negative Zeit; mit REST statt einfacher Differenz vermeiden.
- Rundungsfehler: Dezimalstunden mit RUNDEN(…;2) abschließen, bevor du multiplizierst.
- Feiertage: Excel kennt sie nicht; führe eine eigene Feiertagsliste je Bundesland.
Gegenprobe im Dienstplan-Tool
Das Dienstplan-Tool rechnet Stunden ohne Formeln: Die Wochenauswertung zeigt die geplanten Stunden je Person (Pausen abgezogen), Soll und Differenz, und der Kalender kennt die Feiertage je Bundesland. Zuschläge berechnet das Tool nicht; dafür bleibt die Rechnung wie oben in Excel. Mehr zum Vergleich im Ratgeber Excel oder Online-Tool.
Häufige Fragen
Wie berechne ich Arbeitsstunden in Excel mit Pause?
Mit =(REST(Ende-Beginn;1)-Pause)*24, wobei die Pause als Uhrzeit (zum Beispiel 0:30) eingetragen ist. Das Ergebnis sind Dezimalstunden, zum Beispiel 8,5.
Wie berechne ich Schichten über Mitternacht in Excel?
Mit der Funktion REST (englisch MOD): =REST(Ende-Beginn;1). Sie macht aus einer negativen Differenz die richtige Dauer, zum Beispiel 8 Stunden bei 22:00 bis 06:00 Uhr.
Wie hoch ist der Nachtzuschlag?
Gesetzlich ist nur ein angemessener Ausgleich vorgeschrieben (§ 6 Abs. 5 ArbZG). Die Höhe ergibt sich aus Tarifvertrag oder Arbeitsvertrag. Steuerfrei sind Nachtzuschläge für Arbeit zwischen 20 und 6 Uhr bis 25 Prozent des Grundlohns, für 0 bis 4 Uhr unter bestimmten Voraussetzungen bis 40 Prozent (§ 3b EStG).
Welche Zeiten gelten als Nacht?
Das hängt vom Zweck ab: Nach dem Arbeitszeitgesetz ist Nachtzeit von 23 bis 6 Uhr, für die Steuerfreiheit von Zuschlägen nach § 3b EStG gilt 20 bis 6 Uhr. Tarifverträge können eigene Zeiten festlegen.
Berechnet das Dienstplan-Tool Zuschläge?
Nein. Das Tool zeigt geplante Stunden je Person ohne Pausen, Soll und Differenz sowie Feiertage je Bundesland. Zuschläge berechnen Sie in einer Tabellenkalkulation oder der Lohnabrechnung.