Excel-VBA - Datums-Format-Konvertierung

Ich haben eine herausfordernde Aufgabe, die ich nicht imstande bin zu lösen mit vielen workarounds.

In einer Spalte habe ich Daten, das Datum kann in folgenden drei Formaten:

1) Einfaches dd/mm/yy

2) TT/mm/JJ aber können die Worte "vor,nach oder" um ihn herum. Jeder
einer davon, und wir brauchen nur diese Worte zu löschen in diesem Fall.

3) Datum in einem numerischen format. Eine lange dezimal-Werte, wie 1382923.2323
aber eigentlich kann ich ein Datum von es nach der Konvertierung.

Die Datei hier hochgeladen. Date_format_macro_link

Schrieb ich folgenden code, aber es ' s falsche Ergebnisse.

Sub FormatDates_Mine()
    ManualSheet.Activate
    ManualSheet.Cells.Hyperlinks.Delete
    ManualSheet.Cells.Interior.ColorIndex = xlNone
    ManualSheet.Cells.Font.Color = RGB(0, 0, 0)

    lastRow = ManualSheet.Range("A" & Rows.Count).End(xlUp).Row
    Col = "A"
    For i = 2 To lastRow
        Cells(i, Col) = Trim(Replace(Cells(i, Col), vbLf, "", 1, , vbTextCompare))

        If InStr(1, Cells(i, Col), "about", vbTextCompare) <> 0 Then
            Cells(i, Col) = Trim(Replace(Cells(i, Col), "about", "", 1, , vbTextCompare))
            Cells(i, Col).Interior.Color = RGB(217, 151, 149)
        End If

        If InStr(1, Cells(i, Col), "after", vbTextCompare) <> 0 Then
            Cells(i, Col) = Trim(Replace(Cells(i, Col), "after", "", 1, , vbTextCompare))
            Cells(i, Col).Interior.Color = RGB(228, 109, 10)
        End If

        If InStr(1, Cells(i, Col), "before", vbTextCompare) <> 0 Then
            Cells(i, Col) = Trim(Replace(Cells(i, Col), "before", "", 1, , vbTextCompare))
            Cells(i, Col).Interior.Color = RGB(228, 109, 10)
        End If

        DateParts = Split(Cells(i, Col), "/", , vbTextCompare)

        Cells(i, Col) = Format(Cells(i, Col), "dd/mm/yyyy")
    Next i

    Range("D:E").HorizontalAlignment = xlCenter
End Sub

Die Datei hier hochgeladen. Date_format_macro_link

Bitte um Hilfe!!

Sie haben erwähnt, wie deine Daten Aussehen, aber Sie haben nicht erwähnt, was Sie tun möchten, zu tun. Zum Beispiel, was passiert About 21/05/09 ? Aus deinem code sieht es aus wie Sie wollen, entfernen Sie alle unerwünschten chars und verwandeln Sie es in ein Datum?
Es gibt einige Werte in Ihren Daten, die nicht erfüllt data type Kriterien. Weglassen, das problem könnte man so etwas wie If IsDate(Cells(i, Col)) Then bevor Sie .Format Eigenschaft. Für andere Probleme, siehe @SiddharthRout Kommentar...
Ich denke, Sie tun konnte, dass mit nur einem Arbeitsblatt Formel...und legen die Farbe durch Bedingte Formatierung

InformationsquelleAutor Tejas | 2013-04-10

  1. 2

    Ist es das, was Sie versuchen? Ich habe noch keine Fehlerbehandlung. Ich gehe davon aus, dass Sie nicht abweichen, für das bestehende format der Daten. Wenn sich das format ändert, dann müssen Sie einführen Fehlerbehandlung.

    Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim rng As Range
    Dim MyAr() As String

    Set ws = ThisWorkbook.Sheets("Data")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rng = .Range("A2:A" & lRow)

        With rng
            '~~> Replace "After " in the entire column
            .Replace What:="After ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

            DoEvents

            '~~> Replace "About " in the entire column
            .Replace What:="About ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

            .NumberFormat = "dd/mm/yyyy"
        End With

        For i = 2 To lRow
            '~~> Remove the End Spaces
            .Range("A" & i).Value = Sid_SpecialAlt160(.Range("A" & i).Value)

            '~~> Remove time after the space
            If InStr(1, .Range("A" & i).Value, " ") Then _
            .Range("A" & i).Formula = Split(.Range("A" & i).Value, " ")(0)

            '~~> Convert date like text  to date
            .Range("A" & i).Formula = DateSerial(Split(.Range("A" & i).Value, "/")(2), _
                                                 Split(.Range("A" & i).Value, "/")(1), _
                                                 Split(.Range("A" & i).Value, "/")(0))
        Next i

    End With
End Sub

Public Function Sid_SpecialAlt160(s As String)
    Dim counter As Long

    If Len(s) > 0 Then
        counter = Len(s)
        While VBA.Mid(s, counter, 1) = " "
            counter = counter - 1
        Wend
        Sid_SpecialAlt160 = VBA.Mid(s, 1, counter)
    Else
        Sid_SpecialAlt160 = s
    End If
End Function

    Screenshot

    Excel-VBA - Datums-Format-Konvertierung

    +1 für Sid_SpecialAlt160 (und screenshots), und es funktioniert, aber konnte nicht gelöst werden, mithilfe von Arbeitsblatt Formeln?
    Sorry, aber ich weiß nicht das ist, was ich will. Ich möchte jede und jeden Termin werden Umgerechnet in mm/dd/yy. Wenn Sie sehen, dass die Zelle A1 aus, bevor es wurde, TT/m/JJJJ und TT/mm/JJ. Du hast meine Punkt? Dank und bitte versuchen Sie es erneut
    Wenn Sie es wollen, so mm/dd/yy dann ändern Sie einfach die Zeile .NumberFormat = "dd/mm/yyyy" in meinem obigen code .NumberFormat = "mm/dd/yy". Ich entschied mich für "dd/mm/yyyy", weil Sie Tat das gleiche in Ihren code in deiner Frage 😉
    Ok, danke für deine Bemühungen 🙂

    InformationsquelleAutor Siddharth Rout

Schreibe einen Kommentar