Excel-TippNegative Uhrzeiten im 1900-Datumssystem darstellen

Excel kennt in seinem Standard-Datumssystem keine negativen Uhrzeiten. Wenn Sie dennoch eine negative Zeit darstellen wollen, brauchen Sie eine spezielle Formel. Und wenn Sie mit einer negativen Zeitangabe rechnen müssen, hilft die Erweiterung zur Matrixformel.

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).

Durch Berechnung erhalten Sie eine negative Zeitangabe oder Uhrzeit

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.

Darstellung von negativen Zeitangaben in Excel

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.

Rechnen mit negativen Uhrzeiten kann zu falschen Ergebnissen führen

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.

Mit der Matrixformel ist das Rechenergebnis mit negativen Uhrzeiten korrekt

Dazu im Management-Handbuch

Weiterlesen

Vorlagen nutzen

Excel-Tipps