Zeitberechnung in Excel (auch negative Zeiten)

Am Jahresanfang sind Arbeitszeiterfassungen sehr populär. Eine Frage die beim Erstellen einer solchen Vorlage immer wieder auftaucht ist: Wie kann ich mit Stunden in Excel Rechnen? Und wie kann ich negative Zeiten, z.B. Minderstunden, in Excel erfassen?

Diese und andere Fragen werde ich in diesem Blogbeitrag beantworten.

Wie man mit Stunden in Excel rechnet

Die folgenden Beispielhaften Aufstellungen zeigen Probleme welche man antrifft wenn man anfängt in einem normalen Excel Blatt mit Zeiten zu rechnen.

Beispiel 1

Beispiel 2

Beispiel 1: Die Eingabe von Zeiten ist kein Problem, auch die Differenz aus zwei Uhrzeiten können berechnet werden, so lange diese positiv ist wie in Zelle B4 (Formel wir in C4 gezeigt)! Wenn wir jedoch anfangen Minusstunden zu berechnen wie in Zelle B7 (Formel in C7) im Beispiel dann bekommen wir den Gartenhag zu sehen!

Beispiel 2: Excel addiert 8h Arbeit von vier Tagen und zeigt dann 08:00 an. Eine vermeintlich falsche Antwort!

Weshalb passiert das?

Um das zu verstehen müssen wir uns anschauen wie Excel mit Daten und Zeit rechnet.

Wie Excel Datum und Zeit Werte versteht und speichert

Jedes Datum in Excel wird als eine fortlaufende Zahl gespeichert. Jede Zeit, als ein Bruchteil einer ganzen Zahl.

Der 01. Jan 2015 wird in Excel als 42005 hinterlegt. 08:00 morgens als 0.333333. 8 Uhr morgens am 1. Jan 2015 wäre also 42005.333333.

Welche Zahl hinter einem Datum steckt kann man sehr einfach erkennen in dem man das Format von “Datum” auf “Standard” setzt.

Folgen wir dieser Logik stellt sich bald die Frage, welcher Tag wird in Excel als der erste anerkannt?

Die Antwort: Kommt drauf an…

1904-Datumswerte verstehen

Die Standard Einstellung in Excel ist das 1900-Datumswert System. Somit ist der erste Tag unter diesem System in Excel der 1. Jan 1900! Doch wie wir oben gesehen haben, kann man mit der Standard Einstellung in Excel keine negativen Zeiten berechnen und auch keine Zeiten addieren.

Damit wir negative Zeiten in Excel rechnen können müssen wir auf das das 1904-Datumswerte System wechseln.

Weshalb es diese zwei Systeme überhaupt gibt könnt Ihr im Detail direkt bei Microsoft nachlesen. Kurz zusammengefasst ist: Apple ist schuld.

Der Unterschied zur normalen Datum Einstellung sind 1462 Tage, was 4 Jahren und 1 Tag entspricht.

Der Grund hierfür liegt darin das mit der 1904-Datumswerte Einstellung die Zeitrechnung bei Excel neu am 02.01.1904 anfängt, während es in einem normalen Excel Blatt beim 01. Jan 1900 ist.

Interessant für Excel Geeks:

Excel erkennt keine Daten vor dem 01.01.1900 als solche. Der 31.12.1899 und alle vorherigen Daten werden als Text gespeichert in der 1900-Datum Einstellung! Im 1904-System erkennt es jedoch noch einen Tag vor dem 02.01.1904 (also 01.01.1904) weshalb wir in diesem System auch negative Zeiten rechnen können!

Microsoft hätte dies Problemlos für beide Systeme implementieren können. Weshalb es nicht getan wurde bleibt ein Geheimnis. Und ein Ärger.

Umstellen kann man das Datumssystem unter Excel-Optionen>Erweitert>1904-Datumswerte verwenden (im Bereich: Beim Berechnen dieser Arbeitsmappe)

ACHTUNG: Sämtliche Daten werden um 1462 Tage nach vorne versetzt wenn man in einer Arbeitsmappe die bereits Daten enthält diese Option wählt!!! Auch nicht zu vernachlässigen ist die Gefahr vom Verknüpfen einer Arbeitsmappe mit einer anderen welche ein unterschiedliches Datum System verwendet!!!

Nachdem der Haken gesetzt ist, können wir uns die beiden Beispiel vom Anfang nochmals anschauen:

Beispiel 1:

Wie man sieht haben sich hier zwei Dinge geändert ohne dass wir irgend eine Zelle bearbeitet haben: Die Minderstunden werden jetzt ausgewiesen und das Datum hat sich verschoben vom 01.01.2015 auf den 02.01.2019!

Beispiel 2:

Hier hat sich vordergründig nichts getan!

Weshalb das so ist hat mit der Uhrzeit Formatierung der Eingabewerte zu tun.

Uhrzeit Formate

Die Formatierung der Uhrzeit hat einen entscheidenden Einfluss auf das dargestellte Ergebnis wenn Stunden addiert oder subtrahiert werden in Excel.

hh:mm Format

Gibt man einen Wert als Stunden und Minuten in Excel ein, nimmt das Programm standardmässig hh:mm als das Zeit Format. Dieses Format kann gebraucht werden um eine fortlaufende Uhrzeit zu berechnen. Die Gleichung in diesem Format sagt: Wenn es 13 Uhr ist und nochmals 13 Stunden dazukommen, wie spät ist es dann? Die richtige Antwort ist dann natürlich 2 Uhr morgens.

So kann es geschehen dass aus vier addierten Arbeitstagen mit jeweils 8h eben 08:00 wird. Denn für Excel bedeutet dies 8:00 Uhr morgens am nächsten Tag!

[hh]:mm Format

Dieses Format wird gebraucht um eine absolute Anzahl Stunden [hh] zu berechnen. Die Gleichung in diesem Format sagt: Wenn ich 13 Stunden habe und nochmals 13 Stunden dazukommen, wie viele Stunden habe ich dann? Die korrekte Antwort ist dann natürlich 26, und diese bekommen wir auch wenn wir die Formate der Zeiten anpassen auf [hh]:mm

Hier ein paar Beispiel die das ganze veranschaulichen:

Die Formatierung wird natürlich wieder um im Zellen formatieren Menü vorgenommen (STRG+1 drücken) wobei wir hier ein benutzerdefiniertes Format erstellen.

Nehmen wir diese Formatierung an unserem Beispiel 2 vor dann stimmt jetzt auch die Berechnung unserer gearbeiteten Stunden!

Trick: Negative Zeiten eingeben

Trotz all der richtigen Einstellungen welche oben besprochen wurden, will Excel eine Eingabe einer negativen Anzahl Stunden nicht anerkennen!

Folgende Fehlermeldung kommt:

Mir scheint dies ist eine weitere Macke von Excel welche behoben werden sollte. Also müssen wir Excel austricksen um negative Werte einzugeben!

Hier der Kniff zum Erfassen von Negative Zeiten:

Beispiel 1:

z.B. werden -27:00 Minderstunden mit dem [hh]:mm Format wie folgt eingeben =-27/24

Es erscheint wie gewünscht -27:00!

Beispiel 2:

Es sollen 11 Stunden und 5 Min (-11:05) als Minderstunden eingegeben werden… etwas komplizierter, aber es geht auch.

Zuerst die Stunden in Minuten umrechnen…

(11 * 60 min.) + (5 min.) = 665 min.

24 * 60 min. = 1440 min.

Damit sie dann in der Zelle folgendes eingeben können: =-665/1440 was also -11:05 erscheint!