Sammlung ==> #16 Datum und Zeit

Datum und Zeit:

Gastbeitrag

Beim Rechnen mit Datums- und Zeitangaben benimmt sich Excel manchmal etwas exzentrisch. In diesem Beitrag erfahren Sie, warum das so ist und wie Sie diese Schwierigkeiten meistern.

Im ersten Beispiel (Ankunft 1) sehen wir eine Tabelle, in der ein Arzt versucht hat seine Sprechstunde mit Hilfe von Excel zu organisieren. Wenn Sie diese Tebelle ohne die Hilfe einer Formel zur Berechnung der Zeiten anlegen würden, würden Sie in Spalte D nur die Zeiten lesen können, bei der die Ankunftszeit der Patienten nach dem Zeitpunkt für den ausgemachten Termin liegt. Die anderen Zeiten würden als Fehlerwert ####### ausgegeben werden. Das macht Excel deshalb, weil diese anderen Zeiten ein negatives Ergebnis ergeben würden und Datums- und Zeitangaben in Excel immer nur als positive Werte wiedergegeben dürfen. Mit einem kleinen Griff in die Trickkiste ist diesem Problem aber relativ einfach beizukommen. Geben Sie in Zelle D3 die folgende Formel für die Berechnung der Zeiten ein:

=WENN(C3>=B3;C3-B3;TEXT(B3-C3;"-hh:mm"))

Die Wenn Funktion testet über den Ausdruck C3> (C3 größer) = B3; ob die Differenz zwischen der Ankunft des Patienten und seinem Termin positiv ist. Falls dieses Kriterium zutrifft, wird einfach über die Formel C3 - B3 die Differenz gebildet, da Excel in diesem Fall keine Probleme mit der Darstellung dieser Zeiten hat.

Wenn diese Differenz allerdings negativ ist, stellt Excel diese Werte normalerweise als Fehlerwert ##### dar, deshalb wird die Differenz umgekehrt berechnet also B3 - C3. Dabei erhalten wir dann wiederum ein positives Ergebnis, das wir allerdings über die eingefügte Funktion TEXT und die Angabe " - hh:mm" so definiert haben, das Excel uns diese Werte als negative Werte ausgibt (Beispielsweise: -00:10). Wenn Sie nun zum Abschluß diese Formel in die restlichen Zellen der Spalte kopieren (Feld am rechten Rand anfassen, so das Sie dort ein Kreuz [+] sehen und gleichzeitig die Taste STRG betätigen [++] und solange gedrückt halten, bis Sie die letzte Zelle Ihrer Spalte in der Sie ein Ergebnis sehen möchten erreicht haben), erhalten Sie als Ergebnis die oben abgebildete Tabelle mit den Ergebnissen, die Sie dort sehen können.
Diese Funktion können Sie verwenden, wenn Sie nur eben schnell mal ausrechnen möchten, wie die Zeiten aussehen würden. Wenn Sie diese Berechnungen allerdings übersichtlicher dargestellt haben wollen, benötigen Sie dazu zwei Spalten und zwei Formeln. Das fertige Ergebnis würde dann so aussehen, wie die folgende Tabelle es darstellt:

In der Spalte E sollen nur dann Zeiten erscheinen, wenn der Termin vor der Ankunft liegt, also eine positive Zeit (aus der Sicht von Excel gesehen) daraus resultieren würde. Für die Zelle E4 sieht die Formel also folgendermaßen aus:

=WENN(C4>=B4;C4-B4;"")

In der Spalte D sollen nur die Zeiten aufgeführt werden, die (wiederum aus der Sicht von Excel) zu einem negativen Ergebnis führen würden, nämlich wenn die Ankunftszeit vor dem vereinbarten Termin liegen würde. Die Formel für Zelle D4 sieht folglichermaßen so aus:

=WENN(C4<B4;B4-C4;"")

Als Format wählen Si dazu im Registerblatt ZAHLEN Uhrzeit und im Register FORMATE als Zeit 13:30. In Spalte E formatieren Sie die Zeit ebenfalls so. Wenn Sie die Zeiten, die aus Ihrer Sicht gesehen negativ sein würden, auf einen Blick erkennen möchten, können Sie als Format für die Spalte E noch die Textfarbe rot nehmen, dann werden diese Zeiten in roter Schriftfarbe dargestellt.

Alternative: Negative Zeiten in Minutenzahlen umwandeln

Wenn Sie eine Berechnung durchführen möchten, die Zeitergebnisse liefert, mit denen Sie dann weitere Berechnungen durchführen möchten, bietet sich als Alternative zu den zuvor beschriebenen beiden Berechnungen, die Möglichkeit an, die Ergebnisse als Minutenzahl darzustellen. Dann haben die Ergebnisse zwar kein Zeitformat mehr, aber Sie können mit diesen Ergebnissen problemlos weitere Berechnungen durchführen - ohne sich um irgenwelche Vorzeichen (-) Gedanken machen zu müßen.

Im Gegensat zu den beiden zuvor bwschriebenen und dargestellten Tabellen, besitzt die nachfolgende Tabelle kein Zeitformat in Spalte D, sondern das Zahlenformat Standard. Dort werde dann also Dezimalzahlen ausgegeben, mit denen man weitere Berechnungen durchführen kann.

Um die Differenz in Minuten zu berechnen, tragen Sie dort in Zelle D3 die folgende Formel ein:

=(C3-B3)*1440

Diese Formel macht sich die Tatsache zunutze, daß Excel intern Zeiten als dezimale Bruchteile von Tagen interpretiert. Beispielsweise wird die Zeit 12:00 (also zwölf Uhr Mittags) von Excel als 0,5 verwaltet und ausgegeben. Da ein Tag aus genau 1.440 Minuten besteht, müssen Sie die Differenz der Uhrzeiten also nur mit diesem Wert Multiplizieren und erhalten als Ergebnis die Minutenzahl der entsprechenden Uhrzeit. Die fertige Tabelle sieht danach folgendermaßen aus:

Arbeitstage planen und berechnen

Zur Planung von Arbeitszeitkapazitäten ist es nötig, mit Wochentagen zu rechnen, ohne die Wochenenden zu berücksichtigen. Über eine Formel mit den Tabellenfunktinen GANZZAHL und WOCHENTAG können Sie ein Datum und eine bestimmte Zahl von Werktagen zu einem Datum hinzu addieren. Zu diesem Zweck verwenden Sie die folgende Formel:

=A3+B3+GANZZAHL((B3+WOCHENTAG(A3)-2)/5)*2

Wenn Sie zum Beispiel in nZelle A3 das Startdatum und in Zelle B3 die Anzahl der Tage eingegeben haben, erhalten Sie als Enddatum, das Datum des Tages und Monats, wie es ohne die Wochenenden aussehen würde (also nur Arbeitstage Montag - Freitag; [ohne Wochenenden - 2])

Die Formel zählt das Startdatum nicht mit. Da heißt, wenn Sie das Startdatum und das Enddatum (das von der Formel geliefert wird) mitzählen, erhalten Sie einen Tag mehr, als Sie mit ANZAHL TAGE angegeben haben.
Differenz zweier Daten in Monaten

Mit der Tabellenfunktion MONAT können Sie zwar die Anzahl der Monate zwischen zwei Daten berechnen, aber nur wenn diese maximal 12 Monate beträgt.

Wie sieht das ganze aber aus, wenn Sie eine Zeitspanne berechnen wollen, bei der die Differenz mehr als zwölf Monate beträgt? Über eine Formel mit den beiden Tabellenfunktionen JAHR und MONAT berechnen Sie die Anzahl der Monate zwischen zwei Daten, die mehr als zwölf Monate auseinanderliegen. Diese Formel sieht folgendermaßen aus:

=(JAHR(Spätes Datum)-JAHR(Frühes Datum))*12+MONAT(Spätes Datum)-MONAT(Frühes Datum)

Wenn Sie diese Formel in eine Tabelle eingegeben, die so aufgebaut ist, wie die folgende Abbildung sie zeigt, erhalten Sie als Resultat Ihrer Bemühungen die tatsächliche Anzahl an Monaten, die zwischen diesen beiden Daten liegt.

Mit den beiden Parametern Spätes Datum und Frühes Datum geben Sie die Bezüge auf die Zellen an, in denen sich die Daten befinden, deren Differenz Sie in Monaten berechnen möchten.

Bei der Bildung der Differenz berechnet die Formel die Anzahl der angefangenen Monate. Falls zwischen zwei Daten also z. B. 1 Monat und 3 Tage liegen, liefert sie als Ergebnis die Zahl 2. Sie müßten also in so einem Fall den Term -1 an diese Formel anhängen, wenn Sie nur die vollen Monate berechnen wollen.

Die Formel funktioniert folgendermaßen: Zuerst wird ermittelt, wie viele Jahre zwischen den beiden Daten liegen. Da ein Jahr aus zwölf Monaten besteht, wird diese Zahl mit 12 multipliziert. Indem zu diesem Wert die Differenz der Monate addiert wird, erhalten Sie als Ergebnis die Anzahl der Monate, die zwischen den beiden Daten liegen.

Differenz zwischen zwei Zeitdaten in Tagen berechnen

Bei der Berechnung von Tagen - vor allem wenn diese Berechnungen über einen Zeitraum von einem Jahr oder länger gehen - ist zu beachten, das Excel ein Jahr nicht mit 365 Tagen wie wir es tun rechnet, sondern mit dem kaufmännischen Jahr = 360 Tagen. Wenn eine Berechnung in Tagen also sich also über einen Zeitraum erstreckt, dessen Spanne länger als ein Jahr ist, so gibt Excel die Tage im Kaufmännischen Wert zurück. Wenn Sie die tatsächliche Zahl an Tagen brauchen, müssen Sie also pro volles Jahr, über den Ihre Berechnungen sich erstrecken 5 Tage hinzuzählen. Die Formel zur Berechnung des kaufmännischen Jahres sieht folgendermaßen aus:

=TAGE360(A3;B3;WAHR)

Das Ergebnis, das Ihnen diese Formel liefert, können Sie in der folgenden Tabelle sehen:

Wie gesagt, wenn Sie die tatsächliche Anzahl an Tagen berechnen wollen, müssen Sie zu diesen Ergebnissen, pro Jahr noch 5 Tage hinzuzählen. Dazu müssen Sie allerdings noch eine zusätzliche Spalte zur Berechnung der tatsächlichen Tage in Ihre Tabelle einfügen. Die Formel für diese Berechnung lautet für die Zelle D3 =SUMME(C3;5). Diese Formel können Sie in die Zellen D4 bis D6 kopieren und ebenfalls in die Zelle D8. Für die Zelle D7 sieht die Formel folgendermaßen aus:

=SUMME(C7;(5*3))

Das Ergebnis dieser Bemühungen sieht folgendermaßen aus:

Für den 31. Januar müßten Sie in diesem speziellen Fall noch einen Tag hinzuzählen, weil Excel den Monat nur mit 30 Tagen berechnet, Sie aber bis einschließlich zum 31. Januar rechnen möchten. In diesem Fall würde das richtige Ergebnis für Zelle D7 1068 lauten und nicht wie es dort steht 1067.

Differenz zweier Daten in Jahren und Monaten

Falls Sie nicht nur an der Monatszahl, sondern an Jahren und Monaten interessiert sind (z. B. weil Sie jemanden längere Zeit nicht gesehen haben, und dieser sich zu Besuch angekündigt hat), müssen Sie eine etwas längere Formel verwenden, in der die Anzahl der Jahre und Monate zu einem Text kombiniert werden.

Diese Berechnung hat den Nachteil, dass sie auch angefangene Monate als ganze Monate zählt. Das Ergebnis dass Sie damit erhalten, ist also nicht unbedingt so genau, wie Sie es vielleicht gewohnt sind.

Die dazu benötigte Formel basiert auf der Formel für die Berechnung der Monatsdifferenz und sieht für die Berechnung der Monatsdifferenz und sieht für Zelle C3 folgendermaßen aus:

=GANZZAHL(((JAHR(B3)-JAHR(A3))*12+MONAT(B3)-MONAT(A3))/12)&"Jahr(e)und"&REST ((JAHR(B3)-JAHR(A3))*12+MONAT(B3) MONAT(A3);12)&"Monat(e)"

Dezimalzahl in Minutenzahl umwandeln

Wenn Sie eine Dezimalzahl im Zeitformat darstellen möchten, bleibt Ihnen normalerweise nur der Weg über die Funktion FORMAT | ZELLE | ZAHLEN. Dabei entstehen aber immer recht seltsame Werte, da Zeiten intern als Dezimalzahlen gerechnet werden.

Ganze Zahlen bilden dabei bereits einen kompletten Tag. Wenn Sie zum Beispiel aus der Dezimalzahl 73 (für 73 Minuten) die Zeit 1:13:00 (1 Stunde 13 Minuten 00 Sekunden) erzeugen möchten, müssen Sie zu einer Formel greifen, in der die Dezimalzahl als Minutenanzahl gerechnet wird.

Die Formel besteht aus einer Kombination der Tabellenfunktionen ZEIT, KÜRZEN und RUNDEN. Sie ist folgendermaßen aufgebaut:

=ZEIT(0;KÜRZEN(A3);RUNDEN((A3-KÜRZEN(A3))*60;))

Über die KÜRZEN Funktion wird der ganzzahlige Anteil, also die Minutenzahl der Dezimalzahl, ermittelt.

Der letzte Teil mit der RUNDEN Funktion berechnet aus dem dezimalen Anteil die Anzahl der Sekunden.

Über die Zeitfunktion schließlich werden Minutenzahl und Sekundenzahl zu einer gemeinsamen Zeit addiert. Das erste Argument der ZEIT Funktion ist aus diesem Grunde eine Null (für die Stunden), da es sich bei der Dezimalzahl ja um eine Minutenzahl handelt und sich die Stunden somit nur aus den Minuten ergeben. Die Zellenformatierung selbst ist dabei: "hh:mm:ss".

Sollten Rückfragen zu diesem Gastbeitrag bestehen, stehe ich Ihnen gerne zur Verfügung. Oliver Basel.