Es gibt viele Möglichkeiten, als Text formatierte Datumsangaben in Excel in tatsächliche Datumsangaben zu konvertieren, und dieses Tutorial soll sie alle erkunden.
Wenn Sie Daten in Excel importieren, können sie in vielen verschiedenen Formen vorliegen, die Excel nicht erkennt. Wenn Daten aus einer externen Quelle importiert werden, werden sie manchmal als Text formatiert.
Es ist auch wichtig zu wissen, dass Excel Datumsformate basierend auf Ihren Systemregionseinstellungen anwendet. Wenn Sie also Daten in Excel importieren, die aus einem anderen Land stammen, erkennt Excel diese möglicherweise nicht und speichert sie als Texteinträge.
Wenn dieses Problem auftritt, gibt es viele Möglichkeiten, wie Sie Text in Excel in ein Datum konvertieren können, und wir werden sie alle in diesem Tutorial behandeln.
Methoden zum Konvertieren von Text in Datum
Wenn Ihr Arbeitsblatt als Text formatierte Datumsangaben anstelle von tatsächlichen Datumsangaben enthält, können diese nicht in Berechnungen verwendet werden. Sie müssen sie also wieder in tatsächliche Daten umwandeln.
Wenn Sie Ihre Datumsangaben linksbündig sehen, bedeutet dies, dass sie als Text formatiert sind, da Texte standardmäßig linksbündig ausgerichtet sind. Und die Zahl und das Datum sind immer rechtsbündig.
Es gibt einige verschiedene Möglichkeiten, wie Sie Text in Excel in ein Datum umwandeln können:
- Fehlerüberprüfungsoption
- Excel-Funktion für Text in Spalten
- Ersatz finden
- Spezialwerkzeug einfügen
- Excel Formel und Funktionen
Konvertieren von Text in Datum mit der Fehlerüberprüfungsoption
Excel verfügt über eine integrierte Fehlerüberprüfungsfunktion, die einige offensichtliche Fehler in Ihren Daten erkennt. Wenn es einen Fehler findet, zeigt es Ihnen ein kleines grünes Dreieck (eine Fehleranzeige) in der oberen linken Ecke der Zelle an, die den Fehler enthält. Wenn Sie diese Zelle auswählen, wird ein Warnzeichen mit einem gelben Ausrufezeichen angezeigt. Wenn Sie Ihren Cursor über dieses Zeichen bewegen, informiert Excel Sie über das mögliche Problem mit dieser Zelle.
Wenn Sie beispielsweise das Jahr zweistellig in Ihr Datum eingeben, nimmt Excel dieses Datum als Text an und speichert es als Text. Und wenn Sie diese Zelle auswählen, wird ein Ausrufezeichen mit einer Warnung angezeigt: "Diese Zelle enthält eine Datumszeichenfolge, die nur aus zwei Ziffern des Jahres besteht".
Wenn Ihre Zellen diesen Fehlerindikator aufweisen, klicken Sie auf das Ausrufezeichen, und es werden einige Optionen zum Konvertieren von als Text formatierten Datumsangaben in tatsächliche Datumsangaben angezeigt. Excel zeigt Ihnen Optionen zum Konvertieren in 19XX oder 20XX (19XX für 1915, 20XX für 2015). Wählen Sie die entsprechende Option aus.
Dann wird der Text in das richtige Datumsformat umgewandelt.
So aktivieren Sie die Fehlerüberprüfungsoption in Excel
Normalerweise ist die Option Fehlerüberprüfung in Excel standardmäßig aktiviert. Wenn die Fehlerüberprüfungsfunktion für Sie nicht funktioniert, müssen Sie die Fehlerüberprüfung in Excel aktivieren.
Klicken Sie dazu auf die Registerkarte „Datei“ und wählen Sie im linken Bereich „Optionen“.
Klicken Sie im Fenster "Excel-Optionen" im linken Bereich auf "Formeln" und aktivieren Sie im rechten Bereich "Fehlerprüfung im Hintergrund aktivieren" im Abschnitt Fehlerprüfung. Und kreuzen Sie im Abschnitt Fehlerüberprüfungsregeln das Feld „Zellen mit zweistelligen Jahreszahlen“ an.
Konvertieren von Text in Datum mit Excel-Text-in-Spalten-Funktion
Text in Spalten ist eine großartige Funktion in Excel, mit der Sie Daten in mehrere Spalten aufteilen können, und es ist auch ein leistungsstarkes Werkzeug zum Konvertieren von Textwerten in Datumswerte. Diese Methode erkennt mehrere verschiedene Datenformate und konvertiert sie in das richtige Datumsformat.
Konvertieren einfacher Textzeichenfolgen in Datumsangaben
Angenommen, Ihre Daten sind in Textzeichenfolgen wie folgt formatiert:
Sie können den Text-in-Spalten-Assistenten verwenden, um sie alle schnell wieder in Datumsangaben umzuformatieren.
Wählen Sie zunächst den Bereich der Texteinträge aus, die Sie in Datumsangaben umwandeln möchten. Gehen Sie dann in der Multifunktionsleiste zur Registerkarte "Daten" und klicken Sie in der Gruppe "Datentools" auf die Option "Text in Spalten".
Der Text-in-Spalten-Assistent wird angezeigt. Wählen Sie in Schritt 1 des Text-in-Spalte-Assistenten die Option „Getrennt“ unter Originaldatentyp und klicken Sie auf „Weiter“.
Deaktivieren Sie in Schritt 2 alle Kontrollkästchen „Trennzeichen“ und klicken Sie auf Weiter.
Wählen Sie im letzten Schritt des Assistenten „Datum“ unter Spaltendatenformat und wählen Sie Ihr Datumsformat aus der Dropdown-Liste neben „Daten“ und klicken Sie auf die Schaltfläche „Fertig stellen“. In unserem Fall konvertieren wir die Textdaten, die als „01 02 1995“ (Tag Monat Jahr) dargestellt werden, also wählen wir „DMY“ aus der Dropdown-Liste „Datum:“.
Jetzt wandelt Excel Ihre Textdaten in tatsächliche Daten um und zeigt sie rechtsbündig in den Zellen an.
Notiz: Bevor Sie mit der Verwendung der Text-to-Spalte-Funktion beginnen, stellen Sie sicher, dass alle Ihre Textzeichenfolgen ein identisches Format haben, da sonst Texte nicht konvertiert werden. Wenn beispielsweise einige Ihrer Textdaten wie das Format Monat/Tag/Jahr (MDY) formatiert sind, während andere Tag/Monat/Jahr (DMY) sind, und wenn Sie in Schritt 3 „DMY“ auswählen, erhalten Sie falsche Ergebnisse. Wie im unteren Bild gezeigt.
Konvertieren einer komplexen Textzeichenfolge in ein Datum
Die Funktion "Text in Spalten" ist praktisch, wenn Sie komplexe Textzeichenfolgen in Datumsangaben konvertieren möchten. Sie können die Trennzeichen verwenden, um zu identifizieren, wo Ihre Daten aufgeteilt und in 2 oder mehr Spalten angezeigt werden sollen. Und kombinieren Sie die geteilten Teile von Datumsangaben mit der DATE-Funktion zu einem ganzen Datum.
Wenn Ihre Datumsangaben beispielsweise in mehrteiligen Textzeichenfolgen angezeigt werden, wie folgt:
Mittwoch, 01. Februar 2020
01. Februar 2020, 16.10 Uhr
Sie können den Text-in-Spalten-Assistenten verwenden, um die durch ein Komma getrennten Tag-, Datums- und Uhrzeitinformationen zu trennen und sie über mehrere Spalten hinweg anzuzeigen.
Wählen Sie zunächst alle Textzeichenfolgen aus, die Sie in Datumsangaben umwandeln möchten. Klicken Sie auf der Registerkarte "Daten" auf die Schaltfläche "Text in Spalten". Wählen Sie in Schritt 1 des Text-in-Spalte-Assistenten die Option „Getrennt“ unter Originaldatentyp und klicken Sie auf „Weiter“.
Wählen Sie in Schritt 2 des Assistenten die Trennzeichen aus, die Ihre Textzeichenfolgen enthalten, und klicken Sie auf „Weiter“. Unsere Beispieltextzeichenfolgen durch Komma und Leerzeichen getrennt – „Montag, 01. Februar 2015, 13:00 Uhr“. Wir sollten „Komma“ und „Leerzeichen“ als Trennzeichen wählen, um die Textzeichenfolgen in mehrere Spalten aufzuteilen.
Wählen Sie im letzten Schritt das Format „Allgemein“ für alle Spalten im Bereich Datenvorschau. Geben Sie im Feld „Ziel“ an, wo die Spalten eingefügt werden sollen, andernfalls werden die Originaldaten überschrieben. Wenn Sie einen Teil der Originaldaten ignorieren möchten, klicken Sie im Abschnitt Datenvorschau darauf und wählen Sie die Option „Spalte nicht importieren (überspringen)“. Klicken Sie dann auf „Fertig stellen“.
Jetzt werden die Datumsteile (Wochentage, Monat, Jahr, Uhrzeit) in die Spalten B, C, D, E, F und G aufgeteilt.
Kombinieren Sie dann die Datumsteile mit Hilfe einer DATE-Formel, um das gesamte Datum zu erhalten.
Die Syntax der Excel-DATUM-Funktion:
=DATUM(Jahr,Monat,Tag)In unserem Beispiel befinden sich die Teile für Monat, Tag und Jahr in den Spalten C, D bzw. E.
Die DATUM-Funktion erkennt nur Zahlen und keinen Text. Da unsere Monatswerte in Spalte C alle Textzeichenfolgen sind, müssen wir sie in Zahlen umwandeln. Dazu müssen Sie mit der Funktion MONAT den Namen des Monats in die Zahl des Monats ändern.
Um den Namen eines Monats in eine Monatszahl umzuwandeln, verwenden Sie diese MONAT-Funktion innerhalb der DATE-Funktion:
=MONAT(1&C1)Die Funktion MONAT fügt 1 zu Zelle C2 hinzu, die den Namen des Monats enthält, um den Monatsnamen in die entsprechende Monatsnummer umzuwandeln.
Dies ist die DATE-Funktion, die wir verwenden müssen, um Datumsteile aus verschiedenen Spalten zu kombinieren:
=DATUM(E1,MONAT(1&C1),D1)
Verwenden Sie nun den Ausfüllpunkt in der unteren Ecke der Formelzelle und wenden Sie die Formel auf die Spalte an.
Konvertieren von Text in Datum mit der Methode Suchen und Ersetzen
Diese Methode verwendet Trennzeichen, um das Textformat in Datumsangaben zu ändern. Wenn Tag, Monat und Jahr in Ihren Daten durch ein anderes Trennzeichen als einen Bindestrich (-) oder Schrägstrich (/) getrennt sind, erkennt Excel sie nicht als Datum und speichert sie als Text.
Um dieses Problem zu beheben, verwenden Sie die Funktion Suchen und Ersetzen. Durch Ändern der nicht standardmäßigen Periodentrennzeichen (.) mit Schrägstrichen (/) oder Bindestrichen (-) ändert Excel die Werte automatisch in Datumsangaben.
Wählen Sie zunächst alle Textdaten aus, die Sie in Daten umwandeln möchten. Klicken Sie auf der Registerkarte "Startseite" auf die Schaltfläche "Suchen & Auswählen" in der rechten Ecke des Menübands und wählen Sie "Ersetzen". Drücken Sie alternativ Strg+H um das Dialogfeld Suchen und Ersetzen zu öffnen.
Geben Sie im Dialogfeld Suchen und Ersetzen das Trennzeichen ein, das Ihr Text enthält (in unserem Fall Punkt (.) in das Feld "Suchen nach" und einen Schrägstrich (/) oder Bindestrich (-) in das Feld "Ersetzen durch".) Klicken Sie auf die Schaltfläche „Alle ersetzen“, um die Trennzeichen zu ersetzen, und klicken Sie auf „Schließen“, um das Fenster zu schließen.
Excel erkennt jetzt, dass Ihre Textzeichenfolgen jetzt Datumsangaben sind, und formatiert sie automatisch als Datumsangaben. Ihre Daten werden wie unten gezeigt richtig ausgerichtet.
Konvertieren von Text in Datum mit dem Spezialwerkzeug Einfügen
Eine andere schnelle und einfache Möglichkeit, Textzeichenfolgen in Datumsangaben zu konvertieren, besteht darin, 0 mit der Option „Spezial einfügen“ zur Textzeichenfolge hinzuzufügen. Wenn Sie dem Wert Null hinzufügen, wird der Text in die Seriennummer des Datums konvertiert, die Sie als Datum formatieren können.
Wählen Sie zuerst eine leere Zelle aus und kopieren Sie sie (wählen Sie sie aus und drücken Sie Strg + C Kopieren).
Wählen Sie dann die Zellen aus, die die Textdaten enthalten, die Sie konvertieren möchten, klicken Sie mit der rechten Maustaste und wählen Sie die Option "Spezial einfügen".
Wählen Sie im Dialogfeld „Inhalte einfügen“ im Abschnitt „Einfügen“ die Option „Alle“, im Abschnitt „Vorgang“ die Option „Hinzufügen“ und klicken Sie auf „OK“.
Sie können auch andere arithmetische Operationen ausführen, den Wert in der Zielzelle mit dem eingefügten Wert subtrahieren/multiplizieren/dividieren (wie Zellen mit 1 multiplizieren oder durch 1 dividieren oder Null subtrahieren).
Wenn Sie in Operation „Hinzufügen“ auswählen, wird allen ausgewählten Textdaten „Null“ hinzugefügt, da das Hinzufügen einer „0“ die Werte nicht ändert, erhalten Sie die Seriennummer für jedes Datum. Jetzt müssen Sie nur noch das Format der Zellen ändern.
Wählen Sie die Seriennummern aus und klicken Sie auf der Registerkarte "Startseite" auf die Dropdown-Liste "Zahlenformat" in der Gruppe Nummer. Wählen Sie die Option „Kurzes Datum“ aus der Dropdown-Liste.
Wie Sie jetzt sehen können, sind die Zahlen als Datum formatiert und rechtsbündig ausgerichtet.
Konvertieren von Text in Datum mit Formeln
Es gibt zwei Funktionen, die hauptsächlich zum Konvertieren von Text in ein Datum verwendet werden: DATEVALUE und VALUE.
Verwenden der Excel-DATEVALUE-Funktion
Die Excel-DATEVALUE-Funktion ist eine der einfachsten Möglichkeiten, ein als Text dargestelltes Datum in die Seriennummer eines Datums umzuwandeln.
Die Syntax der DATEVALUE-Funktion:
=DATUMSWERT(date_text)Streit: date_text gibt die Textzeichenfolge an, die Sie umhüllen möchten oder auf die Zelle verweisen möchten, die Textdaten enthält.
Die Formel:
=DATUMSWERT(A1)Das folgende Bild zeigt, wie die Funktion DATEVALUE einige verschiedene Datumsformate handhabt, die als Text gespeichert werden.
Sie haben die Datumsseriennummern erhalten, jetzt müssen Sie das Datumsformat auf diese Nummern anwenden. Wählen Sie dazu die Zellen mit Seriennummern aus, gehen Sie dann zur Registerkarte „Startseite“ und wählen Sie „Kurzes Datum“ aus der Dropdown-Liste „Zahlenformat“.
Jetzt haben Sie Ihre formatierten Daten in Spalte C.
Auch wenn Ihr Textdatum (A8) keinen Jahresanteil enthält, verwendet DATEVALUE das aktuelle Jahr aus der Uhr Ihres Computers.
Die DATEVALUE-Funktion konvertiert nur die Textwerte, die wie ein Datum aussehen. Es kann keinen Text, der einer Zahl ähnelt, in ein Datum umwandeln, noch kann es einen Zahlenwert in ein Datum ändern, dafür benötigen Sie die VALUE-Funktion von Excel.
Verwenden der Excel-WERT-Funktion
Die Excel-WERT-Funktion ist in der Lage, jede Textzeichenfolge, die einem Datum oder einer Zahl ähnelt, in einen numerischen Wert umzuwandeln.
Die WERT-Funktion:
=WERT(Text)Text– die Textzeichenfolge, die wir konvertieren möchten, oder auf die Zelle verweisen, die die Textzeichenfolge enthält.
Die Beispielformel zum Konvertieren des Textdatums:
=WERT(A1)Die folgende VALUE-Formel kann alle Textzeichenfolgen, die wie ein Datum aussehen, in eine Zahl umwandeln, wie unten gezeigt.
Die Funktion VALUE unterstützt jedoch nicht alle Arten von Datumswerten. Wenn Datumsangaben beispielsweise Dezimalstellen (A11) verwenden, wird #VALUE! Error.
Sobald Sie die Seriennummer für Ihr Datum haben, müssen Sie die Zelle mit der Datumsseriennummer formatieren, damit sie wie ein Datum aussieht, wie wir es für die DATEVALUE-Funktion getan haben. Wählen Sie dazu die Seriennummern aus und wählen Sie die Option „Datum“ aus dem Dropdown-Menü „Zahlenformat“ auf der Registerkarte „Startseite“.
Das ist es, dies sind die 5 verschiedenen Möglichkeiten, wie Sie Datumsangaben, die als Text formatiert sind, in Datumsangaben in Excel konvertieren können.