Excel-TippNegative Uhrzeiten im 1900-Datumssystem darstellen
Negative Zeitangaben resultieren aus Berechnungen mit Uhrzeiten
Wenn Sie in Excel eine negative Uhrzeit in einer Zelle erfassen, dann wird diese standardmäßig mit einem Gartenzaun (# Rautezeichen) dargestellt. Dies ist in der Praxis oft ein Problem, da es viele Anwendungsmöglichkeiten gibt, in denen Sie mit negativen Uhrzeiten arbeiten müssen.
Vergleichen Sie zum Beispiel bei Mitarbeitenden die geleistete Ist-Arbeitszeit mit der täglichen Soll-Arbeitszeit, dann können sich auch negative Abweichungen ergeben (siehe folgende Abbildung).
Problem: Excel kennt keine negative Uhrzeit im 1900-Datsumsystem
In den Zellen mit negativen Abweichungen zeigt Excel den „Gartenzaun“ anstelle einer negativen Uhrzeit an. Im üblichen 1900-Datumssystem von Excel lassen sich keine negativen Uhrzeiten in einer Zelle angeben. Eine Eingabe oder eine Berechnung mit dem Ergebnis -hh:mm ist also nicht möglich.
Lösung: Formel zur Darstellung negativer Uhrzeiten in Excel
Da Excel keine negativen Uhrzeiten in einer Zelle darstellen kann, müssen Sie Excel mit der folgenden Formel überlisten:
=WENN(A2-B2<0;TEXT(ABS(A2-B2);"-hh:mm");A2-B2)
Die Formel geht dabei von den folgenden Annahmen aus (siehe Abbildung oben):
- A2 = Zelle, welche die Uhrzeit 1 (Ist) beinhaltet
- B2 = Zelle, welche die Uhrzeit 2 (Plan) beinhaltet
- Uhrzeit 2 wird von Uhrzeit 1 abgezogen, was zu einem negativen Ergebnis führen kann.
Die Formel geht dabei wie folgt vor:
Die Formel überprüft zunächst, ob es sich um eine positive oder negative Zeitdifferenz handelt: Wenn, …
Ist die Differenz negativ (<0), dann wird wie folgt vorgegangen (Wert-Wenn-Wahr):
- Es wird die absolute Zahl der Differenz mit der Funktion ABS() berechnet; das heißt, das negative Vorzeichen bei der Differenz wird nicht beachtet.
- Das Ergebnis wird anschließend in einen Text umgewandelt und mit einem Uhrzeitzahlenformat formatiert, dem ein Minuszeichen vorangestellt wird; dies geschieht über die Funktion TEXT().
- Sie bekommen somit die negative Uhrzeit als Text und nicht als Zahl in der Zelle dargestellt.
Ist die Differenz positiv (Wert-Wenn-Falsch), wird die Differenz als positive Uhrzeit (Zahl) dargestellt.
Problem: Mit negativen Uhrzeiten im Textformat lässt sich nicht rechnen
Wollen Sie jetzt über alle Differenzen (Spalte C) eine Summe bilden, dann müssen sie aufpassen, da die negativen Uhrzeiten keine Zahlen, sondern Texte in Excel darstellen. Um dies genauer zu verdeutlichen, wird im Beispiel aus der vorigen Abbildung eine Summe mit der Funktion SUMME() gebildet.
Das Ergebnis sieht auf den ersten Blick gut aus, ist aber falsch, da das richtige Ergebnis 2:00 lauten müsste. Excel hat die negativen Uhrzeiten bei der Summenbildung nicht berücksichtigt, da diese Texte und keine Zahlen darstellen.
Lösung: Formel für das Rechnen mit negativen Uhrzeiten zur Matrixformel erweitern
Sie müssen für diese Aufgabenstellung wieder eine spezielle Summenformel erstellen:
=WENN(SUMME(A2:A6-B2:B6)<0;TEXT(ABS(SUMME(A2:A6-B2:B6));"-hh:mm");SUMME(A2:A6-B2:B6))
Es handelt sich dabei um die Formel von oben, die zu einer Matrixformel erweitert ist. Damit können Sie die entsprechenden Zeilendifferenzen – egal ob Zahl oder Text – summieren. Mit dieser Formel wird zunächst für jede einzelne Zeile die Differenz ermittelt und anschließend aufsummiert. Die summierte Zahl wird dann wieder in einen Text umgewandelt, sodass auch negative Summen als Uhrzeit dargestellt werden können.
Wichtig: Da es sich um eine Matrixformel handelt, bestätigen Sie die Formel nicht mit Enter, sondern mit der Tastenkombination Strg + Umschalttaste + Enter. Die Formel wird dann automatisch durch geschweifte Klammern {} eingerahmt.
