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!
Noch´n Tip: Wenn ich nun z.B. auch negative Stunden mit Minutenanteil eingeben möchte, also z.B. -10:05 Stunden geht (wenn auch etwas umständlich) der Trick mit dem Bruch: Die Eingabe muss dann lauten: -(10+5/60)/24
oder noch einfacher
-“10:05”
Sehr cool! Kannte ich noch nicht. Danke!
oder unter Optionen>Berechnen Häkchen setzen bei “1904-Datumswerte verwenden”
Die “1904” ist oben erwähnt, allerdings kann man auch mit dem gesetzen Häkchen kein negative Zeiten direkt Eintragen… es geht eben nur über diesen Kniff.
Alles recht, aber was tun wenn ich 01:07 und 10760:58 zusammen zählen will, alse 1h 7 min und 10760 h 58 min … sollte 10762:05 rauskommen, würde man meinen. Der Wert 5 stellige Wert wird ignoriert. Was tun?
Danke, es hilft wirklich
Vielen Dank
Und wer weiß, wie man Minusstunden in einer Dezimalzahl darstellt? Bei positiven Uhrzeiten funktioniert das prima, bei negativen Uhrzeiten jedoch nicht.
Beispiel 1: die SOLLzeit beträgt 7:30h die ISTzeit beträgt 2:00h auf dem Zeitkonto sind jetzt -5:30h. Als Dezimalzeit wird mir -12:00 angezeigt. Also falsch.
Beispiel 2: die SOLLzeit beträgt 7:30h die ISTzeit beträgt 7:15h auf dem Zeitkonto sind jetzt 0:15h. Als Dezimalzeit wird mir 0,25h angezeigt. Also richtig. Was mache ich falsch?
*Die SOLLzeit beträgt 7:30h die ISTzeit beträgt 7:15h auf dem Zeitkonto sind jetzt 0:15h. *
Bei Euch möchte ich auch arbeiten, wo muss ich mich Bewerben?
Was wenn ich die Zeit von 14:30 bis 2:30 in der Nacht berechne? Muss ich dann 26:30 eingeben, damit der Wert nicht negativ wird?
Hallo, dank Ihrer Beschreibung mit der Umstellung auf das Datum, konnte ich in meiner Arbeitszeiterfassung auch die Minusstunden berechnen.Wie kann ich aber die Gesamtsumme von Plus- und Minusstunden berechnen, sprich, dass ich weiß, ob ich am Monatsende Plus- oder Minusstunden habe?
Guten Tag, ich möchte in Excel eine Summe errechnen indem ich ein datum minis ein anderes Datum, also =F2-D100 z.B. und dann das ganze auf 100 Spalten machen. Ich bekomme beim Kopieren von vier mit der hand eingegebenen Spalten immer -43456 angezeigt. Was kann ich tun bitte. Danke für Eure Hilfe.
Wie müsste die Formatierung aussehen damit ich die Zeit in diesem Format bekomme: 08 Uhr 30 min
Das funktioniert nicht. hh“ Uhr „mm“ min“
Oder so:
Sub ConvTimeHMSToNegativ()
ActiveCell.Value2 = -(Hour(ActiveCell.Value2) / 24 + Minute(ActiveCell.Value2) * 6.94444444444442E-04 + Second(ActiveCell.Value2) * 0.0000115741)
End Sub
Kleine Präzisierung für Super-Geeks:
Excel erkennt tatsächlich auch Daten vor dem 01.01.1900, zwar nicht den 31.12.1899 aber sowas Ähnliches, nämlich den (eigentlich nicht real existierenden) 00.01.1900.
Der 01.01.1900 entspricht nämlich tatsächlich bereits der “1”. Den 00.01.1900 kann ich zwar so nicht eingeben, aber wenn man eine 0 als Datum formatiert, wird dieser Tag tatsächlich angezeigt und sogar als Samstag erkannt (ist somit irgendwie so etwas wie der “31.12.1899”. 0,5 als Datum formatiert ergibt den 00.01.1900 12 Uhr.
Ist das weiter wichtig? Kaum, in den meisten Fällen macht das keinen Unterschied, trotzdem steht es eben ein bisschen falsch oder ungenau in einigen Lehrwerken verzeichnet. Einen Unterschied macht es, wenn man sagt, der Zahlenwert eines Datums entspricht der Anzahl der Tage seit dem 1.1.1900. Dieser ist aber eben genau um 1 höher als die vergangenen Tage.
Danke. Auch dieser Kommentar von Dir ist eine Bereicherung!
Schön wäre dennoch die Einführung eines neuen Zellen-Plus/Minus-Zeitformates das in jeder Arbeitsmappe funktioniert ohne Umstellungen an den Dateioptionen vornehmen zu müssen.
Ich habe auch das Problem, das ich beim Summieren mit Std. einen falschen Wert als Ergebnis erhalte. Sobald ein Arbeitstag einen Minuswert enthält. (Also z.b. ein Arbeitstag abgezogen wird) Die Summe aus: 01:44,01:30, 01:59, -07:00, 01:04 ergibt den Wert 06:32 Die 7 Minusstunden werden quasi ignoriert. Was mache ich falsch? Wie sollte die Formel aussehen? Muss vielleicht dazu sagen, dass diese Werte ebenfalls aus je einer Berechnung entstanden sind.