Sinnvoller Umgang mit negativen Zeitwerten
Excel, alle Versionen
Immer wieder beliebt und immer wieder ein Ärgernis: Negative Zeiten und Excel. Zugegeben, auch ich ärgere mich ständig darüber und kann einfach nicht verstehen, dass Microsoft nicht willens oder vielleicht auch nicht in der Lage ist, das Thema ein für allemal aus der Welt zu schaffen. Das vermutliche Argument „Kompatibilität” kann ich einfach nicht gelten lassen, denn wo ein Wille ist, ist bekanntlich auch ein Weg.
Eine Anmerkung vorweg: Es gibt immer wieder Zeitgenossen, die in mehr oder weniger arroganter Form behaupten, es gäbe keine negativen oder Minus-Zeiten. Denen kann ich nur entgegnen, dass Scheuklappen bei Pferden manchmal angebracht sind, bei Menschen jedoch den Blickwinkel zu sehr einengen. Ich jedenfalls verwende hier eine Wortwahl, die geläufig ist und von den meisten Anwenderinnen und Anwendern so verstanden wird, wie es gemeint ist.
Minus-Stunden (oder andere „negative Zeiten”) können entstehen, wenn beispielsweise eine Vorgabe von 38 Wochenstunden Arbeitszeit gegeben ist und nur 36 Stunden geleistet wurden. Und wenn keine Gründe wie Krankheit, Dienstgang, etc. vorliegen, dann sind in einem solchen Fall 2 Stunden zu wenig gearbeitet worden. So weit, so gut. Machen Sie das aber einmal Excel (in der Windows-Version) klar. Ich zeige Ihnen hier einmal das Ergebnis auf:
Falsche Darstellung negativer Zeiten
Ein schöner, langer Gartenzaun in D3. Das sieht zwar zuerst wie ein Fehler aus, ist aber in Wirklichkeit keiner. Ich belasse es in dieser Zelle bei der Formel und formatiere sie jetzt als ganz normale Zahl und:
Negativ-Zeit als „normale” Zahl formatiert
Der logische Schluss: Excel rechnet zwar richtig aber kann offensichtlich negative Zeiten nicht korrekt als Uhrzeit darstellen. Und so ist es auch. Der Beweis (achten Sie auf die Eingabe-Zeile!):
Beweis der korrekten Berechnung
… und wenn Sie nun E3 (wieder) im Zeit-Format darstellen, wird die korrekte Differenz von 0:00 Stunden angezeigt:
Korrigierte Zeitangabe als Zeit formatiert
Sie werden wahrscheinlich wissen: Excel kann mit kalendarischen Daten und Zeiten nur rechnen, weil intern eine so genannte serielle Zahl verwendet wird. Beim Datum gilt, dass der 1. Januar 1900 den Wert 1 hat, der 2. Januar 1900 den Wert 2, usw. Heute ist der 3. März 2012 und der Tag hat den Wert 40971. Und mit Zeiten verhält es sich ähnlich.
Zeiten werden als Teil des Tages berechnet. Um 12:00 Uhr Mittag ist genau der halbe Tag vorbei, intern rechnet Excel ½ (Tag) also 0,5. 6:00 Uhr früh ist ¼ Tag, also 0,25. Eine Stunde ist natürlich 1⁄24 Tag (entspricht 0,041666..) und eine Minute wiederum davon der 60te Teil. Wenn Sie in eine Zelle 0,75 eingeben und das Format auf Zeit ändern, wird dort sofort 18:00 erscheinen; die eigentlichen Werte sind identisch.
Das Problem mit den Minuszeiten lässt sich nun auf verschiedene Weisen lösen. Eine Lösung, die sehr einfach scheint ist aus verschiedenen Gründen nicht sehr praktikabel und darum nicht empfehlenswert: 1904-Datumswerte ist das Stichwort. Ich erkläre diese Option hier extra nicht, weil ich dabei mehr Ärger als Erfolg befürchte. Diese 1904-Datumswerte sind nur dafür gedacht, wenn aus einer Mac-Excel-Version Daten in einer Windows-Version geöffnet werden. Da Mac-Excel das 1904-Datumsformat verwendet, ist das in den Fällen erforderlich.
Für die empfehlenswerten Lösungsansätze verwende ich eine etwas umfangreicherer Datei, weil sie den realen Erfordernissen eher entspricht. Öffnen Sie dazu bitte erst einmal diese Datei. Sie sehen anfangs folgendes Bild:
Roh-Arbeitsblatt als Basis
Dank Gleitzeit ist an keinem einzigen Tag exakt 7:45 Stunden gearbeitet worden. In Spalte H soll nun entsprechend die Abweichung von der Soll-Zeit berechnet werden. Im ersten Schritt wird das auf die herkömmliche Art und Weise geschehen, im vollen Bewusstsein der (noch) fehlerhaften Darstellung. Das sieht dann so aus:
Fehlerhafte Darstellung der negativen Zeiten
Industriezeit verwenden
Zumindest ist klar erkennbar, an welchen Tagen das Soll unterschritten worden ist. OK, im ersten Schritt werden Sie etwas für die Optik tun, auch wenn es noch nicht zielführend ist. Plus-Zeiten sollen (der besseren Lesbarkeit wegen statt grün) in blau dargestellt werden und auch mit einem + davor sein. Minus-Zeiten rot und mit einem - davor und Null-Abweichungen bleiben schwarz und sind davor mit +/- ausgezeichnet. Zwischen den „Vorzeichen” und der Zeitangabe soll ein Leerzeichen sein.
Fenster mit den Formatierungsregeln
Na ja, prinzipiell funktioniert das ja auch, nur die Minuswerte „zicken” wieder herum:
Nach wie vor: „Lattenzaun” bei den Minuswerten
Der einfachste Weg zum Ziel besteht darin, die Industriezeit statt der hh:mm – Zeit zu verwenden. Da werden Stunden als normale Zahl dargestellt und Minuten als dezimaler Anteil einer Stunde. Das hört sich kompliziert an, ist es aber nicht wirklich. 1:30 Stunden sind eineinhalb Stunden, also 1,5 Stunden. Das gewünschte Ergebnis (in diesem Beispiel 1,5) erreichen Sie, indem Sie das errechnete Ergebnis mit 24 multiplizieren:
Darstellung als Industriezeit
Beachten Sie bitte die Klammen, denn Excel rechnet sonst mathematisch korrekt aber nicht wie es hier sein sollte. In der obigen Abbildung habe ich auch schon das Zahlen-Format (siehe folgende Abbildung) den neuen Gegebenheiten angepasst.
Das neue Zahlenformat für die Industriezeit
Das ist ein Ergebnis, mit dem man vielleicht schon leben kann. Es bedeutet zwar im Kopf eine Umrechnung von der dezimalen Darstellung (Industriezeit) zurück zu hh:mm, aber das Ganze hat auch Vorteile. Denn wenn die Arbeitszeit mit dem Stundenlohn multipliziert werden soll, dann brauchen Sie in jedem Fall die dezimalen Zeitwerte für die Berechnung. Zur Erinnerung: 6 Stunden haben intern einen Wert von 0,25 und wenn Sie das mit einem Stundenlohn von beispielsweise 10,00€ multiplizieren, dann wäre das ein ausgesprochen knapper Verdienst von insgesamt 2,50€ für die ganze Zeit. Das Ganze mit 24 multipliziert ergibt dann die korrekten sechzig Euro.
Dieser Stand, so wie er jetzt und hier vorliegt, ist in dieser Datei festgehalten.
Differenz-Zeiten als hh:mm
Natürlich ist es auch möglich, die Differenz-Zeiten im üblichen Format hh:mm darzustellen. Selbstverständlich auch die negativen Werte. Aber das Ganze hat einen kleinen Haken: Das Ergebnis der Berechnung ist ein Text, also keine Zahl, mit welcher Sie rechnen können. Wenn Sie das wollen, bietet sich das jeweilige Ergebnis aus Spalte H an. Aber sehen Sie selbst …
Als Referenz-Zelle verwenden Sie erst einmal stets H11, die zu testende Formel schreiben Sie immer in I11. Zur Kontrolle werden Sie die jeweils aktuelle Formel immer bis I18 herunter kopieren. Im ersten Schritt geht es darum, das passende Vorzeichen zu generieren. Dazu geben Sie in I11 diese Formel ein:
=WENN(H11>0; "+ "; WENN(H11<0;"- "; "+/- ")) & "0:00"
Der letzte Teil (&„0:00”) ist derzeit ein reiner Platzhalter, um die Optik etwas angenehmer zu gestalten. Sie sollten nun kontrollieren, ob die Vorzeichen für alle Zeilen korrekt sind. Wenn Sie exakt die obige Formel verwenden, dann wird alles OK sein.
Bereits an dieser Stelle ein Hinweis: Alle hier dargestellten Formeln müssen „in einem Stück”, also in einer Zeile des Eingabebereichs eingegeben werden. Die Darstellung hier ist nur aus technischen Gründen vielfach mehrzeilig.
Als nächstes kommen die Stunden an die Reihe. In I11 steht ja derzeit 0,75 als Wert, was einer dreiviertel Stunde entspricht. Um die vollen Stunden zu bestimmen, reicht ja eigentlich die Zahl vor dem Komma. Und dafür eignet sich prinzipiell die Excel-Funktion GANZZAHL(). Also ändern Sie in I11 die Formel dahin gehend ab, dass diese nun so aussieht:
=WENN(H11>0; "+ "; WENN(H11<0;"- ";"+/- ")) & GANZZAHL(H11)
Passt. Dort steht nun erst einmal + 0. Wenn dann noch die Minuten, getrennt durch einen Doppelpunkt folgen, passt das gut. Aber getreu dem Motto, nach jedem Schritt eine Kontrolle durchzuführen, kopieren Sie diese Formel auch nach unten:
Falsche Berechnung der Negativwerte
Aha. Jetzt wird Ihnen vielleicht klar sein, warum ich kurz vorher mehrfach „eigentlich” verwendet habe. Das Ergebnis ist ja wirklich irritierend bis hin zu falsch. Die Plus-Werte sind ja in Ordnung, auch die Null-Ergebnisse. Aber die negativen Zeiten haben ein doppeltes Minuszeichen und sind stets um den Wert 1 verändert. Und das ist kein Rundungsfehler, dann wären – 8,18 keineswegs – 9.
Langer Rede kurzer Sinn: Das geht so nicht. Was mir dann als nächstes einfallen würde: Die Stunden sollen ja auch als Text ausgegeben werden, vielleicht liegt es daran. Die korrigierte Formel würde dann so aussehen:
=WENN(H11>0;"+ ";WENN(H11<0;"- ";"+/- "))&TEXT(GANZZAHL(H11);"0")
Das Ergebnis wird sich aber auch dann nicht ändern. Es muss ein anderer Weg beschritten werden. Das „Zauberwort” heißt KÜRZEN() und ist eine Funktion, die hier wirklich die Nachkommastellen abschneidet und an der Zahl vor dem Komma keine Veränderungen vornimmt. Und bei der Gelegenheit können Sie auch gleich den Doppelpunkt als Trenner zwischen Stunden und Minuten mit in die Formatierung einfügen. Die Funktion sieht nun so aus:
=WENN(H11<0; "- "; WENN(H11=0; "+/- "; "+ ")) & TEXT(KÜRZEN(ABS(H11); 0); "00:")
Das Ergebnis wird erst einmal korrekt sein. Die doppelten Minuszeichen sind verschwunden und die Minuszeiten haben auch den korrekten Wert der Stunde:
Korrektes Zwischenergebnis
Bleiben erst einmal noch die Minuten, die zu ergänzen sind. Und die stehen ja im Nachkommateil der Spalte H. Allerdings nicht als „echte” Minuten sondern als Bruchteil einer Stunde. Wenn in H11 der Wert von + 0,75 steht, dann sind das ja null Stunden und 45 Minuten. Und um hier die „reinen” Minuten zu bekommen, muss der um die Stunden bereinigte Teil der Zeitangabe (hier: 0,75) mit 60 multipliziert werden. Diese Formel (komplett in 1 Zeile!) sieht dann so aus:
=WENN(H11<0; "- "; WENN(H11=0;"+/- "; "+ ")) & TEXT(KÜRZEN(ABS(H11); 0); "00:") & TEXT(REST(ABS(H11); 1)*60; "00")
Mit der REST()-Funktion ermittele ich den Nachkommateil des Wertes. Und wenn Sie nun die Formel nach unten kopieren, sieht das Ganze schon richtig gut aus:
Korrekte Ergebnisse im Textformat
Formatierung
Bleibt nur noch die Formatierung. Mit dem bisher verwendeten Zahlenformat ist hier nichts zu erreichen, denn das Ergebnis ist ja ganz klar Text. Das ist auch an der Ausrichtung links in der Zelle zu erkennen. Das Ausrichten der Inhalte nach rechts sollte Ihnen kein Problem bereiten. Tun Sie es.
Das Ganze lässt sich per Bedingte Formatierung lösen. Auch in Excel 2003 sind ja drei Bedingte Formatierungen ohne Einsatz von Makros möglich, darum hier die Anleitung im 2003er Format:
Markieren Sie erst einmal den Bereich I11:I18.
Anschließend Menü Format | Bedingte Formatierung… und Sie werden dieses Fenster sehen:
Bedingte Formatierung, erstes Fenster
Ändern Sie Zellwert ist zu Formel ist und tragen Sie dann folgende Formel ein:
Eintragen der Formel
Da ja Werte unter Null rot formatiert werden sollen, wählen Sie die entsprechende Textfarbe:
Auswahl der Farbe Rot für negative Werte
Nach einem OK wird diese Formatierung übernommen. Über Hinzufügen » legen Sie für die gleiche Zelle den Farbwert für positive Ergebnisse fest:
Der zweite Farbwert
… Und als Bedingung 3 die schwarze Schrift, wenn der Wert in H11 null ist. Ein letztes OK und auch der Teil ist geschafft. Ich habe die Rahmenformatierung in I18 noch angepasst und das Ergebnis lässt sich so durchaus sehen. Hier ist das endgültige Ergebnis.
Hinweis: Das Ganze ist wirklich nur eine Basis-Version. Die Daten und auch große Teile des Layouts wurden aus Unterlagen für eine Schulung entnommen, wo noch erheblich mehr zu diesem Thema erarbeitet wird.
Nachtrag: Ich habe hier noch eine Datei für den ganzen Monat eingefügt. Für ältere Excel-Versionen brauchen Sie ein Zusatztool (Kompatibilitätspack) von Microsoft, um diese Datei lesen und bearbeiten zu können, was natürlich auch für die anderen hier angebotenen Files gilt. Die volle Funktionalität ist dann aber nicht immer vorhanden, kleinere Einschränkungen müssen Sie hinnehmen.
Hinweise: Ein aufmerksamer Leser dieses Beitrages hat mich auf einige Fehler im Beitrag hingewiesen. Diese sind nun beseitigt. Vielen Dank Thomas B.! Und zum Thema „Zusatztool”, wo hierüber von mir der entsprechende Link eingefügt worden ist. Damals (April 2014) war auch ein Zugriff bei Microsoft unter der Adresse möglich. Zwischenzeitlich hat das Blog-Programm bzw. ein Add-In festgestellt, dass dieser Link nicht mehr erreichbar ist ind den Text durchgestrichen dargestellt. Um nicht in irgendwelche Copyright-Konflikte zu geraten, belasse ich es erst einmal dabei. Ich kann Ihnen aber folgende Abhilfe anbieten, da Sie in Excel-Versionen bis einschließlich 2013 dieses Tool brauchen, um die aktuellen Dateiformate *.xlsx lesen und in sehr vielen Funktionen auch bearbeiten zu können. Ich will Ihnen (vor allen Dingen aus rechtlichen Gründen) keine Empfehlung geben, wie Sie am besten an dieses Programm kommen. Ich habe eben (07.02.2021) einmal diesen Link genutzt und bin auch (beispielsweise) hier fündig geworden. Und ja, viele andere Links funktionieren gewiss auch.