Kompatibilität: Die in diesem Artikel vorgestellten Methoden funktionieren in allen aktuellen Excel-Versionen (Excel 2010, 2016, 2019, Microsoft 365) sowie in Excel für das Web. Einzelne Funktionen wie TEXTTEILEN stehen nur in neueren Versionen (Excel 365) zur Verfügung.
Arbeiten Sie mit historischen Daten, genealogischen Informationen oder langfristigen Zeitreihen? Dann stoßen Sie in Excel schnell an eine harte Grenze: Datumswerte vor dem Jahr 1900 lassen sich nicht direkt berechnen.
In diesem Beitrag zeige ich Ihnen Schritt für Schritt, wie Sie dieses Problem elegant lösen – inklusive praxiserprobter Formeln und Profi-Tipps.
⚠️ Wichtiger Hinweis vorab: Der 400-Jahre-Trick basiert auf dem gregorianischen Kalender, der 1582 eingeführt wurde. Für Daten vor dem 15. Oktober 1582 arbeiten Sie mit einem sogenannten proleptischen Kalender – das ist für die meisten genealogischen Zwecke völlig ausreichend, aber historisch nicht exakt. Für Jahreszahlen vor Christus (negative Jahreszahlen) funktioniert dieser Trick nicht, da Excel keine negativen Jahreszahlen verarbeitet. Hier wäre eine Umstellung auf eine reine Tageszählung (z. B. julianische Tagesnummer) nötig – das würde den Rahmen dieses Beitrags sprengen.
1. Problemstellung: Warum Excel bei 1900 stoppt
Excel verwendet intern ein sogenanntes Seriendatums-System. Dabei wird jedes Datum als fortlaufende Zahl gespeichert – beginnend mit dem 01.01.1900 als Tag 1. Das führt zu einem entscheidenden Problem:
Alle Datumswerte vor dem 01.01.1900 werden von Excel nicht als Datum erkannt, sondern als Text behandelt.
Die Folgen:
- Keine Berechnungen möglich (#WERT!)
- Funktionen wie DATEDIF, WOCHENTAG oder JAHR funktionieren nicht
- Diagramme und Zeitachsen sind unbrauchbar
💡 Tipp zur Dateneingabe: Damit Excel das Datum garantiert als Text behandelt, geben Sie es mit einem vorangestellten Apostroph ein: ‚10.07.1750 oder formatieren Sie die Spalte als „Text“.
2. Praxisziel: Historische Datumswerte berechnen
In diesem Artikel lernen Sie:
- wie Sie Datumswerte vor 1900 korrekt verarbeiten
- wie Sie Differenzen (z. B. Alter oder Zeiträume) berechnen
- wie Sie Excel dazu bringen, „historische Daten“ zu verstehen
3. Die Lösung: Der 400-Jahre-Trick
Der entscheidende Ansatz basiert auf einem mathematischen Prinzip:
Der gregorianische Kalender wiederholt sich alle 400 Jahre exakt in seiner Struktur.
Das bedeutet:
- Der 01.01.1800 hat denselben Wochentag wie der 01.01.2200.
- Die Abfolge von Schaltjahren ist identisch.
- Zeitspannen (Tage zwischen zwei Daten) bleiben gleich.
Die Idee:
Sie verschieben das Datum einfach um +400 Jahre (oder ein Vielfaches davon) in die Zukunft, führen Ihre Berechnung durch und nutzen das Ergebnis. Da sich die Schaltjahr-Zyklen decken, ist das Ergebnis mathematisch absolut präzise – allerdings nur innerhalb des gregorianischen Kalenders (ab 1582).
4. Schritt-für-Schritt-Anleitung
📌 Hinweis: Die folgenden Formeln gehen vom deutschen Datumsformat TT.MM.JJJJ aus (z. B. 10.07.1750). Bei anderem Format (z. B. MM/DD/YYYY) müssen die INDEX- bzw. TEIL-Formeln entsprechend angepasst werden.
Angenommen, Sie haben folgendes Datum in Zelle A2: 10.07.1750 (Excel erkennt dies nur als Text).
Schritt 1: Datum zerlegen
Zerlegen Sie das Textdatum in seine Bestandteile Tag, Monat und Jahr. Für ältere Excel-Versionen (ohne TEXTTEILEN) nutzen Sie:
Tag: =LINKS(A2; FINDEN(„.“;A2)-1)
Monat: =TEIL(A2; FINDEN(„.“;A2)+1; FINDEN(„.“;A2; FINDEN(„.“;A2)+1) – FINDEN(„.“;A2)-1)
Jahr: =RECHTS(A2; 4)
Schritt 2: 400 Jahre addieren
Addieren Sie zum Jahr einfach 400: 1750 + 400 = 2150. Jetzt liegt das Datum im gültigen Excel-Bereich.
Schritt 3: Hilfsdatum erstellen
Erstellen Sie ein „Hilfsdatum“, mit dem Excel rechnen kann:
=DATUM(Jahr+400; Monat; Tag)
📌 Hinweis: Die DATUM-Funktion prüft nach Excel-Regeln (nicht streng historisch). Bei historisch ungültigen Datumsangaben (z. B. 29.02.1800) korrigiert sie automatisch. Für maximale Genauigkeit sollten Sie nur historisch gültige Daten verwenden.
5. Berechnungen durchführen (Praxisbeispiel)
Jetzt können Sie ganz normal rechnen.
Beispiel: Altersberechnung
=DATEDIF(Hilfsdatum_Geburt; Hilfsdatum_Tod; „Y“)
Der Vorteil: Da beide Daten um exakt 400 Jahre verschoben wurden, ist die Differenz (das Alter) auf den Tag genau korrekt. Eine Rückrechnung des Ergebnisses ist bei Differenzen nicht notwendig!
Konkretes Zahlenbeispiel:
- Geburt: 10.07.1750 (Hilfsdatum → 10.07.2150)
- Tod: 15.03.1823 (Hilfsdatum → 15.03.2223)
- =DATEDIF(B2; C2; „Y“) → ergibt exakt 72 Jahre (korrekt).
Weitere Beispiele mit dem Hilfsdatum:
- Wochentag: =WOCHENTAG(Hilfsdatum;2) – Ergebnis: 1 = Montag, 2 = Dienstag, …, 7 = Sonntag. Für den Wochentag als Text: =TEXT(Hilfsdatum;“TTTT“) (zeigt z. B. „Montag“)
- Monate addieren: =EDATUM(Hilfsdatum;3)
- Arbeitstage: =NETTOARBEITSTAGE(Hilfsdatum_Geburt; Hilfsdatum_Tod)
⚠️ Achtung zu DATEDIF: Diese Funktion ist undokumentiert. Bei exakten Monats- und Jahresgrenzen (z. B. 31.01. → 01.03.) sollten Sie die Ergebnisse stichprobenartig prüfen.
6. Komfortlösung mit Excel 365
Wenn Sie Excel 365 nutzen, können Sie die Funktion TEXTTEILEN verwenden, um das Textdatum (z. B. in A2) direkt zu verarbeiten. Möchten Sie die Verschiebung flexibel halten, ersetzen Sie den Wert +400 durch eine Zellreferenz (z. B. $E$1), sodass Sie den Verschiebungswert (400, 800 …) zentral ändern können:
=DATUM(INDEX(TEXTTEILEN(A2;“.“);3)+400; INDEX(TEXTTEILEN(A2;“.“);2); INDEX(TEXTTEILEN(A2;“.“);1))
📌 Hinweis: Da TEXTTEILEN eine Matrix liefert, greifen wir mit INDEX gezielt auf Jahr (3), Monat (2) und Tag (1) zu.
7. Alternative Methode: Textanalyse (für ältere Versionen)
Falls Sie ohne moderne Funktionen arbeiten, extrahieren Sie die Werte manuell (die Einzelformeln für Tag, Monat und Jahr haben Sie bereits in Schritt 1 kennengelernt). Kombiniert in einer einzigen Formel für das Hilfsdatum:
⚠️ Achtung: Um auch einstellige Tage/Monate (z. B. 1.5.1800) sicher zu erfassen, ist die Funktion DATWERT oft riskant. Nutzen Sie stattdessen die folgende Formel:
=DATUM(RECHTS(A2;4)+400; TEIL(A2; FINDEN(„.“;A2)+1; FINDEN(„.“;A2; FINDEN(„.“;A2)+1) – FINDEN(„.“;A2)-1); LINKS(A2; FINDEN(„.“;A2)-1))
8. Wichtige Hinweise aus der Praxis
1. Die 1900-Schaltjahr-Falle
Excel behandelt das Jahr 1900 fälschlicherweise als Schaltjahr (den 29.02.1900 gab es historisch nicht).
💡 Profi-Tipp: Wenn Ihre Daten den Bereich um den Februar 1900 kreuzen, rechnen Sie lieber mit +800 Jahren. So umgehen Sie den Berechnungsfehler im „Excel-Jahr 1900“ komplett.
2. Sehr alte Daten (vor 1500)
Hier reicht +400 Jahre nicht aus, um sicher im positiven Bereich zu landen:
- vor 1500 → +800 Jahre
- vor 1100 → +1200 Jahre
Arbeiten Sie immer in 400er-Schritten, um die Wochentags-Logik beizubehalten.
🚨 Obergrenze beachten: Excel kann nur Daten bis zum 31.12.9999 darstellen. Bei sehr alten Ausgangsdaten und großen Verschiebungen (+800 oder mehr) prüfen Sie, ob das resultierende Hilfsdatum diese Grenze nicht überschreitet.
3. Grenzen des Tricks (wichtig für Genealogen)
- Der Trick basiert auf dem gregorianischen Kalender ab 1582. Für Daten davor (z. B. 15.03.1500) arbeiten Sie mit einem proleptischen Kalender – das ist für viele Anwendungen ausreichend, aber nicht historisch exakt.
- Unterschiedliche Einführungsdaten des gregorianischen Kalenders: Verschiedene Länder haben den gregorianischen Kalender zu unterschiedlichen Zeitpunkten eingeführt – Großbritannien erst 1752, Russland sogar erst 1918. Wenn Sie Datumsangaben aus verschiedenen Quellen oder Ländern miteinander vergleichen, müssen Sie daher prüfen, ob beide Daten wirklich im selben Kalendersystem erfasst wurden. Andernfalls können sich stille Fehler von mehreren Tagen oder sogar Wochen einschleichen.
- Für Jahreszahlen vor Christus (negative Jahreszahlen) versagt der Trick, da Excel keine negativen Jahreszahlen verarbeitet.
4. Fehlerbehandlung: Robuste Formeln mit WENNFEHLER
Was passiert, wenn jemand ein ungültiges Datum eingibt (z. B. 32.01.1800)? Die DATUM-Funktion „rollt“ dann auf den nächsten gültigen Tag weiter, was zu stillen Fehlern führen kann. Schützen Sie Ihre Formel mit WENNFEHLER:
=WENNFEHLER(DATUM(RECHTS(A2;4)+400; TEIL(A2;…); LINKS(A2;…)); „Ungültiges Datum“)
5. Optik vs. Berechnung
Das Hilfsdatum (z. B. im Jahr 2150) dient nur der Kalkulation. Für die Anzeige im Blog oder Bericht sollten Sie immer das ursprüngliche Textdatum verwenden – oder das Ergebnis per Formel wieder als Text „zurück-formatieren“:
=TAG(Hilfsdatum) & „.“ & MONAT(Hilfsdatum) & „.“ & (JAHR(Hilfsdatum)-400)
9. Best Practice: Saubere Modellstruktur
Für professionelle Modelle empfehle ich:
- Originaldaten (Text) in Spalte A.
- Hilfsspalten für das transformierte Datum (+400/800 Jahre) in Spalte B. Tipp: Den Verschiebungswert (z. B. 400) in einer separaten Zelle (z. B. $E$1) ablegen, damit er zentral änderbar bleibt.
- Berechnungen finden nur in Spalte C (basierend auf Spalte B) statt. Das erhöht Transparenz und Wartbarkeit enorm.
Zusätzlich können Sie mit den Hilfsdaten (Spalte B) ganz normal sortieren, filtern und sogar Zeitachsen-Diagramme erstellen – die Achsenbeschriftung ersetzen Sie dann einfach durch die Originaldaten aus Spalte A.
10. Fazit
Datumsangaben vor 1900 sind in Excel kein echtes Hindernis – wenn Sie die richtige Technik kennen. Mit dem 400-Jahre-Trick verwandeln Sie Excel von einem reinen Business-Tool in ein mächtiges Werkzeug für historische Analysen und komplexe Zeitreihen. Beachten Sie dabei die Grenzen des Tricks (gregorianischer Kalender ab 1582, unterschiedliche Einführungsdaten in verschiedenen Ländern, keine negativen Jahreszahlen, Obergrenze 31.12.9999), dann steht Ihren Berechnungen nichts im Wege.
11. Beispieldatei mit Formeln
Navigation innerhalb der Formel-Serie
- ⬅️ Vorheriger Beitrag: F1 – Quartal aus Datum in römischen Zahlen darstellen – Excel-Formel
- ➡️ Nächster Beitrag: F3 – Excel Datum und Zeit: So funktioniert das interne System wirklich
- 🧭 Zur Übersicht: Excel Formeln & Berechnungen – Praxisbeispiele und Funktionen verständlich erklärt
Austausch & Fragen
Haben Sie die Formel ausprobiert? Schreiben Sie mir gerne auf Linkedin, wie Sie Datumswerte vor 1900 in Excel bearbeiten – ich freue mich auf den Austausch. Den Link zu meiner LinkedIn-Gruppe finden Sie hier: https://www.linkedin.com/groups/13079313