☰

Wie kann ich eine Zelle wie „12/04/2003“ in das Datumsformat konvertieren?

tag-icon • tag-icon microsoft-excel
Wie kann ich eine Zelle wie „12/04/2003“ in das Datumsformat konvertieren?

21.03.2004 wird nicht als Datum registriert, während 07.03.2004 als Datum registriert wird. Weiß jemand, wie ich alle Nullen aus Datumswerten entfernen kann, deren Monatswerte bei 0 beginnen?

Daher werden die Spalten, die am linken Rand angeklebt sind, nicht automatisch konvertiert. Bei den Spalten, die rechts angeklebt sind, ist das kein Problem.

http://puu.sh/27CEm

Antwort1

Normalerweise könnte dies gelöst werden, indem man die Funktion DATEVALUE verwendet, um ein Datum, das als Text vorliegt, in einen Excel-Datumswert umzuwandeln (also eine Zahl, die Excel als Datum erkennt). Die Verwendung wäre DATEVALUE(Datumszeichenfolge oder Zellenadresse).

Dies funktioniert hier jedoch nicht, da 1) in Ihren Beispielzeichenfolgen der Tag vor dem Monat steht und DATEVALUE erwartet, dass auf den Monat ein Tag folgt, und 2) bei einigen Datumszeichenfolgen auch eine Uhrzeit enthalten ist.

Hier ist eine Alternative zur Array-Formel, die mit den von Ihnen angegebenen Beispielen funktioniert. Sie muss mit der Tastenkombination Control- Shift- eingegeben werden.Enter

  =DATE(
        RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),

        LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),

        LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)))
        )

Diese sehr komplizierte Matrixformel (ich hoffe, jemand kann eine einfachere anbieten) verwendet die DATE-Funktion, um einen Excel-Datumswert zu konstruieren. (Ich hatte zufällig die ursprüngliche Datumszeichenfolge in Zelle F8, als ich das Datum erstellte.)

Die zu konvertierende Datumszeichenfolge kann nicht in ihrer Rohform verwendet werden, da sie manchmal eine Uhrzeit enthält. Stattdessen wird verwendet: RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8). Diese Formel sucht in der Datumszeichenfolge nach einem Leerzeichen. Wenn sie ein Leerzeichen findet, verwendet sie die Funktion LEFT, um alle Zeichen links vom Leerzeichen abzurufen. Wenn sie kein Leerzeichen findet, gibt sie einfach die ursprüngliche Zeichenfolge zurück.

Der äußerste Teil der Formel ist die DATE-Funktion: Wenn Jahr, Monat und Tag angegeben werden, ergibt sie ein Excel-Datum. Ich habe die Berechnung dieser drei Teile im obigen Code aufgeschlüsselt.

Die Jahresberechnung erfolgt wie folgt:

  1. Kehren Sie die Datumszeichenfolge um (wobei die Uhrzeit wie oben erläutert abgeschnitten wird).
  2. Suchen Sie mit der Suchfunktion die Position des ersten „/“ in dieser umgekehrten Zeichenfolge. (Dieser Schrägstrich ist tatsächlich der erste Schrägstrich in der nicht umgekehrten Datumszeichenfolge.)
  3. Nehmen Sie mit der Funktion RIGHT so viele Zeichen von der rechten Seite der Datumszeichenfolge.

Die Monatsberechnung umfasst die Schritte:

  1. Suchen Sie die Position des ersten Schrägstrichs in dernicht rückgängig gemachtDatumszeichenfolge.
  2. Bestimmen Sie die Anzahl der Zeichen im Monat (das können 1 oder 2 sein), indem Sie die Differenz zwischen dieser Position und der Position des zweiten "/" berechnen, die bei der Jahresberechnung ermittelt wurde.
  3. Verwenden Sie die MID-Funktion, um die Zeichen zwischen dem ersten und dem zweiten Schrägstrich zurückzugeben.

Die Tagesberechnung ist die einfachste.

  1. Berechnen Sie die Position des ersten "/" mit der gleichen Methode wie bei der Monatsberechnung.
  2. Nehmen Sie mit der Funktion LEFT so viele Zeichen (minus 1) von der linken Seite der Zeichenfolge.

Es gibt weniger aufwändige Möglichkeiten, zum Ergebnis zu gelangen. Die einfachste davon wäre zweifellos die Verwendung des Befehls „Text in Spalten“ auf der Registerkarte „Daten“ des Menübands, wie von @Barry vorgeschlagen.

Eine andere Möglichkeit ist die Verwendung einer REGEX-Mustervergleichsfunktion. Diese Funktion ist nicht in Excel integriert, kann aber mit VBA-Code erstellt und aufgerufen werden. Obwohl der Code ein wenig knifflig ist, sind mehrere Versionen im Internet verfügbar (z. B.dieses Beispiel). Die Verwendung der Funktion wäre jedoch sehr einfach. Der Übereinstimmungsausdruck zum Abrufen des Tages könnte beispielsweise so aussehen REGEXP("./",F8), was dann in der DATE-Funktion verwendet werden könnte, um den Excel-Datumswert abzurufen.

Der Einfachheit halber finden Sie hier die Formel in ihrer vollständigen Form.

  =DATE(RIGHT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)),LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-1),MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1)),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-MATCH("/",MID(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),LEN(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))-ROW(1:25),1),0)-LEN(LEFT(IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8),SEARCH("/",IFERROR(LEFT(F8,IFERROR(SEARCH(" ",F8),0)-1),F8))+1))))

Antwort2

Der Wert, der auf der linken Seite klebt, bedeutet im Allgemeinen, dass es sich um einen Textwert handelt, während der numerische/Datumswert rechtsbündig ausgerichtet ist.

Vielleicht können Sie diese Dinge auch überprüfen, bevor Sie eine mögliche Datums-/Uhrzeitkonvertierung in Excel durchführen:

  1. Überprüfen Sie die Sprach-/Regionseinstellungen Ihres Systems, insbesondere Ihr Datums-/Uhrzeitformat. Excel verwendet die Standardeinstellungen Ihres Systems für Datum und Uhrzeit. Daher ist die Datums-/Uhrzeitkonvertierung für verschiedene Regionen unterschiedlich.

  2. Stellen Sie sicher, dass Ihrem Zellenwert keine zusätzlichen Leerzeichen oder versteckten Zeilenumbruchzeichen beigefügt sind. Ein gültiger Datums-/Uhrzeitwert mit einem Leerzeichen wird als Textwert und nicht als Datums-/Uhrzeitwert behandelt und daher von Excel nicht automatisch konvertiert.

  3. Verwenden Sie die benutzerdefinierte Zellenformatoption von Excel, wo Sie Ihre eigenen Konvertierungsregeln angeben können, beispielsweise TT/MM/JJJJ.

Antwort3

Das Quellformat ist vermutlich TT/MM/JJJJ. Versuchen Sie, die Funktion „Text in Spalten“ zum Konvertieren zu verwenden.

Datumsspalte auswählen

Daten > Text in Spalten > Weiter > Weiter > unter „Spaltendatenformat“ „Datum“ auswählen und das Quellformat aus der Dropdown-Liste „DMY“ auswählen > OK

Antwort4

Das Standarddatenformat in Excel ist: TT/M/JJJJ. Der linke Rand wird nicht automatisch konvertiert, weil Sie „TT/M/JJJJ“ wählen. Wählen Sie einfach das richtige Format, und das Problem ist gelöst.

verwandte Informationen