Text in Datum umwandeln

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

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

Diese Formel extrahiert die Jahres-, Monats- und Tageswerte separat und verwendet die DATE-Funktion, um sie zum 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 Datumsangaben stattdessen wie Text behandelt werden. Beispielsweise könnten Sie Textwerte wie diese haben:

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

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

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

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 einzugeben:

um herauszufinden, wie viele Zellen in einem Bereich Werte enthalten

 
= DATE (year,month,day)

Die LINKS-Funktion extrahiert die 4 Zeichen ganz links für das Jahr, die MID-Funktion extrahiert Zeichen in den Positionen 5-6 für den Monat und die RECHTE 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 ist beispielsweise dd.mm.yyyy und die Formel in C8 lautet:

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

Langer Text

Manchmal haben Sie möglicherweise Datumsangaben in einer längeren Form wie 'Apr 11 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 dem korrekt erkennt ERSATZ-Funktion . Die folgende Formel ersetzt die zweite Instanz eines Leerzeichens (' ') durch ein Komma und ein Leerzeichen (', '):

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

Sobald wir das Komma nach dem Monatsnamen hinzufügen, versteht Excel das Datum, aber es braucht noch einen kleinen 'Kick'. Deshalb fügen wir am Ende Null hinzu. Die mathematische Operation veranlasst Excel, zu versuchen, die Zeichenfolge in eine Zahl zu konvertieren. Dies führt bei Erfolg zu einem gültigen Excel-Datum. Beachten Sie, dass Sie möglicherweise das Datum anwenden müssen Zahlenformatierung um das Datum richtig anzuzeigen.

Ohne Formeln

Bevor Sie eine Formel zum manuellen Analysieren und Erstellen eines Datums aus Text verwenden, probieren Sie eine der folgenden Korrekturen aus. Die erste Option verwendet eine mathematische Operation, um Excel ein wenig zu 'anstoßen' und zu zwingen, den Text als Zahl auszuwerten. Weil Excel-Daten sind tatsächlich Zahlen , dies kann 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 zu fixieren

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

Die Vlookup-Funktion von Excel wird höchstwahrscheinlich?

Um Datumsangaben durch Hinzufügen von Null umzuwandeln, versuchen Sie es Spezial einfügen :

Wo ist Flash Fill in Excel

1. Geben Sie Null (0) in eine unbenutzte Zelle ein und kopieren Sie sie in die Zwischenablage
2. Wählen Sie die problematischen Daten aus
3. Inhalte einfügen > Werte > Hinzufügen
4. Anwenden eines Datumsformats (falls erforderlich)

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

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

wobei A1 ein unbekanntes Datum enthält.

Text in Spalten, um Daten zu fixieren

Eine andere Möglichkeit, Excel dazu zu bringen, Datumsangaben zu erkennen, ist die Verwendung der Text in Spalten Besonderheit:

Wählen Sie die Datumsspalte aus und versuchen Sie es dann mit Daten > Text in Spalten > Fest > Fertig

Wenn Excel die Daten erkennt, werden sie alle in einem Schritt korrigiert.