Excel-Formel: Text in Datum konvertieren -

Inhaltsverzeichnis

Generische Formel

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))

Zusammenfassung

Um Text in einem nicht erkannten Datumsformat in ein korrektes Excel-Datum zu konvertieren, können Sie den Text analysieren und ein korrektes Datum mit einer Formel zusammenstellen, die auf mehreren Funktionen basiert: DATUM, LINKS, MITTEL und RECHTS. Im gezeigten Beispiel lautet die Formel in C6:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Diese Formel extrahiert die Jahres-, Monats- und Tageswerte getrennt und verwendet die DATE-Funktion, um sie zu dem Datum 24. Oktober 2000 zusammenzusetzen.

Hintergrund

Wenn Sie mit Daten aus einem anderen System arbeiten, kann es vorkommen, dass Datumsangaben von Excel nicht richtig erkannt werden und die Daten stattdessen wie Text behandelt werden. Beispielsweise könnten Sie folgende Textwerte haben:

Text	Datum dargestellt
20001024	24. Oktober 2000
20050701	1. Juli 20115
19980424	24. April 1998
28.02.2014	28. Februar 2014

Wenn Excel einen Datumswert als Text ausgewertet hat, besteht eine Option darin, den Text mithilfe einer Formel in seine Komponenten (Jahr, Monat, Tag) zu analysieren und mit der Funktion DATE ein Datum zu erstellen. Wie oben erwähnt, empfehle ich Ihnen, zuerst die folgenden Lösungen auszuprobieren (Null hinzufügen und Spalten mit Text versehen), bevor Sie eine Formel verwenden. Beide Problemumgehungen sind schneller und erfordern weniger Aufwand.

Erläuterung

Die DATE-Funktion erstellt ein gültiges Datum mit drei Argumenten: Jahr, Monat und Tag:

=DATE(year,month,day)

In Zelle C6 verwenden wir die Funktionen LEFT, MID und RIGHT, um jede dieser Komponenten aus einer Textzeichenfolge zu extrahieren und die Ergebnisse in die DATE-Funktion einzuspeisen:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Die LEFT-Funktion extrahiert die 4 Zeichen ganz links für das Jahr, die MID-Funktion extrahiert Zeichen an den Positionen 5-6 für den Monat und die RIGHT-Funktion extrahiert die 2 Zeichen ganz rechts als Tag. Jedes Ergebnis wird direkt an die DATE-Funktion zurückgegeben. Das Endergebnis ist ein korrektes Excel-Datum, das beliebig formatiert werden kann.

Dieser Ansatz kann nach Bedarf angepasst werden. Das nicht erkannte Datumsformat in Zeile 8 lautet beispielsweise TT.MM.JJJJ und die Formel in C8 lautet:

=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))

Langtext

Manchmal haben Sie Daten in einer längeren Form wie "11. April 2020 08:43:13", die Excel nicht richtig erkennt. In diesem Fall können Sie die Zeichenfolge möglicherweise so anpassen, dass Excel das Datum mit der Funktion SUBSTITUTE korrekt erkennt. Die folgende Formel ersetzt die zweite Instanz eines Leerzeichens ("") durch ein Komma und ein Leerzeichen (","):

=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month

Sobald wir das Komma nach dem Monatsnamen einfügen, versteht Excel das Datum, benötigt aber noch einen kleinen "Kick". Deshalb addieren wir am Ende Null. Die mathematische Operation veranlasst Excel, die Zeichenfolge in eine Zahl umzuwandeln. Bei Erfolg führt dies zu einem gültigen Excel-Datum. Beachten Sie, dass Sie möglicherweise die Datumsnummer formatieren müssen, um das Datum korrekt anzuzeigen.

Ohne Formeln

Bevor Sie eine Formel zum manuellen Parsen und Erstellen eines Datums aus Text verwenden, versuchen Sie eine der folgenden Korrekturen. Die erste Option verwendet eine mathematische Operation, um Excel ein wenig zu "stupsen" und es zu zwingen, den Text als Zahl auszuwerten. Da Excel-Daten tatsächlich Zahlen sind, kann dies oft den Trick tun. Möglicherweise müssen Sie ein Datumsformat anwenden, wenn die Vorgänge erfolgreich sind.

Fügen Sie Null hinzu, um Daten festzulegen

Manchmal stoßen Sie auf Daten in einem Textformat, das Excel erkennen sollte. In diesem Fall können Sie Excel möglicherweise zwingen, die Textwerte in Datumsangaben zu konvertieren, indem Sie dem Wert Null hinzufügen. Wenn Sie Null hinzufügen, versucht Excel, Textwerte zu Zahlen zu zwingen. Da Datumsangaben nur Zahlen sind, ist dieser Trick eine hervorragende Möglichkeit, Datumsangaben in ein Textformat zu konvertieren, das Excel wirklich verstehen sollte.

Um Datumsangaben durch Hinzufügen von Null zu konvertieren, versuchen Sie, Inhalte einfügen:

Geben Sie Null (0) in eine nicht verwendete Zelle ein und kopieren Sie sie in die Zwischenablage
Wählen Sie die problematischen Daten aus
Inhalte einfügen> Werte> Hinzufügen
Wenden Sie ein Datumsformat an (falls erforderlich)

Sie können in einer Formel wie der folgenden auch Null hinzufügen: