Das Mysterium mit dem Datum - die Zeitrechnung von Excel

Hallo zusammen,

wer hat nicht schon mal mit den Tücken der Datumswerte in Excel zu kämpfen gehabt? Um sicher damit umgehen zu können sollte man zu allererst verstehen, was ein Datum in Excel überhaupt ist.

Ein Datum ist für Excel auch nur eine Zahl. Es gibt einen Punkt 0 und von da an wird einfach jeder Tag als 1 hochgezählt. Stunden, Minuten und Sekunden sind entsprechende Bruchteile von 1.

Excel hat für den "Nullpunkt" zwei verschiedene Einstellungen. Standardmäßig ist der am 00.01.1900 um 0:00 Uhr. Dies ist das Datum, das man bekommt, wenn man in eine als Datum formatiere Zelle die Zahl 0 eingibt. Zugegeben, der 00.01.1900 ist ein recht seltsames Datum, mit dem man nichts weiter anfangen kann, es geht halt erst mit 1 so richtig los ;)

Es gibt in den Excel-Optionen auch die Möglichkeit, das Datum an Das Macintosh-Format anzupassen - das zählt dann ab dem 01.01.1904 um 0:00 Uhr. Sollte dir also mal eine Tabelle begegnen, in der die Datumswerte um 4 Jahre differieren, könnte es sein, dass die mit dieser Einstellung angelegt wurde. Generell wird das allerdings unter Windows eher selten genutzt.

So, wir wissen also, dass ein Tag dem Wert 1 entspricht und alles, was kleiner ist als ein Tag ein entsprechender Bruchteil von 1 ist.

Hier mal eine kleine Tabelle, die in Spalte A und B die selben Zahlenwerte stehen hat, in Spalte A werden diese aber als Zahl und in Spalte B als Datum/Uhrzeit dargestellt. Das macht die Sache mit den Bruchteilen etwas deutlicher.

Ganze Zahlen sind also immer der Start des jeweiligen Tages, also 0 Uhr. Gibt man ein Datum mit der Uhrzeit 24:00:00 Uhr ein, zeigt Excel das als den Folgetag an. 

Beispiel: Gibt in eine als Datum formatierte Zelle "31.12.2011 24:00:00" ein und schau was passiert.

Das liegt daran, dass 24 Uhr und 0 Uhr des nächsten Tages die selbe Uhrzeit sind. Der 31.12.2011 0 Uhr entspricht der Zahl 40908. Plus 24 Stunden, also plus 1, ist 40909 und das wiederum ist der 01.01.2012 0 Uhr.

Anzeige/Formatierung von Datumswerten

Das Datum, das ich oben verwendet habe, ist keins der Standardformate, die bei Excel mitgeliefert werden. Man kann sich sein eigenes Format aber ganz einfach selbst erstellen, wenn man ein paar kleine Grundregeln kennt.

Dafür kann man entweder ein bestehendes Format an seine Bedürfnisse anpassen, oder das ganze neu schreiben. Wer sich beim Zellformat schon mal in das Register "Benutzerdefiniert" verirrt hat, hat dort bereits recht kryptisch anmutende Zeichenfolgen gesehen. Genau die werden wir jetzt entschlüsseln.

Grundlegend gelten für die verschiedenen Teile eines Datums die folgenden Abkürzungen:

J = Jahr

M = Monat

T = Tag

h = Stunde

m = Minute

s = Sekunde

Die Kombination dieser Abkürzungen - und der Zeichen dazwischen - ergibt dann die verschiedenen Datumsformate. Dabei ist die Groß- und Kleinschreibung bei m/M für Minuten/Monat unbedingt zu beachten "TT.mm.JJJJ" würde in der Mitte statt dem Monat gnadenlos die Minuten anzeigen. Generell empfehle ich die Großschreibung beim Datumsteil und die Kleinschreibung bei der Uhrzeit

Tasten wir uns mal Schritt für Schritt ran. Im folgenden beschreibe ich einige Format-Beispiele, die du weiter unten in einer Tabelle im Ein

Format "T.M.J"

Das kürzeste Format. Es kann mehr, als das, wonach es aussieht. Beim Jahr zeigt dieses Format immer 2 Stellen an. Aus "2001" wird "01" und aus "2011" wird "11". Bei Tag und Monat regiert es flexibel. Bei Werten kleiner 10 wird nur eine Stelle angezeigt, bei Werten größer 10 2 Stellen.

Format "TT.MM.JJJJ"

Eins der gebräuchlichsten Formate. Es zeigt bei Tag und Monat auch bei Werten kleiner 10 beide Stellen an - dann mit führender 0. Das Jahr kommt hier vierstellig an.

Format "TT. MMMM JJJJ"

Hier wird die Kombination noch deutlicher. Dieses Format zeigt den Tag 2-stellig und das Jahr 4-stellig an. Der Monat wird mit seinem Monatsnamen dargestellt. Tage und Monate haben zwar generell nur 2 Stellen, gibt man "T" oder "M" allerdings 3- oder 4-stellig im Format an, springt Excel zum Tages- bzw. Monatsnamen. "TTT" oder "MMM" zeigt die Abkürzung des Tages- oder Monatsnamens an und "TTTT" oder "MMMM" den kompletten Namen. Beim Jahr ist das anders. "J" und "JJ" zeigt das Jahr 2-stellig an, "JJJ" und "JJJJ" 4-stellig.

Hier kommt auch noch eine kleine Besonderheit bei den Zeichen dazu. Bisher hatten wir Tag/Monat/Jahr immer mit einem Punkt getrennt. Aber auch da ist Excel flexibel. In diesem Beispiel kommt nach dem Tag ein Punkt und ein Leerzeichen und nach dem Monat nur ein Leerzeichen. Da ist auch alles andere möglich.

Format "TTTT, TT.MM.JJJJ"

Hier wird als erstes der ausgeschriebene Wochentag, dann ein Komma und dann das lange Datum dargestellt. Damit sollte das Datum selbst soweit klar sein. Weitere Beispiele folgen unten in der Tabelle. Widmen wir uns der Uhrzeit.

Format "h:mm:ss"

Ich hab mit Absicht kein Format mit einstelliger Minuten-/Sekundenanzeige gewählt. Das sieht sehr seltsam aus und ist als Uhrzeit nicht mehr zu entziffern - würde aber genauso wie beim Datum funktionieren. Dieses Format zeigt also die Uhrzeit mit zweistelliger Minuten- und Sekundenangabe an und die Stunde nur im Bedarfsfall 2-stellig - im 24-Stunden-Format. Übrigens: mehr als 2 Stellen werden hier einfach ignoriert, weil die nicht möglich sind. 

Format "[$-409]hh:mm AM/PM"

Dieses Format zeigt die Stunden und Minuten immer 2-stellig an - der Teil sollte klar sein. Das "[$-409]" am Anfang bewirkt, dass Excel in das englische/amerikanische Darstellungsprinzip wechseln. Die beginnen ja ab 13 Uhr wieder mit 1 an zu zählen und stellen ein "AM" am Vormittag und ein "PM" am Nachmittag dahinter. Das "AM/PM" muss zwingend mit dahinter, sonst wechselt Excel wieder in die gewohnte Darstellung.

Format "[h]:mm"

Dieses Format ist für das Rechnen mit Zeiten gedacht, die nicht als Datum, sondern als Zeitspanne gedacht sind. Hier werden die Stunden nicht in Tage umgerechnet, wenn sie 24 überschreiten, sondern schön brav weitergezählt - die Minuten wiederum werden ab 60 als Stunde berechnet. Auch das ließe sich ändern: das Format [m] würde nur Minuten anzeigen. Wer auf die Idee kommt, das Format "[h][m]" ausprobieren zu wollen, wird einen Fehler produzieren - das wiederum ist ähnlich unlogisch wie eine Division durch null.

Achtung - wenn man ein "echtes" Datum so formatiert, kommen sehr große Zahlen dabei raus. Zum Rechnen mit Zeiten empfehle ich, nur die Stunden, Minuten und Sekunden einzugeben. Dazu später mehr.

Format "TTT, TT.MM.JJJJ   hh:mm:ss "Uhr""

Hier eine Kombination aus dem bisher Gelernten. Dies ist das Format, was ich oben in der Tabelle verwendet habe, erweitert um den abgekürzten Wochentag. Nach dem Datum habe ich der Übersichtlichkeit wegen nicht eins, sondern 3 Leerzeichen eingefügt - das könnten genau so gut 50 sein - dann die Uhrzeit und dann wieder ein Leerzeichen und den text "Uhr". Besonderheit: ich habe das Wort "Uhr" in Anführungszeichen gefasst. Muss eigentlich nicht, aber das Wort Uhr enthält dummerweise ein h, was wiederum als Stunde interpretiert würde. Ohne die Anführungszeichen würde sich zwischen "U" und "r" also der Wert der Stunden schummeln. Es wäre auch ausreichend, nur das h in Anführungszeichen zu setzen. So kann man auch beliebige Texte in das Format einbauen.

Soviel zu ein paar Beispielen. Hier noch eine etwas umfangreichere Tabelle mit Format und dem, was am Ende bei rauskommt. Wer das gern nachbauen möchte (macht sich zum Testen ganz gut) hier, was dahintersteckt:

In Spalte A habe ich das Format als Text eingegeben. In den 4 folgenden Spalten steht in der Kopfzeile ein Datum mit Uhrzeit. in den restlichen Zellen befindet sich eine Formel, die Datum und Format zusammenbringt. Die Formel heißt "TEXT" und bekommt 2 Parameter übergeben: einen zu formatierenden Wert und das Format, in das er gebracht werden soll. was am Ende rauskommt, wird als Text angezeigt (sieht man daran, dass es linksbündig ausgerichtet wird - ich hab das per Formatierung wieder geändert), taugt aber durchaus auch zum weiterrechnen.

Die Formel in Zelle B2 ist folgende: "=TEXT($B$1;A2)". Wenn dazu noch jemand Fragen hat: nur her damit ;)

So, hier die versprochene Tabelle

Rechnen mit Datumswerten

So, jetzt geht's ans Eingemachte. Ein Datum soll ja nicht nur schön angezeigt werden, sondern man will evtl. auch damit rechnen. Dafür hat Excel ein paar nette Formeln - bitte beachte hier aber, dass es in den neuen Excel-Versionen auch weit mehr Formeln gibt. Wenn du unter Excel 2003 oder früher arbeitest, kann es sein, dass die eine oder andere Formel nicht verfügbar ist.

Differenz zwischen zwei Tagen berechnen

Du weißt bereits, dass ein Datum auch nur eine Zahl ist. Aus diesem Grund kann man ein Datum ganz einfach vom anderen subtrahieren. Willst du also die Differenz zwischen 2 Tagen wiederum in Tagen berechnet haben, dann ziehst du einfach das frühere vom späteren Datum ab.

DATEDIF - Der Geheimtipp

Möglichkeit 2 - und etwas flexibler - ist die Formel "DATEDIF". Die ist ein echter Geheimtipp. Im Formelassistenten ist sie nicht enthalten - wird demzufolge auch nicht unterstützt - und auch in der Hilfe ist dazu nix zu finden.

Die Formel bekommt die Datumswerte genau umgekehrt wie beim Subtrahieren - also erst das frühere, dann das spätere Datum. Als drittes Argument gibt man an, in welcher Einheit man die Differenz haben möchte. Folgende Werte sind da möglich:

Wenn das dritte Argument direkt in der Formel erfasst wird, muss es in Anführungszeichen gesetzt werden:

=DATEDIF(B1;B2;"m")

Wenn es aus einer anderen Zelle kommt, darf es in dieser Zelle nicht in Anführungszeichen stehen.

Etwas tricky dabei ist die Definition "ganze Monate" (oder auch Jahre) im Hinblick auf die Tatsache, dass ein Datum immer 0 Uhr des betreffenden Tages bedeutet. Deshalb liefert unsere Formel bei "01.06.2012" und "31.07.2012" nicht wie erwartet eine 2, sondern nur eine 1. Grund: der 31.07.2012 ist nicht vollständig, da Excel die Uhrzeit 0 Uhr annimmt und damit der 31.07. grade erst beginnt. Deshalb habe ich mir angewöhnt, immer zum Enddatum 1 dazuzurechnen.

Beispiele findest du unten in der Tabelle. Grade die letzten drei Möglichkeiten für den Intervall werden erst am Beispiel richtig deutlich.

Datum auseinander nehmen und zusammensetzen

Wenn man nur bestimmte Teile eines Datums braucht, helfen zum auseinander nehmen die Formeln JAHR, MONAT und TAG weiter, die als einziges Argument das jeweilige Datum bekommen - Das kann als Zahl oder Datumstext in einer Zelle oder direkt in der Formel angegeben werden.

Um diese Werte wieder zu verknpüfen gibt es die Formel DATUM, die kriegt die drei Argumente Jahr, Monat und Tag - jeweils als Zahl, der Monatsname zieht hier nicht.

Rechnen mit Zeiten

Das wird schon schwieriger. Wenn du einfach Uhrzeiten bei Excel eingibst, dann macht es standardmäßig ein Datum draus. Alles, was über 24 Stunden hinausgeht, wird ganz gern mal einfach in einen Tag umgerechnet und auch entsprechend angezeigt. Da hilft die oben ersichtliche Formatierung mit den eckigen Klammern

Beispiel: Eine Liste mit Arbeitszeiten und am Ende eine Summe, die die Monatsarbeitszeit anzeigen soll: hier wird bei der Summe in den meisten Fällen (abhängig davon, welche Formatierungen bisher in der Tabelle verwendet wurden ist Excel auch manchmal intelligent und übernimmt diese) werden alle vollen 24 Stunden weggekürzt und es bleibt immer ein Wert kleiner 24 Stunden stehen.

In diesem Beispiel habe ich die Tage zwischendrin mal ausgeblendet, weil sonst der ganze Monat nicht ins Bild passt. Die Summe ist über eine einfache SUMME-Formel über die Spalte E berechnet worden.

Aber: Die Tage sind nicht weggekürzt worden, sondern die seltsame Anzeige ist einfach nur ein Formatproblem. Stellen wir das Zellformat um auf "[hh]:mm", dann haben wir wieder das richtige Ergebnis.

Das Rechnen an sich funktioniert genau wie beim Datum. Plus und Minus, Summenfunktion, Mittelwert etc. - man hat auch hier wieder alle Möglichkeiten

Auch die schon vom Datum bekannten Formeln zum Auseinandernehmen und Zusammensetzen gibt es für die Zeit. Die heißen halt nur entsprechend anders: STUNDE, MINUTE, SEKUNDE und zum Verketten ZEIT. Die Formel ZEITWERT macht aus einer Uhrzeit, wie z.B. "20:15" eine Zahl - in diesem Beispiel 0,84375.

Zeitminuten und Industrieminuten

Oft werden Zeiten auch als Dezimalwert gebraucht. Bei den Stunden kein Problem, die bleiben wie sie sind. Die Minuten müssen aber in sogenannte Industrieminuten umgerechnet werden.

Eine Stunde besteht normalerweise aus 60 Minuten. Also ist eine Minute 1/60 Stunden. Bei den Industrieminuten wird das auf das Dezimalsystem übertragen. Dort ist eine Minute 1/100 Stunden.

Minuten rechnet man also per Zeitminuten/60*100 in Industrieminuten um.

In Excel ist das einfacher. Man nimmt die Spalte mit der Zeit einfach mal 24 - und achtet darauf, dass das auch als Zahl und nicht als Zeit formatiert ist.

Soviel vorerst zum Thema Datum und Zeit.