Excel vba: Format Problem

[sharky51]

Hallo zusammen,
ich schon wieder mit einer Frage.
Ich bekomme aus verschiedenen Quellen u.a. Uhrzeiten die in einer Tabelle in einer Spalte abgelegt werden sollen.
Nur sind die Quellzellen mal als "Standard" mal als "Text" und mal als "Uhrzeit" formatiert.

Für die Weiterverarbeitung benötige ich aber nur das Textformat.
Wie kann man jetzt in der Zielspalte alle Zellen durchlaufen, den Wert lesen, die Zelle als Text formatieren und den Zeitwert (z.B. 11:00) ohne den Sekundenanteil, falls vorhanden, wieder in die Zelle schreiben?

Habe schon mal gegoogelt aber nix passendes gefunden.

Ähnliches Problem habe ich mit Datumseinträgen die in Form von So. 29.04.2018 daherkommen.
Um die Datumswerte nun filtern/sortieren zu können, müssen die ersten 4 Zeichen abgeschnitten werden und die Spalte als "Text in Spalten" formatiert werden.
Da ich hier auch hunderte von Daten bekomme würde ich das auch gerne mittels Makro abwickeln.

Habe das mal mit drei kleinen Makros ausprobiert, verbunden mit der Frage ob das nicht einfacher, schlanker geht?

Code:

'Löscht vorgegebene Anzahl von Zeichen (von links)
Sub Zeichen_löschen()
 Dim rng As Range
 Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
 Dim Zelle As Range
 For Each Zelle In rng.Cells
   Zelle = Right(Zelle, Len(Zelle) - 4)
 Next Zelle
End Sub

'Formatiert Text in Spalten mit Datumseinträgen
Sub Datumstext_Formatieren()
 letztezeile = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
 
 For lngZeile = 4 To letztezeile
   Cells(lngZeile, 2).TextToColumns Destination:=Range("B4"), DataType:=xlDelimited, _
     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
     :=Array(1, 1), TrailingMinusNumbers:=True
 Next
End Sub

'Sortiert Datum absteigend
Sub DatumSortieren()
 letztezeile = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
 ActiveSheet.AutoFilter.Sort.SortFields.Clear
 ActiveSheet.AutoFilter.Sort. _
   SortFields.Add Key:=Range("B3:B" & letztezeile), SortOn:=xlSortOnValues, Order:= _
   xlAscending, DataOption:=xlSortTextAsNumbers
 
 With ActiveSheet.AutoFilter.Sort
   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With
End Sub

[RPP63]

Moin!
Gib mal ein Beispiel der Zeitspalte mit ca. 20 Uhrzeiten.
Als .xlsx natürlich.

Gruß Ralf

[sharky51]

Hallo Ralf,

hier mal ein Beispiel mit nur zwei unterschiedlichen Formatierungen.

[RPP63]

Moin!
Deine "Text-Uhrzeiten" haben sämtlich ein führendes Leerzeichen.
Folgende Formel entfernt dieses und wandelt den Text in eine echte Uhrzeit:
=--GLÄTTEN(B2)
Da Du ja (warum auch immer) das Ganze als Text haben möchtest:
=TEXT(--GLÄTTEN(B2);"hh:mm")

Ergänzend:
Das Format hat nichts mit dem Wert zu tun!
Zu Deinem zweiten Problem (Datum) hast Du leider keine Beispiele gebracht.
Dennoch ein Schuss ins Blaue:
=--TEIL(B2;FINDEN(". ";B2)+2;10)
Ginge auch mittels PowerQuery, denn das "exotische" Datumsformat wird hier automatisch erkannt.

Gruß Ralf

[sharky51]

Moin,

auf Deinen Bemerk - warum auch immer - die Zeit und Datumsangaben sollen von Excel aus mit anderen Angaben als Termin in den Outlook-Kalender übertragen werden.
Wenn die Formate (Zeit & Datum) nicht exakt so vorliegen klappt das eben nicht. Ich habe zumindest keine andere Möglichkeit gefunden.

Vielen Dank für die Formeln, aber geht die Formatanpassung auch mittels vba?

[RPP63]

Of course!
Bevor Dir hier jemand eine Schleife vorschlägt:
Ich würde exakt so (mittels Formel) vorgehen.
Dies ist in Punkto Tempo jeder Schleife vorzuziehen!
Ich schreibe die Formel temporär in Spalte 1002 aka ALN

Modul Modul1

Option Explicit 



Sub RPP() 

With Tabelle8 

  With .Range(.Range("B2"), .Range("B2").End(xlDown)).Offset(0, 1000) 

    .Formula = "=TEXT(--TRIM(B2),""hh:mm"")" 

    .Copy 

    Tabelle8.Range("B2").PasteSpecial xlPasteValues 

    .Delete 

  End With 

End With 

With Application 

  .CutCopyMode = False 

  .Goto Tabelle8.Range("B2") 

End With 

End Sub 

Gruß Ralf

[sharky51]

Hallo Ralf,

funktioniert sehr gut.
Trotzdem habe ich ein Verständnisproblem. In der Spalte "D" hatte ich ja diese Formel "=ZELLE("Format";B2)" zur Prüfung der Formateinstellung eingegeben.
Nach Ausführung des Makros hätte ich eigentlich erwartet, dass sich dort etwas ändert... oder wie muss ich das nun verstehen?
An der Formateinstellung der Zellen hat sich nix geändert sondern es wurde nur am Inhalt der Zelle etwas herumgeschnipselt - richtig?

[RPP63]

Moin mal wieder!
Du hattest vorher eine "Text-Uhrzeit" mit, später eine ohne führendem Leerzeichen in der Zelle.
Aber dies war ja von Dir so gewollt. ;)
Die ehemals echten Uhrzeiten wurden natürlich auch wunschgemäß in Text geändert, so dass =ZELLE() dort eigentlich das Ergebnis ändern müsste.
Gerade überprüft: Das Zellformat ändert sich (logischerweise) nicht.
Muss ja auch nicht; wenn ich in einer Zelle mit Zeitformat einen Text eingebe, bleibt dieser natürlich Text.

Gruß Ralf

[sharky51]

OK, verstanden!

Vielen Dank nochmals - auch für Deine Geduld!