Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1600to1604
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Uhrzeit beim extrahieren verfälscht

Uhrzeit beim extrahieren verfälscht
17.01.2018 11:57:44
Burak
Moin Moin!
also es geht um die Uhrzeit. Wenn ich das Datum und die Uhrzeit aus einer Zelle in zwei aufteile, verfälscht er die Uhrzeit unter gewissen Umständen.
Die Zelle die das Datum UND die Uhrzeit enthält ist als Standard formatiert und beinhaltet Werte wie:
20181101212256, also yyyymmddhhmmss was dann 01.11.2018 und 21:22:56 Uhr wäre.
Bei Werten, wo die Uhrzeit mit 0 beginnt, werden die Uhrzeiten verfälscht bzw. die 0 vorne weggelassen.
also ...011705 wird zu 11:70:05 statt 01:17:05
...003748 wird zu 37:48:48 statt 00:37:48
und ...000818 wird zu 81:8:18 statt 00:08:18
Die Beispieldatei (Ausschnitt):
https://www.herber.de/bbs/user/119035.xlsm
und der Code:
Sub Logimport()
'Deklarationen der Variablen
Dim k As Long
Dim ws As Worksheet
Dim importdatei As String
Dim Zeilenzahl As Long
'Auswahl der Log-Datei für den Import
importdatei = Application.GetOpenFilename
Do Until importdatei  "Falsch"
importdatei = Application.GetOpenFilename
Loop
'Tabellenblatt leeren
Worksheets("LogImport").Cells.Clear
'Import der Log-File
Set ws = ActiveWorkbook.Sheets("LogImport")
With ws.QueryTables.Add(Connection:="TEXT;" & importdatei, Destination:=ws.Range("A2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
With Worksheets("LogImport")
'Anzahl der beschrifteten Zeilen zählen
Zeilenzahl = .Cells(.Rows.Count, 2).End(xlUp).Row
'Spalten hinzufügen
.Columns("A:A").Insert Shift:=xlToRight
.Columns("J:K").Insert Shift:=xlToRight
.Columns("M:N").Insert Shift:=xlToRight
'Textformatierung von Spalten
For k = 2 To Zeilenzahl
.Range("A" & k).Value = Left(.Range("B" & k).Value, 4)                                ' _
Erste 4 Stellen des Masterbarcodes
If .Range("O" & k).Value  0 Then
.Range("F" & k).Value = .Range("F" & k).Value & "-" & .Range("O" & k).Value         ' _
Analyse-Typ Zusammensetzung
End If
.Range("J" & k).Value = Left(.Range("I" & k).Value, 8)                                ' _
AOI-Eintrag Datum
.Range("K" & k).Value = Right(.Range("I" & k).Value, 6)                               ' _
AOI-Eintrag Uhrzeit
.Range("M" & k).Value = Left(.Range("L" & k).Value, 8)                                ' _
Reparatureneintrag Datum
.Range("N" & k).Value = Right(.Range("L" & k).Value, 6)                               ' _
Reparatureneintrag Uhrzeit
.Range("J" & k).Value = Right(.Range("J" & k).Value, 2) & "." & Mid(.Range("J" & k).Value, _
5, 2) & "." & Left(.Range("J" & k).Value, 4)
.Range("K" & k).Value = "'" & Left(.Range("K" & k).Value, 2) & ":" & Mid(.Range("K" & k). _
Value, 3, 2) & ":" & Right(.Range("K" & k).Value, 2)
.Range("M" & k).Value = Right(.Range("M" & k).Value, 2) & "." & Mid(.Range("M" & k).Value, _
5, 2) & "." & Left(.Range("M" & k).Value, 4)
.Range("N" & k).Value = "'" & Left(.Range("N" & k).Value, 2) & ":" & Mid(.Range("N" & k). _
Value, 3, 2) & ":" & Right(.Range("N" & k).Value, 2)
.Range("O" & k).Value = Right(.Range("C" & k).Value, 1)                               ' _
Rausfiltern der LP aus dem BT-Namen
.Range("C" & k).Value = Left(.Range("C" & k).Value, Len(.Range("C" & k).Value) - 2)   'BT- _
Name korrigieren
Next
'Datum und Uhrzeiten formatieren
.Columns("J").NumberFormat = "dd.mm.yyyy"
.Columns("M").NumberFormat = "dd.mm.yyyy"
.Columns("K").NumberFormat = "hh:mm:ss"
.Columns("N").NumberFormat = "hh:mm:ss"
'Nicht benötigte Spalten entfernen
'.Columns("I").Delete
'.Columns("K").Delete
'Tabellenüberschriften
.Range("A1").Value = "Barcode"
.Range("B1").Value = "Masterbarcode"
.Range("C1").Value = "BT-Name"
.Range("D1").Value = "PIN"
.Range("E1").Value = "LIBname"
.Range("F1").Value = "AnalyseTyp"
.Range("G1").Value = "Fehlercode"
.Range("H1").Value = "Benutzer"
.Range("J1").Value = "AOI Datum"
.Range("K1").Value = "AOI Uhrzeit"
.Range("M1").Value = "Rep Datum"
.Range("N1").Value = "Rep Uhrzeit"
.Range("O1").Value = "LP Nr."
.Range("P1").Value = "Prüfung"
.Rows(1).NumberFormat = "General"
'Formatierungen
.Rows(1).Font.Bold = True
.Columns("A:M").HorizontalAlignment = xlRight
.Rows(1).HorizontalAlignment = xlLeft
.Columns("A:A").ColumnWidth = 7.43
.Columns("B:B").ColumnWidth = 13.71
.Columns("C:C").ColumnWidth = 8.43
.Columns("D:D").ColumnWidth = 3.43
.Columns("E:E").ColumnWidth = 7.86
.Columns("F:F").ColumnWidth = 11.14
.Columns("G:G").ColumnWidth = 10.29
.Columns("H:H").ColumnWidth = 8.29
.Columns("J:J").ColumnWidth = 9.86
.Columns("K:K").ColumnWidth = 10.57
.Columns("L:L").ColumnWidth = 10
.Columns("M:M").ColumnWidth = 10.71
.Columns("N:N").ColumnWidth = 5.43
.Columns("O:O").ColumnWidth = 7.29
End With
End Sub
Habe in der Datei versucht den Code etwas anzupassen, da der Import der Daten für euch nicht möglich ist.
Ich denke der Fehler liegt da, wo die letzten 6 Ziffern des Wertes erstmal in eine Zelle geschrieben werden und dann erst formatiert. Dadurch gehen vorrangehende Nullen wahrscheinlich flöten.
Wie gehe ich das Problem am besten an?
Danke und freundliche Grüße an alle!

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Uhrzeit beim extrahieren verfälscht
17.01.2018 12:43:11
Burak
ah habs.
hab aus
.Range("K" & k).Value = Right(.Range("I" & k).Value, 6)
.Range("N" & k).Value = Right(.Range("L" & k).Value, 6)
einfach
.Range("K" & k).Value = "'" & Right(.Range("I" & k).Value, 6)
.Range("N" & k).Value = "'" & Right(.Range("L" & k).Value, 6)
gemacht um so das wegfallen der Nullen zu verhindern.
Danke trotzdem an alle
AW: Uhrzeit beim extrahieren verfälscht
17.01.2018 12:43:53
Rudi
Hallo,
als Beispiel:
Sub aaa()
Dim x, d, z
x = 20181101072256#
d = DateSerial(--Left(x, 4), --Mid(x, 5, 2), --Mid(x, 7, 2))
z = TimeSerial(--Mid(x, 9, 2), --Mid(x, 11, 2), --Mid(x, 13, 2))
End Sub

Gruß
Rudi
Anzeige
AW: Uhrzeit beim extrahieren verfälscht
17.01.2018 12:52:32
UweD
Hallo
anstelle das ganze per Schleife zu machen...
so..
- Formeln in je den gesamten Bereich reinschreiben
- durch Werte ersetzen
    Zeilenzahl = .Cells(.Rows.Count, 9).End(xlUp).Row
  
  
    With Union(.Range("J2:J" & Zeilenzahl), .Range("M2:M" & Zeilenzahl))
        .FormulaR1C1 = "=DATEVALUE(MID(RC[-1],7,2)&"".""&MID(RC[-1],5,2)&"".""&LEFT(RC[-1],4))"
        .NumberFormat = "dd.mm.yyyy"
    End With
    With Union(.Range("K2:K" & Zeilenzahl), .Range("N2:N" & Zeilenzahl))
        .FormulaR1C1 = "=TIMEVALUE(MID(RC[-2],9,2)&"":""&MID(RC[-2],11,2)&"":""&RIGHT(RC[-2],2))"
        .NumberFormat = "hh:mm:ss"
    End With
    With .Range("J2:N" & Zeilenzahl)
        .Value = .Value
    End With
  
  
  
  
    'Nicht benötigte Spalten entfernen 

LG UweD
Anzeige
AW: Uhrzeit beim extrahieren verfälscht
17.01.2018 13:44:35
Burak
beide Vorschläge funktionieren gut, aber da ich Schleifen gerne vermeide, könntest du mir evtl folgenden Teil auch noch "entschleifen"?

For k = 2 To Zeilenzahl
.Range("A" & k).Value = Left(.Range("B" & k).Value, 4)
If .Range("O" & k).Value  0 Then
.Range("F" & k).Value = .Range("F" & k).Value & "-" & .Range("O" & k).Value
End If
Next k
hab noch nicht viel erfahrungen mit Range.
Danke
AW: Uhrzeit beim extrahieren verfälscht
17.01.2018 14:44:09
UweD
Hallo nochmal
    'Spalte A 
    With .Range("A2:A" & Zeilenzahl)
        .FormulaR1C1 = "=LEFT(RC[1],4)"
        .Value = .Value
    End With
    
    'Spalte F über temporäre Hilfsspalte 
    LC = .Cells.SpecialCells(xlCellTypeLastCell).Column + 1 'neue Spalte ganz hinten 
    With .Range(.Cells(2, LC), .Cells(Zeilenzahl, LC))
        .FormulaR1C1 = "=IF(RC6<>"""",IF(RC15<>"""",RC6&""-""&RC15,RC6),"""")"
    End With
    .Range("F2:F" & Zeilenzahl).Value = .Range(.Cells(2, LC), .Cells(Zeilenzahl, LC)).Value 'zurückübertragen 
    .Columns(LC).ClearContents 'temp Spalte leeren 
LG UweD
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige