Nachtstunden berechnen in Excel: Wie viele Stunden einer Schicht liegen im Nachtfenster?

Manchmal reicht es nicht zu wissen, wie lang eine Schicht war. Sie wollen auch wissen, wie viele ihrer Stunden in ein bestimmtes Zeitfenster fallen, zum Beispiel zwischen 22:00 und 06:00 Uhr. Das Fenster ist dabei ein Parameter, den Sie selbst festlegen. Dieser Artikel zeigt, wie Sie diese Überschneidung mit Formeln berechnen, auch wenn Schicht und Nachtfenster über Mitternacht gehen. Ob und wie die so ermittelten Stunden später weiterverwendet werden, etwa für Zuschläge, müssen Sie gesondert prüfen; hier geht es nur um die Rechnung.

Die Idee: Überschneidung zweier Zeiträume

Die Nachtstunden sind die Überschneidung zwischen dem Zeitraum der Schicht und dem Zeitraum des Nachtfensters. Zwei Zeiträume überschneiden sich von „der spätere der beiden Anfänge“ bis „das frühere der beiden Enden“. Ist dieses Stück negativ, gibt es keine Überschneidung, und das Ergebnis ist 0. In Formeln heißt das:

MAX(0; MIN(Ende1; Ende2) - MAX(Anfang1; Anfang2))

Die Schwierigkeit liegt bei Mitternacht: Ein Nachtfenster von 22:00 bis 06:00 Uhr ist kein einzelner Zeitraum innerhalb eines Tages, sondern liegt je zur Hälfte am Abend und am frühen Morgen. Deshalb rechnen wir mit zwei Fenstern: dem Fenster, das am Vorabend begann, und dem Fenster, das am Tag der Schicht beginnt.

Aufbau der Tabelle

Zeile 1 enthält Überschriften, ab Zeile 2 steht je Schicht eine Zeile:

• C – Beginn (Eingabe, z. B. 22:00)
• D – Ende (Eingabe, z. B. 06:00)
• F – Dauer in Stunden (Formel)
• G – Nachtstunden (Formel)

Rechts daneben stehen die Parameter:

• K1 – Nachtbeginn, z. B. 22:00
• K2 – Nachtende, z. B. 06:00
• K3 – Länge des Fensters (Formel)

Geben Sie Uhrzeiten immer mit Doppelpunkt ein („22:00“). Mit Punkt geschriebene Werte erkennt Excel nicht als Uhrzeit. Formatieren Sie K1 und K2 als Uhrzeit, F und G als Zahl mit zwei Nachkommastellen.

Intern speichert Excel Uhrzeiten als Bruchteil eines Tages: 06:00 ist 0,25, 22:00 ist 0,9167 (22/24). Alle Rechnungen unten laufen in Tagesbruchteilen und werden am Ende mit 24 in Stunden umgerechnet.

Schritt 1: Länge des Nachtfensters

In K3:

=REST(K2-K1;1)

REST liefert den positiven Rest der Division durch 1 und damit auch bei einem Fenster über Mitternacht die richtige Länge. Rechenbeispiel: 06:00 − 22:00 = 0,25 − 0,9167 = −0,6667; REST(−0,6667;1) = 0,3333, das sind 8 Stunden. Bei einem Fenster ohne Mitternachtswechsel, etwa 20:00 bis 23:00 Uhr, ergibt sich 0,9583 − 0,8333 = 0,125, also 3 Stunden.

Achtung: Sind Nachtbeginn und Nachtende gleich, ergibt K3 den Wert 0, das Fenster ist dann leer.

Schritt 2: Dauer der Schicht

In F2:

=REST(D2-C2;1)*24

Das ist die bekannte Formel für Schichten über Mitternacht (siehe auch unseren Ratgeber Stundenzettel in Excel erstellen). Beispiel: Beginn 18:00, Ende 02:00 ergibt REST(0,0833 − 0,75;1) = 0,3333, mal 24 sind 8 Stunden. Pausen sind hier noch nicht abgezogen.

Schritt 3: Nachtstunden

Das Ende der Schicht auf einer fortlaufenden Zeitachse ist Beginn plus Dauer: C2+F2/24. Liegt es über 1, ist die Schicht in den nächsten Kalendertag gelaufen. Jetzt prüfen wir die Überschneidung mit beiden Fenstern. In G2:

=24*(MAX(0;MIN(C2+F2/24;$K$1+$K$3)-MAX(C2;$K$1))+MAX(0;MIN(C2+F2/24;$K$1+$K$3-1)-MAX(C2;$K$1-1)))

Der erste Teil prüft das Fenster, das am Tag der Schicht beginnt (von K1 bis K1+K3). Der zweite Teil prüft das Fenster vom Vorabend (um einen Tag nach vorn verschoben: von K1−1 bis K1+K3−1). Das Ergebnis wird mit 24 in Stunden umgerechnet.

Rechenbeispiele

Parameter: K1 = 22:00 (0,9167), K2 = 06:00, K3 = 0,3333.

Beispiel 1: Schicht 22:00 bis 06:00. Dauer 8 Stunden, Ende auf der Zeitachse 0,9167 + 0,3333 = 1,25. Erster Teil: MIN(1,25; 1,25) − MAX(0,9167; 0,9167) = 0,3333. Zweiter Teil: MIN(1,25; 0,25) − MAX(0,9167; −0,0833) = 0,25 − 0,9167, negativ, also 0. Ergebnis: 0,3333 × 24 = 8 Stunden.

Beispiel 2: Schicht 18:00 bis 02:00. Dauer 8 Stunden, Ende 0,75 + 0,3333 = 1,0833. Erster Teil: MIN(1,0833; 1,25) − MAX(0,75; 0,9167) = 1,0833 − 0,9167 = 0,1667. Zweiter Teil: MIN(1,0833; 0,25) − MAX(0,75; −0,0833) = 0,25 − 0,75, negativ, also 0. Ergebnis: 4 Stunden.

Beispiel 3: Schicht 04:00 bis 12:00. Dauer 8 Stunden, Ende 0,1667 + 0,3333 = 0,5. Erster Teil: MIN(0,5; 1,25) − MAX(0,1667; 0,9167) = 0,5 − 0,9167, negativ, also 0. Zweiter Teil: MIN(0,5; 0,25) − MAX(0,1667; −0,0833) = 0,25 − 0,1667 = 0,0833. Ergebnis: 2 Stunden.

Beispiel 4: Schicht 08:00 bis 16:30. Beide Teile ergeben 0, also 0 Stunden.

Weil das Fenster nur in K1 und K2 steht, können Sie es jederzeit ändern, ohne eine Formel anzufassen.

Grenzen der Formel

• Die Formel geht von Schichten unter 24 Stunden aus. Zusätzlich darf die Schicht das Nachtfenster des Folgetags nicht mehr erreichen: Sie muss kürzer sein als der Nachtbeginn in Stunden (bei 22:00 also unter 22 Stunden).
• Sind Beginn und Ende gleich, ergibt die Dauer 0, nicht 24.
• Pausen sind nicht berücksichtigt. Ob eine Pause in die Nachtstunden fällt, müssen Sie selbst festlegen; die Formel kann das nicht wissen. Eine einfache Lösung ist eine eigene Eingabespalte „Pause in der Nacht (Minuten)“, die Sie von G abziehen.
• Eingaben in Dezimalschreibweise (7,5) sind Zahlen, keine Uhrzeiten. Schreiben Sie Zeiten nur als „07:30“.

Anzeige und Summen

Rechenungenauigkeiten können dazu führen, dass intern 3,9999999 statt 4 steht. Setzen Sie bei Bedarf RUNDEN um die Formel: =RUNDEN(24*(...);4).

Für die Monatssumme genügt =SUMME(G2:G32). Für die Anzeige in Stunden und Minuten teilen Sie durch 24 und formatieren als [h]:mm: =G2/24 zeigt bei 8 Stunden 8:00. Ohne die Division durch 24 zeigt das Format falsche Werte: 7,5 würde als „180:00“ angezeigt (7,5 Tage) und nicht als 7:30.

Wie Sie solche Spalten in einen Dienstplan einbauen, lesen Sie in Schichtplan in Excel erstellen. Welche rechtlichen Vorgaben zu Nachtarbeit und Ruhezeiten bei Ihnen gelten, müssen Sie gesondert prüfen.

Hinweis

Auf dieser Website finden Sie einen kostenlosen Stundenzettel-Rechner, der nur in Ihrem Browser rechnet: /stundenzettel-rechner. Das Dienstplan-Paket der ebats solutions UG (haftungsbeschränkt) enthält vier Excel-Arbeitsmappen (Stundenzettel, Dienstplan, Urlaubsplaner, Überstundenkonto) und eine Anleitung als PDF und HTML, zum Preis von 14,90 € inkl. 19 % USt. Es handelt sich um einen digitalen Download, es fallen keine Versandkosten an. Das Paket ist eine Rechenhilfe und prüft nicht, ob gesetzliche Vorgaben eingehalten sind.

Alle Ratgeber · Herausgeber: ebats solutions UG (haftungsbeschränkt)