Mehrere Datumsformate in einer Spalte in Excel

678
Crops

Ich habe eine CSV-Datei mit einer Datumsspalte mit Datumsangaben in verschiedenen Formen wie folgt:

dd-mm-yyyy

yyyy-mm-dd

yyyy-mm

yyyy

Diese unregelmäßigen Daten befinden sich in einer einzelnen Spalte.

Wie konvertiere ich alle in das dd-mm-yyyyund extrahiere daraus das Jahr mithilfe der Excel-Formel? Die gewünschte Ausgabe lautet wie folgt:

Input Output 25-03-1954 25-03-1954 22-09-1987 22-09-1987 1990-01-25 25-01-1990 1968-11-15 15-11-1968 1919-01 01-01-1919 1873-02 01-02-1873 1945 01-01-1945 1933 01-01-1933
2
Was meinen Sie mit bedingt konvertieren? Zeigen Sie uns den Zustand! Rajesh S vor 6 Jahren 0
@RajeshS Was ich mit Bedingte meinte, war die Konvertierung in 'dd-mm-yyyy' entsprechend der Art der Eingabe in der Zelle. Crops vor 6 Jahren 0
Wenn Sie einfach konvertieren möchten, geben Sie die Eingabedaten ein. Rajesh S vor 6 Jahren 0
Und was meinen Sie damit "und extrahieren Sie das Jahr mit der Excel-Formel?" da Ihre Ausgabe alle 3 Datum, Monat und Jahr hat !! Rajesh S vor 6 Jahren 0
"TEXT" kann einzeln verwendet werden, wenn ich das Format der Eingabe kenne. Ich habe Tausende von Eingabezellen (in mehreren Formaten), die ich in eine Ausgabe konvertieren möchte. Ich kann die Ausgabe jedoch nicht abrufen. Sobald ich die Ausgabe erhalten habe, kann ich das Jahr mit `YEAR ()` extrahieren Crops vor 6 Jahren 0
Angenommen, Ihre Eingabe befindet sich in Spalte A, dann schreiben Sie diese in ** B2 = Text (a2, "DD-MM-YYYY") ** und füllen Sie sie aus. Wenn Sie alles im schnellsten Modus ausführen möchten, ist Makro erforderlich. Rajesh S vor 6 Jahren 0
Repräsentiert diese Liste alle möglichen Formate in Ihrer Spalte? Ron Rosenfeld vor 6 Jahren 0
@RonRosenfeld Ja, das sind alle Formate. Crops vor 6 Jahren 0

2 Antworten auf die Frage

1
robinCTS

Da Ihre Eingabedaten Datumsangaben vor 1900 enthalten, wird es etwas schwieriger. Der einfachste Weg, um damit umzugehen, besteht darin, ausschließlich mit den Textdarstellungen der Datumsangaben zu arbeiten.

Wenn Sie nicht das vollständige Datum benötigen, sondern nur das Jahr, geben Sie diese Grundformel in eine beliebige Zelle in der Zeile ein 2:2:

=IFERROR(VALUE(LEFT(A2,4)),VALUE(RIGHT(A2,4)))

Für das vollständige Datum ist die Formel etwas komplizierter:

=IF(MID(A2,3,1)="-",A2,CHOOSE((LEN(A2)-4)/3+1,"01-01","01"&MID(A2,5,3),RIGHT(A2,2)&MID(A2,5,3))&"-"&LEFT(A2,4))

Erläuterung:

Die verifizierte Version der vollständigen Datumsformel lautet wie folgt:

= IF( MID(A2,3,1)="-", A2, CHOOSE( (LEN(A2)-4)/3+1, "01-01", "01"&MID(A2,5,3), RIGHT(A2,2)&MID(A2,5,3) ) &"-"&LEFT(A2,4) )

Das ist ziemlich einfach. Die einzige etwas knifflige Sache ist das erste Argument der CHOOSE()Funktion (LEN(A2)-4)/3+1,. Dies ordnet die Länge der eingegebenen Datumszeichenfolgen 1-basierten Indizes zu, dh → → .[yyyy, yyyy-mm, yyyy-mm-dd, dd-mm-yyyy][4, 7, 10, 10][1, 2, 3, 3]

Anmerkungen:

  • Die verettete Formel funktioniert tatsächlich, wenn sie eingegeben wird.

Vorsichtsmaßnahmen:

  • Die Formeln setzen voraus, dass alle Eingabedaten als Text gespeichert werden. (Die Formeln können leicht geändert werden, um Datumsangaben zu speichern, die als Seriennummern gespeichert sind.)
  • Wenn die Eingabedaten führende / nachgestellte Leerzeichen enthalten, funktionieren die obigen Formeln möglicherweise nicht ordnungsgemäß. Sie werden definitiv nicht mit Leerzeichen (oder anderen Zeichen) an anderen Stellen in den Datumszeichenfolgen arbeiten. Wenn Sie alle A2s in den Formeln durch TRIM(A2)oder besser noch durch ersetzen SUBSTITUTE(A2," ",""), wird dieses Problem gelöst.
1
Ron Rosenfeld

Wenn Sie wirklich nur das Jahr benötigen, verwenden Sie die @robinCTS-Formel.

WENN Sie möchten, dass das Ergebnis "echte Excel-Daten" ist und

  • wenn Ihre Daten TEXT sind und keine echten Daten,
  • und wenn Ihre regionalen Datumseinstellungen sind DMY,
  • und die Spalte wird als formatiert dd-mm-yyyy

Dann könnten Sie diese Formel verwenden:

=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")

Beachten Sie, dass Datumsangaben vor 1900 keine echten Daten in Excel sein können, aber als TEXT-Zeichenfolgen dargestellt werden können. Ich überlasse es Ihnen, die Formel zu optimieren.

Wenn Ihre Ausgabe für Länder mit anderen Datumsformaten international bekannt sein muss, ist eine VBA-Lösung möglicherweise die beste Lösung. Wenn dies nicht möglich ist, können Helfer-Spalten verwendet werden, um die Daten aufzuteilen und anschließend wieder richtig zusammenzusetzen, oder es kann eine sehr komplexe Formel verwendet werden, um dies zu tun.

Beachten Sie, dass diese UDF abhängig vom zweiten optionalen Argument entweder das tatsächliche Datum oder nur das Jahr ausgibt.

Option Explicit Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant Dim V Dim dtTemp As Date  V = Split(S, "-") Select Case UBound(V) Case 0 dtTemp = DateSerial(V(0), 1, 1) Case 1 dtTemp = DateSerial(V(0), V(1), 1) Case 2 If Len(V(0)) = 4 Then dtTemp = CDate(S) Else dtTemp = DateSerial(V(2), V(1), V(0)) End If End Select  If Yr = True Then ConvertToDate = Year(dtTemp) Else If Year(dtTemp) < 1900 Then ConvertToDate = Format(dtTemp, "dd-mm-yyyy") Else ConvertToDate = dtTemp End If End If  End Function

Beiden Methoden Blick auf dem ersten Segment, und nehmen an, es ist ein Jahr, wenn LEN > 4, und dann schauen, wie viele Segmente zu entscheiden, ob die notwendigen anhänge -01‚s

Die VBA-Lösung gibt Datumsangaben vor 1900 als Textzeichenfolge aus.

OP hat nie für VBA gefordert, OP braucht Excel-Formel !! Rajesh S vor 6 Jahren 0
@RajeshS Und das war der erste Teil der Antwort. Ich habe aber auch andere Möglichkeiten besprochen. Dies ist nicht nur ein kostenloser "Code für mich" -Dienst, sondern dient dazu, sowohl dem OP als auch anderen Informationen zur Verfügung zu stellen, die anschließend in der Datenbank nach Antworten suchen. Daher können sowohl Erklärungen der Algorithmen als auch alternative Methoden zur Problemlösung hilfreich sein. Ron Rosenfeld vor 6 Jahren 1

Verwandte Probleme