Datum umwandeln in VBA

[ChickenWing]

Hallo ihr Lieben,

ich habe hier etwas mit Daten zu kämpfen. Zur Erklärung:

Ich arbeite an einem Tool, dass sich um den Import und Weiterverarbeitung von Daten in eine Tabellen kümmert. Mein aktuelles Problem damit ist, dass es sich hierbei auch um 2 Datumsfelder handelt.
Mein erster Gedanke war "Kein Problem, Excel kann das.", aber dann habe ich gesehen wie die bei mir ankommende Daten aussehen.

In den Feldern sind keine Daten im Sinne von "dd.mm.yyy" oder ähnlichem sondern "yyyymm" und ja der Punkt in der Mitte fehlt extra.
Ich hab mit ein paar Gedanken dazu gemacht, etwas Google geqäult(hoch lebe die heilige Suchmaschine) und habe mir einen kleinen Code aus einem Forum angepasst, weil mir selber auch nichts dolleres eingefallen ist.
Das ganze sieht jetzt so aus:

Code: [Auswählen]

Sub DatumUmbauen()
Dim Jahr As String
Dim Monat As String
Dim i As Integer
Dim ZeilenZahl As Integer

i = 1
    With ThisWorkbook.Worksheets("Datenimport")
         ZeilenZahl = .Range("J2:J" & .Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Count
     End With

MsgBox ZeilenZahl

While i <> ZeilenZahl + 1

Jahr = Left(Cells(i + 1, 10).Value, 4)
Monat = Right(Cells(i + 1, 10).Value, 2)

Sheets("Datenimport").Cells(i + 1, 10).Value = "01." & Monat & "." & Jahr
i = i + 1
Wend

Das macht auch soweit erstmal das was es soll, ABER:

Das dauert ewig. Die Makros die mir die Daten an sich anpassen, einfügen etc. brauchen eh schon eine ganze weile weil ich im mittleren vierstelligem Bereich unterwegs bin, ab alles noch im Rahmen. Der Code her braucht aber entschieden zu lange und ich hoffe eine schlankere/schnellere Lösung zu finden, da er nur ca. 2 Felder pro Sekunde ändert. Das bei 5000-6000 Datensätzen und den Spaß noch mal 2 weil er 2 Spalten damit überarbeiten muss... Hooyah, der Arbeitstag ist um bis er fertig gerechnet hat.

Von daher meine Frage: Bekomme ich es irgendwie schneller hin? Ich habe schon überlegt eine weitere Spalte anzulegen und es über eine direkte Excel Formel machen lasse und dann die Spalten einfach überschreibe. Würde das helfen...? So nach dem Motto: =Left... etc. und dann:

Code: [Auswählen]

Sheets("Datenimport").Range("M").Copy _
Sheets("Datenimport").Range("L")

Hoffe mir kann und mag jemand helfen, schonmal im voraus vielen Dank =)


Grüße aus Mittelhessen
Max

[Lachtaube]

Ich würde den Bereich in ein Array umwandeln, die Datumswerte ermitteln und anschließend in den neuen Zielbereich überführen.

Code: [Auswählen]

Public Sub yyyymmddToDate()
   Dim a() As Variant, dest As Range
   Dim r&, y&, m&, d&
   Dim t!

   t = Timer()

   a = Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

   For r = 1 To UBound(a)
      y = a(r, 1) \ 10000
      m = (a(r, 1) Mod 10000) \ 100
      d = (a(r, 1) Mod 10000) Mod 100
      a(r, 1) = DateSerial(y, m, d)
   Next

   Set dest = Range("I2")
   dest.Resize(UBound(a, 1), UBound(a, 2)).Value = a
   
   Debug.Print Timer() - t

End SubFür 100000 Werte in Spalte J benötigt mein lahmes Notebook gerade einmal 3,3 Sekunden zur Umwandlung.

[ChickenWing]

Ich würde den Bereich in ein Array umwandeln, die Datumswerte ermitteln und anschließend in den neuen Zielbereich überführen.
Code: [Auswählen]

Public Sub yyyymmddToDate()
   Dim a() As Variant, dest As Range
   Dim r&, y&, m&, d&
   Dim t!

   t = Timer()

   a = Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

   For r = 1 To UBound(a)
      y = a(r, 1) \ 10000
      m = (a(r, 1) Mod 10000) \ 100
      d = (a(r, 1) Mod 10000) Mod 100
      a(r, 1) = DateSerial(y, m, d)
   Next

   Set dest = Range("I2")
   dest.Resize(UBound(a, 1), UBound(a, 2)).Value = a
   
   Debug.Print Timer() - t

End Sub
Für 100000 Werte in Spalte J benötigt mein lahmes Notebook gerade einmal 3,3 Sekunden zur Umwandlung.

Funktioniert soweit super von der Geschwindigkeit her, aber die Werte stimmen nicht.

Er setzt mir den Wert 201101 was eigentlich für 01.01.2011 steht zu 01.11.2020 um. Vielleicht bekomme ich es noch selbst entsprechend angepasst, falls ja poste ich es hier natürlich rein, aber es hapert grade an Verständnis für den Code. Fängt schon bei "Dim r&" an... Aber was wäre ein Tag ohne was dazu zu lernen Auf zu Google^^

Auf jeden Fall schon mal vielen Dank für den Ansatz =)

[PhilS]

...aber es hapert grade an Verständnis für den Code. Fängt schon bei "Dim r&" an...

Dazu hilft sicherlich dieser Link: https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/type-characters
Ohne Lachtaube zu nahe treten zu wollen. Ich finde diese Art der Variablentypdefinition sehr unintuitiv und fehlerträchtig und rate dringend davon ab diese so zu verwenden.
(Gemeint ist hier ausschließlich die Variablendeklaration, nicht der übrige Code!)

[ChickenWing]

...aber es hapert grade an Verständnis für den Code. Fängt schon bei "Dim r&" an...

Dazu hilft sicherlich dieser Link: https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/type-characters

Ja, der hilft auf jeden Fall, Danke!  Hab ich so vorher noch nie gesehen.

[Lachtaube]

Selbstverständlich sind selbstsprechende Variablen-Deklarationen besser zu lesen und zu verstehen - aber hier geht es ja eher um das Prinzip, ein Range-Objekt in ein Array zu wandeln, im Array die Daten zu manipulieren und anschließend das Array in einem Rutsch in einen Bereich des Blatts zurückzuschreiben.

Zurück zum Thema: ich habe gedanklich noch dem Zahlenwert ein dd hinten angefügt, das gar nicht vorhanden ist. Im vorliegenden Fall reicht eine Ganzzahl-Division durch 100 aus, um den Jahresanteil zu erhalten. Mit dem Modulus-Operator kann dann der Monatswert ermittelt werden

Code: [Auswählen]

   y = a(r, 1) \ 100
   m = a(r, 1) Mod 100Für d setzt Du einfach 1 bei Dateserial ein.

[ChickenWing]

Zurück zum Thema: ich habe gedanklich noch dem Zahlenwert ein dd hinten angefügt, das gar nicht vorhanden ist. Im vorliegenden Fall reicht eine Ganzzahl-Division durch 100 aus, um den Jahresanteil zu erhalten. Mit dem Modulus-Operator kann dann der Monatswert ermittelt werden

Code: [Auswählen]

   y = a(r, 1) \ 100
   m = a(r, 1) Mod 100Für d setzt Du einfach 1 bei Dateserial ein.

Super, danke, ich werds gleich umsetzen und werde mich die Tage mal mit Arrays beschäftigen, damit hab ich bis jetzt noch nichts gemacht. Bei neuen Themen hilft es immer direkt ein Beispiel passend dazu parat zu haben, das hier sollte perfekt sein weil ich genau weiß was es machen soll =)

Grüße
Max