Nachtschichten und mehrtägige Arbeitszeiten
Excel, alle Versionen
Die Grundlage
Die Berechnung von Arbeitszeiten ist ja standardmäßig so:
Arbeitsende-Arbeitsbeginn-Pausenzeiten.
Und das ergibt auch immer die richtigen Ergebnisse, solange das Arbeitsende nicht nach Mitternacht liegt. Ist das der Fall, dann werden Sie als Ergebnis einen „Lattenzaun” aus lauter Rauten sehen: ###…
Der Grund dafür: Excel kann im Normalfall negative Zeiten nicht anzeigen, wohl aber damit rechnen. Negative Ergebnisse wie sie bei Nachtschichten (beispielsweise 22:00 bis 06:30) und der oben gezeigten Rechenweise herauskommen würden, kann Excel immer noch nicht darstellen. Schade, aber ohne Umwege nicht zu ändern. Bei der Gelegenheit: Der „Umweg” mit den 1904-Datumswerten ist nicht zu empfehlen, es treten zu oft unerwartete Nebeneffekte und falsche Ergebnisse auf.
Sonderfall: Genau Mitternacht
Es gibt einen Sonderfall bei den Berechnungen, der eine echte Stolperfalle ist: Die Arbeitszeit endet genau um Mitternacht. Wir sind geneigt, dann 24:00 als Zeit einzugeben, weil wir berechtigterweise davon ausgehen, dass die 24. Stunde des Tages erfüllt ist und der neue Tag noch nicht begonnen hat. Aber merke: Der Mensch denkt, Excel lenkt. 💡
Geben Sie doch einfach einmal in eine beliebige Zelle den Wert 24:00 ein. Und schauen Sie dann einmal in die Editier- bzw. Eingabezeile. Da steht nicht nur die Uhrzeit sondern ein komplettes Datum mit Uhrzeit: 01.01.1900 00:00:00 und Ihre Eingabe wurde auch gleich umgewandelt, in der Zelle steht 0:00!
Excel handelt da einfach nach eigener Logik und wandelt den Wert 0.1.1900 (was der Zahl 0 in ein kalendarisches Datum umgewandelt entspricht) plus 24 Stunden (die ja eingegeben worden sind) um und zeigt nicht nur einen ungewollten Wert an sondern rechnet auch damit.
Folgerung: Wenn Sie Mitternacht eines Tages eingeben und damit auch rechnen wollen, dann geben Sie das Datum des Folgetages ein und als Uhrzeit 0:00, dann rechnet Excel auch genau so, wie Sie es wollen. 😎
Lösungsmöglichkeiten
Viele Wege führen zum Ziel. Zwei davon möchte ich Ihnen hier vorstellen. In A2 steht der Arbeitsbeginn, in B2 das Arbeitsende und in C2 die Pausenzeit. Dann verwenden Sie beispielsweise folgende Formel: =B2-A2-C2
… und das ergibt (natürlich) den eben genannten Effekt. Etwas abgeändert allerdings wird das korrekte Ergebnis ausgegeben: =B2-A2-C2+(B2<A2)
Damit ersparen Sie sich eine WENN()-Funktion. Das einzige, was etwas irritiert: Das Ergebnis wird als ganz normale Zahl ausgegeben. Formatieren Sie die Ergebniszelle als Zeit (einfach das Format aus Start oder Ende kopieren) und alles ist so, wie es sein soll. – Sie fragen, warum das funktioniert? Nun, der erste Teil der Formel ist bekannt. Dann folgt das Plus mit der in Klammern stehenden auszuwertenden Formel, dass B2 kleiner ist als A2. Das Ergebnis ist entweder WAHR oder FALSCH. Intern ist für Excel WAHR = 1, und FALSCH = 0. Was im Endeffekt bedeutet, dass immer dann, wenn die Ende-Zeit (und nur die) kleiner ist als die Anfangszeit, dann wird +1 gerechnet, sonst +0. Diese Formel einmal eingetragen kann problemlos nach unten kopiert werden, die Berechnung wird stets korrekt vorgenommen.
Nachtrag: Zwischenzeitlich verwende ich diese Formel in leicht abgewandelter Form:
So wird das Ergebnis als Zeit ausgegeben
Ohne die Pause wäre die Formel dann: =B2-A2+N(B2<A2) und entspräche damit fast der vorher definierten Formel. Der Vorteil hier: Das Ergebnis wird hier auch gleich im Zeit-Format ausgegeben und muss nicht nicht noch anders formatiert werden. Die Abbildung wurde mit Excel 2013 erstellt.
Keine Regel ohne Ausnahme. Wenn Sie mindestens 1 ganzen Tag im Einsatz waren, dann kann Excel natürlich nicht erkennen, wann der Start und wann das Ende war. Darum hier ein zweiter Lösungsweg. Sie beginnen am 03.01.2011 um 18:00 mit der Arbeit und enden am 05.01.2011 um 12:00. Dann müssen Sie die Eingaben in die Zellen mit der Angabe des Datums machen:
Zeiteingabe mit Datum
Sie erkennen, dass zwischen Datum und Zeit ein Leerzeichen ist. Für die Berechnung der Arbeitszeit gelten hier zwei Regeln: 1. Die Formel kann auch so aussehen: =B2-A2-C2 und sie wird nicht zu Fehlern führen, da bei korrekter Eingabe der Daten der Wert in Spalte B immer größer sein wird als in Spalte A. Das Datum wird hier ja bei der Berechnung der Zeit-Differenz mit einbezogen. Und 2.: Die Spalte C muss in einem benutzerdefinierten Zahlenformat dargestellt werden: [h]:mm ist der korrekte Wert. Das „h” oder „hh” (für die Stunden) muss in eckige Klammern eingeschlossen sein, damit die korrekte Zahl erscheint. Sonst werden alle vollen Tage „unterschlagen” und nur jene Stunden angezeigt, die als Rest berechnet werden, wenn die Gesamtstunden durch 24 dividiert (geteilt) bzw. die ganzen Tage subtrahiert (abgezogen) werden.
Hinweis: Sie können natürlich auch standardmäßig bei Zeitberechnungen ein Datum mit eingeben, selbst wenn beide Zeiten am gleichen Tag oder das Ende am Folgetag ist. Die Berechnung wird immer korrekt sein.
Wie bereits erwähnt, es führen viele Wege nach Rom. Andere Berechnungen mit der REST()-Funktion oder auch dem WENN() sind ähnlich effektiv. Die REST()-Funktion hat den Charme, dass beim Ergebnis gleich die Uhrzeit im Zeit-Format angezeigt wird: =REST(Ende-Anfang;1). Suchen Sie sich bei Bedarf aus dem Netz die Lösung, die Ihnen am meisten liegt.
Last but not least: Mitunter wird vorgeschlagen, die Option 1904-Datumswerte zu aktivieren. Ich rate davon eigentlich immer ab, diese Funktionalität birgt mehr Gefahren in sich als es Vorteile bringt. Einzig beim Import von Mac-Dateien (Apple) ist dieser Punkt wichtig und wirklich sinnvoll.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen …