Pause abziehen in Excel: als Eingabe oder per Regeltabelle
Beginn und Ende allein ergeben die Anwesenheit, nicht die Arbeitszeit. Die Pause muss noch abgezogen werden. Dieser Artikel zeigt zwei Wege: die Pause als Wert, den Sie selbst eintragen, und die Pause als Ergebnis einer Regeltabelle, die Sie selbst definieren. Alle Formeln sind Rechenhilfen. Welche Pausen vorgeschrieben oder vereinbart sind, müssen Sie gesondert prüfen.
Variante 1: Pause als eingetragener Wert
Spalten: C – Beginn, D – Ende, E – Pause in Minuten (Eingabe), F – Arbeitszeit in Stunden (Formel). In F2:
=WENN(ODER(C2="";D2="");0;REST(D2-C2;1)*24-E2/60)
Zur Erklärung: D2−C2 ist die Differenz in Tagesbruchteilen, REST(...;1) fängt Schichten über Mitternacht ab, mal 24 ergibt Stunden. E2/60 rechnet Minuten in Stunden um. Die WENN-Abfrage liefert 0 für leere Zeilen.
Rechenbeispiel: Beginn 09:00, Ende 17:45, Pause 45. Die Differenz beträgt 8,75 Stunden, minus 45/60 = 0,75 ergibt 8 Stunden. Eine Schicht von 22:00 bis 06:00 mit 30 Minuten Pause: REST(0,25 − 0,9167;1) = 0,3333, mal 24 = 8, minus 0,5 ergibt 7,5 Stunden.
Pause als Uhrzeit statt Minuten
Möchten Sie die Pause lieber als Uhrzeitwert eintragen (0:45 für 45 Minuten), rechnen Sie im Tagesbruchteil weiter:
=WENN(ODER(C2="";D2="");0;(REST(D2-C2;1)-E2)*24)
Beispiel: 09:00 bis 17:45 und Pause 0:45: (0,3646 − 0,0313) × 24 = 0,3333 × 24 = 8 Stunden. Die Zelle E muss dafür als Uhrzeit formatiert sein und die Eingabe mit Doppelpunkt erfolgen. Tippen Sie nur „45“ ein, versteht Excel 45 Tage. Wer Minuten als einfache Zahl eintragen will, bleibt deshalb besser bei Variante 1.
Variante 2: Pause aus einer Regeltabelle
Hier hängt die Pause von der Länge der Anwesenheit ab. Statt die Pause jeden Tag einzutippen, legen Sie eine Tabelle mit Schwellen an und lassen Excel nachschlagen.
Wichtig: Die Zahlen unten sind reine Beispielwerte, damit die Formel anschaulich wird. Sie sind keine Aussage darüber, welche Pausen gelten. Welche Regeln für Ihren Betrieb maßgeblich sind, müssen Sie gesondert prüfen und die Tabelle entsprechend selbst anpassen.
Legen Sie die Tabelle in M1:N4 an:
| M – Ab Anwesenheit (Stunden) | N – Pause (Minuten) |
|---|---|
| 0 | 0 |
| 6 | 30 |
| 9 | 45 |
(Zeile 1 enthält die Überschriften, die Werte stehen in M2:N4.) Die erste Zeile mit 0 ist Pflicht, damit auch kurze Schichten einen Treffer finden. Die Schwellen müssen aufsteigend sortiert sein.
Die Formel mit VERWEIS
Nun die Spalten C – Beginn, D – Ende, E – Pause manuell (optional, Minuten), F – Pause angewendet (Formel), G – Arbeitszeit (Formel). In F2:
=WENN(ODER(C2="";D2="");0;VERWEIS(RUNDEN(REST(D2-C2;1)*24;4);$M$2:$M$4;$N$2:$N$4))
VERWEIS(Suchkriterium; Suchvektor; Ergebnisvektor) sucht in M2:M4 den größten Wert, der kleiner oder gleich der Anwesenheit ist, und gibt den Wert aus derselben Zeile von N2:N4 zurück. RUNDEN auf vier Stellen verhindert, dass eine Rechenungenauigkeit (5,99999999 statt 6) die Schwelle knapp verfehlt.
Rechenbeispiele mit der Beispieltabelle:
- 09:00 bis 14:30: Anwesenheit 5,5 Stunden, größte Schwelle ≤ 5,5 ist 0, Pause 0.
- 08:00 bis 16:30: 8,5 Stunden, Schwelle 6, Pause 30.
- 07:00 bis 17:00: 10 Stunden, Schwelle 9, Pause 45.
- 08:00 bis 14:00: genau 6 Stunden, Schwelle 6, Pause 30.
Dass die Schwelle bei „ab“ greift und nicht bei „über“, ergibt sich aus der Funktionsweise von VERWEIS. Brauchen Sie „über 6 Stunden“, tragen Sie eine Schwelle knapp darüber ein, zum Beispiel 6,01. Prüfen Sie, welche Lesart für Ihre Regel gilt.
Manuelle Abweichung zulassen
Manchmal weicht der Tag von der Regel ab. Dafür dient Spalte E: Steht dort etwas, gilt es, sonst die Tabelle. F2 lautet dann:
=WENN(ODER(C2="";D2="");0;WENN(E2<>"";E2;VERWEIS(RUNDEN(REST(D2-C2;1)*24;4);$M$2:$M$4;$N$2:$N$4)))
Und die Arbeitszeit in G2:
=WENN(ODER(C2="";D2="");0;REST(D2-C2;1)*24-F2/60)
Rechenbeispiel: 08:00 bis 16:30 mit leerer Spalte E: F = 30, G = 8,5 − 0,5 = 8 Stunden. Mit E = 60: F = 60, G = 8,5 − 1 = 7,5 Stunden. Eine 0 in E bedeutet „keine Pause“ und wird respektiert, denn 0 ist nicht leer.
Tabelle statt fester Zahlen in der Formel
Ändert sich eine Regel, passen Sie eine Zelle in N an, statt in jeder Formel Zahlen zu suchen. Das Gleiche gilt für eine feste WENN-Kette wie WENN(h>=9;45;WENN(h>=6;30;0)): Sie funktioniert, hat aber die Zahlen fest in der Formel versteckt. Wollen Sie mehrere Regelsätze, etwa für verschiedene Betriebsteile, legen Sie je Satz eine eigene Tabelle an. Dabei sollten Sie die Bereiche sauber trennen und nicht mit anderen Hilfsbereichen überschneiden. Die Tabelle M1:N4 liegt hier bewusst weit rechts von den Datenspalten.
Typische Fehlerquellen
- #NV bei VERWEIS: Die Tabelle beginnt nicht bei 0, oder die Anwesenheit ist negativ.
- Falsche Sortierung: VERWEIS erwartet aufsteigende Schwellen, sonst entstehen falsche Treffer ohne Fehlermeldung.
- Uhrzeiten als Text: Schreiben Sie „08:00“ mit Doppelpunkt. Auch Dezimalwerte wie 7,5 sind Zahlen und keine Uhrzeiten.
- Pausenzeit länger als die Schicht: Dann wird die Arbeitszeit negativ. Prüfen Sie Ihre Eingaben.
Den vollständigen Aufbau eines Stundenzettels mit Datum, Soll und Monatssumme finden Sie in Stundenzettel in Excel erstellen.
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)