Beitrag aus SmartTools Excel Weekly
So rechnen Sie in Excel mit Datumswerten vor dem Jahr 1900
Excel 365 2024 2021 2019 2016 2013
FRAGE Ich arbeite seit Jahren mit Excel und kann ohne Probleme auch Formeln erstellen, in denen mit Datumswerten gerechnet wird. Nun bin ich aber an meine Grenzen gestoßen. In meinem neuesten Projekt möchte ich Stammbaumdaten analysieren. Naturgemäß habe ich es in diesem Fall mit Datumswerten zu tun, die sehr weit zurückliegen und da scheint Excel irgendwelche Probleme zu haben. Können Sie mir helfen?
R. Becker
ANTWORT In Excel beginnt die Zeitrechnung generell erst im Jahr 1900. Das ist in der Historie von Excel begründet, das in seinen Anfängen mit Lotus 1-2-3, dem damaligen Marktführer in Tabellenkalkulationsprogrammen, konkurrierte. Microsoft hat das Datumsmodell von Excel an Lotus 1-2-3 angelehnt und deshalb den 1. Januar 1900 als Startpunkt der Zeitskala gewählt. Alles, was davor liegt, ist für Excel bis heute kein gültiges Datum.
Das sehen Sie auch daran, dass Excel Datumswerte vor 1900 als Text interpretiert. Sie werden linksbündig so angezeigt, wie Sie sie eingegeben haben. Das ist anders als bei Datumswerten ab dem Jahr 1900, die Excel rechtsbündig anzeigt und womöglich sogar automatisch in ein Datumsformat mit führenden Nullen bei Tagen und Monaten konvertiert.
Wenn Sie mit Datumswerten vor 1900 rechnen wollen, müssen Sie folglich immer irgendwelche Hilfskonstruktionen einsetzen. Sie könnten zum Beispiel auf benutzerdefinierte VBA-Funktionen zurückgreifen, denn VBA unterstützt auch Datumswerte ab dem 1.1.100. Dafür müssen Sie sich allerdings mit der Programmiersprache von Excel auseinandersetzen.
Eine andere Hilfskonstruktion, die ohne Programmierung auskommt, könnte so aussehen, dass Sie Datumswerte vor 1900 immer 2000 Jahre in die Zukunft verlegen und Ihre Berechnungen dann mit diesen "gültigen" Datumswerten anstellen. So können Sie Datumsbereiche ab dem Jahr 0 abdecken.
Als Beispiel soll ein Tabellenblatt dienen, in dem Sie in den Spalten A und B Datumsangaben eingeben, um anschließend die Differenz in Jahren zu berechnen.
Wenn Sie die Jahresdifferenz mit einer Formel wie
=DATEDIF(A4;B4;"y")
berechnen lassen, erhalten Sie bei Datumsangaben vor 1900 den Fehler #WERT!.
Diesen Fehler umgehen Sie, indem Sie Hilfsspalten in das Tabellenblatt einfügen und darin die Datumsangaben um 2000 Jahre in die Zukunft verlegen. Im Beispiel fügen Sie rechts neben Spalte B zwei neue Spalten ein. Für die Umrechnung des Datumswerts in A4 geben Sie in Zelle C4 folgende Formel ein:
=WENN(ISTTEXT(A4);DATWERT(LINKS(A4;FINDEN("@";
WECHSELN(A4;".";"@";2)))&TEIL(A4;FINDEN("@";
WECHSELN(A4;".";"@";2))+1;4)+2000);EDATUM(A4;24000))
Die Formel können Sie dann nach rechts in Zelle D4 kopieren, um die Umrechnung für die Datumsangabe in Zelle B4 durchzuführen. Und für weitere Datumspaare kopieren Sie die Formelzellen C4:D4 einfach nach unten.
Die Formel prüft zunächst, ob es sich bei dem zu untersuchenden Datum um einen Textwert handelt. Das ist ein Zeichen dafür, dass ein Datum vor 1900 vorliegt. Wenn das nicht der Fall ist – und somit ein gültiges Datum ab dem 1.1.1900 vorhanden ist, – führt die Formel die EDATUM-Funktion am Ende aus, die das Datum ebenfalls 2000 Jahre (= 24.000 Monate) verschiebt. So nützt Ihnen die Hilfsspalte sowohl bei Jahren vor als auch nach 1900.
Bei Terminen vor 1900 erzeugt die Formel mittels DATWERT-Funktion ein gültiges Datum. Dazu untersucht sie die als Text vorliegende Datumsangabe. Die Formel geht davon aus, dass Sie das Datum in der hierzulande üblichen Notierung "Tag.Monat.Jahr" eingegeben haben.
Somit ermittelt sie zunächst den Text bis zum zweiten Punkt des Datumstexts. Dazu wandelt die Formel den zweiten Punkt in ein @-Zeichen um, sucht per FINDEN-Funktion nach der Position dieses Zeichens und liefert mittels LINKS-Funktion den Text bis zu dieser Position. Beim Datum "1.4.1815" wäre das zum Beispiel der Text "1.4.".
Mit einer TEIL-Funktion sucht die Formel dann nach demselben Prinzip den Text hinter dem zweiten Punkt des Datumstexts. Dieser Teil entspricht der Jahreszahl, die schließlich noch um den Wert 2000 erhöht wird. So erhalten Sie beim Datum "1.4.1815" beispielsweise die Jahreszahl 3815.
Zusammen bilden die beiden Textbestandteile eine gültige Datumsangabe – hier etwa "1.4.3815" –, die per DATWERT in einen Datumswert umgewandelt wird.
So verlegen Sie alle Datumsangaben in einen gültigen Datumsbereich, und Sie können mit den Ergebnissen aus den Hilfsspalten weitere Berechnungen anstellen. Im eingangs genannten Beispiel mit der Jahresdifferenz erhalten Sie das gewünschte Resultat nun zum Beispiel per
=DATEDIF(C4;D4;"y")