„Minus“-Zeiten

Sinnvoller Umgang mit negativen Zeitwerten

Excel, alle Versionen

Immer wieder beliebt und immer wieder ein Ärgernis: Negative Zeiten und Excel. Zuge­ge­ben, auch ich ärgere mich ständig darüber und kann einfach nicht verstehen, dass Micro­soft 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 Ergeb­nis auf:

Falsche Darstellung negativer Zeiten

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

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 Ein­gabe-Zeile!):

Beweis der korrekten Berechnung

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

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 Op­ti­on hier extra nicht, weil ich dabei mehr Ärger als Erfolg befürchte. Diese 1904-­Datums­werte 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

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 Be­wusst­sein der (noch) fehlerhaften Darstellung. Das sieht dann so aus:

Fehlerhafte Darstellung der negativen Zeiten

Fehlerhafte Darstellung der negativen Zeiten

▲ nach oben …

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 wer­den und auch mit einem + davor sein. Minus-Zeiten rot und mit einem - davor und Null-­Ab­weichungen bleiben schwarz und sind davor mit +/- ausgezeichnet. Zwischen den „Vor­zeichen“ und der Zeitangabe soll ein Leerzeichen sein.

Fenster mit den Formatierungsregeln

Fenster mit den Formatierungsregeln

Na ja, prinzipiell funktioniert das ja auch, nur die Minuswerte „zicken“ wieder herum:

Nach wie vor: "Lattenzaun" bei den Minuswerten

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

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 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 mul­ti­pli­ziert werden soll, dann brauchen Sie in jedem Fall die dezimalen Zeitwerte für die Berech­nung. Zur Erinnerung: 6 Stunden haben intern einen Wert von 0,5 und wenn Sie das mit einem Stundenlohn von beispielsweise 10,00€ multiplizieren, dann wäre das ein aus­ge­sprochen knapper Verdienst von insgesamt 5,00€ 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.

▲ nach oben …

Differenz-Zeiten als hh:mm

Natürlich ist es auch möglich, die Differenz-Zeiten im üblichen Format hh:mm dar­zu­stel­len. 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ön­nen. 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 schrei­ben 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 gene­rieren. 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 angeneh­mer 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 eigent­lich 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 Kon­trolle durchzuführen, kopieren Sie diese Formel auch nach unten:

Falsche Berechnung der Negativwerte

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 dop­peltes 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 kor­ri­gierte 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 ver­schwun­den und die Minuszeiten haben auch den korrekten Wert der Stunde:

Korrektes Zwischenergebnis

Korrektes Zwischenergebnis

Bleiben erst einmal noch die Minuten, die zu ergänzen sind. Und die stehen ja im Nach­kom­mateil 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

Korrekte Ergebnisse im Textformat

▲ nach oben …

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 Aus­rich­tung 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

Bedingte Formatierung, erstes Fenster

Ändern Sie Zellwert ist zu Formel ist und tragen Sie dann folgende Formel ein:

Eintragen der Formel

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

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

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 ange­bo­te­nen Files gilt. Die volle Funktionalität ist dann aber nicht immer vorhanden, kleinere Ein­schränkungen müssen Sie hinnehmen.

▲ nach oben …

Dieser Beitrag wurde unter Datum und Zeit, Formatierung, Musterlösungen, Ohne Makro/VBA, Stundenabrechnung abgelegt und mit , , , , , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.