Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1516to1520
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

VBA: manuelle Formatierung von Zeitwerten

VBA: manuelle Formatierung von Zeitwerten
23.09.2016 23:22:09
Zeitwerten
Hallo Zusammen,
ich hätte da mal eine Frage an euch Experten. Ich suche seit Stunden nun schon die Lösung und komme nicht weiter. Recherche im Internet hat mir bisher auch nicht so Recht geholfen.
Ich habe folgendes Problem:
Aus einem Programm habe ich eine Datentabelle mit Zeitwerten im Format T:hh:mm:ss.0000 exportiert. Da Excel scheinbar nur das Format T:hh:mm:ss.00 verarbeiten kann und mich die Tausendstel für meine Auswertung nicht interessieren, entferne ich zurzeit per Makro die letzten fünf Zeichen jedes Eintrages und formatiere den resultierenden Zelleninhalt im Format T:hh:mm:ss. So weit so gut.
Problematisch wird es, wenn die zu verarbeitenden Werte ab und zu nicht das Format T:hh:mm:ss.0000 aufweisen, sondern bspw mm:ss.0000. Sobald ich hier die letzten fünf Ziffern entferne, zerschießt Excel die komplette Formatierung, sodass mir komplett abwegige Zeitwerte angezeigt werden (z.B wird 05:00.0000 zu 0:05:00:00). Versuche ich dieses Problem zu umgehen, indem ich vor dem Löschen der letzten fünf Ziffern den Wert "00:00:" voransetze (um wieder auf die Formatierung T:hh:mm:ss.0000 zu kommen), passiert das gleiche. Probleme bereiten mir nur die Formatierungen mm:ss.0000, m:ss.0000, ss.0000 und s.0000 - der Rest funktioniert tadellos.
Wisst ihr, wo hier mein Fehler liegt und wie ich ihn beheben könnte? Gibt es ferner eine elegantere Möglichkeit, als zellenweise die Einträge der Tabelle manuell und sehr zeitintensiv anzupassen?
Wäre es zum Beispiel möglich, Excel mittels VBA dazu zu zwingen, die Formatierung T:hh:mm:ss.0000 anzunehmen?
Oder gibt es einen Befehl, mit dem die letzten x Ziffern aller markierter Zellen entfernt werden können?
Für den Fall, dass mein Quellcode für die Formatierungsprobleme verantwortlich ist, füge ich ihn euch gerne hier noch an.
Vielen Dank für eure Hilfe!
JMD

For Each Zelle In Sheets("Durchlaufzeiten").UsedRange
Str = Zelle
If Len(Str) = 6 Then
Str1 = Zelle.Value
Str1 = "00:00:00:0" & Str1
Zelle.Value = Str1
End If
If Len(Str) = 7 Then
Str1 = Zelle.Value
Str1 = "00:00:00:" & Str1
Zelle.Value = Str1
End If
If Len(Str) = 9 Then
Str1 = Zelle.Value
Str1 = "00:0" & Str1
Zelle.Value = Str1
End If
If Len(Str) = 10 Then
Str1 = Zelle.Value
Str1 = "00:" & Str1
Zelle.Value = Str1
End If
If Len(Str) > 4 Then
If Mid$(Str, Len(Str) - 4, 1) = "." Then
Zelle = Left(Str, Len(Str) - 5)
End If
End If
Next
Sheets("Durchlaufzeiten").Columns("C:E").NumberFormat = "d:hh:mm:ss"

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA: manuelle Formatierung von Zeitwerten
24.09.2016 09:51:59
Zeitwerten
Hallo,
kann man zuerst prüfen, welches Format voliegt?
mfg
AW: VBA: manuelle Formatierung von Zeitwerten
24.09.2016 10:46:07
Zeitwerten
Hallo Fennek,
meinst du das Format der einzelnen Zellen? Indirekt überprüfe ich das, indem ich die Länge des Zellenstrings auslese. Wenn er bspw. die Länge 9 hat, weiß ich, das Format ist vom Typ m.ss.0000.
VG
AW: VBA: manuelle Formatierung von Zeitwerten
25.09.2016 07:34:16
Zeitwerten
Hallo JMD,
außer dem dem Auffüllen der fehlenden hh und oder mm muss auch noch eine Werte-Konversion in ein Excel-Datum gemacht werden.
Dein exotisches Format mit ":" zwischen Tag und Zeitwert kann Excel nicht automatisch konvertieren - da kommt nur Müll raus.
Nachfolgend 2 Varianten wie man es lösen kann.
Bei Variante B werden die Teile deines Zeitwerts am ":" getrennt und beginnend von rechts in einer Schleife zu einem Zeitwert summert.
Damit nicht noch mehr Kuddelmuddel entsteht solltest du die Umwandlung gezielt auf den gewünschten Bereich beschränken.
Gruß
Franz
Sub Test()
Dim Zeile As Long
Dim StatusCalc As Long
With Application
StatusCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Durchlaufzeiten")
'in Spalte C bis E die Zeit-Texte in Excelzeiten umwandeln
Zeile = .UsedRange.Row + .UsedRange.Rows.Count - 1
Call prcZeitenConvertierenA(.Range(.Cells(1, 3), .Cells(Zeile, 5)), ".", "D:hh:mm:ss")
'        Call prcZeitenConvertierenB(.Range(.Cells(1, 3), .Cells(Zeile, 5)), ".", "D:hh:mm:ss")
End With
With Application
.Calculation = StatusCalc
.ScreenUpdating = True
End With
End Sub
Function prcZeitenConvertierenA(Bereich As Range, _
Optional DezimalZeichen As String = ",", _
Optional Zeitformat As String = "DD.MM.YYYY hh:mm:ss")
Dim strZeit As String, str1000 As String
Dim Zelle As Range
Dim dblTag As Double
Dim intDP As Integer
'Zeitformat in Bereich setzen - muss vor dem Einfügen der konvertierten Texte erfolgen!
Bereich.NumberFormat = Zeitformat
For Each Zelle In Bereich.Cells
If Not IsEmpty(Zelle) Then
strZeit = Zelle.Text
'Prüfen, ob Wert nummerisch, wenn die Doppelpunkte entfernt werden
If IsNumeric(VBA.Replace(strZeit, ":", "")) Then
'Dezimalzeichen durch System-Dezimalzeichen ersetzen
strZeit = VBA.Replace(strZeit, DezimalZeichen, _
Application.International(xlDecimalSeparator))
'1000stel Sekunden merken
str1000 = Right(strZeit, 5)
'Text nach dem Dezimalzeichen abschneiden
strZeit = Mid(strZeit, 1, Len(strZeit) - 5)
'Anzal der Doppelpunkte in Zelle
intDP = Len(strZeit) - Len(VBA.Replace(strZeit, ":", ""))
dblTag = 0
Select Case intDP
Case 0 'nur Sekunden
strZeit = "00:00:" & strZeit
Case 1 'mm:ss
strZeit = "00:" & strZeit
Case 2 ' hh:mm:ss
Case 3 ' Tage:hh:mm:ss
dblTag = Val(Left(strZeit, InStr(strZeit, ":") - 1))
strZeit = Mid(strZeit, InStr(strZeit, ":") + 1)
End Select
If IsDate(strZeit) Then
Zelle.Value = dblTag + CDbl(CDate(strZeit)) + CDbl(str1000) / 24 / 3600
End If
End If
End If
Next
End Function
Function prcZeitenConvertierenB(Bereich As Range, _
Optional DezimalZeichen As String = ",", _
Optional Zeitformat As String = "DD.MM.YYYY hh:mm:ss")
Dim strZeit As String
Dim Zelle As Range
Dim arrZeit, dblZeit As Double
Dim intJ As Integer, intK As Integer
'Zeitformat in Bereich setzen - muss vor dem Einfügen der konvertierten Texte erfolgen!
Bereich.NumberFormat = Zeitformat
For Each Zelle In Bereich.Cells
If Not IsEmpty(Zelle) Then
strZeit = Zelle.Text
If IsNumeric(VBA.Replace(strZeit, ":", "")) Then
'Dezimalzeichen durch System-Dezimalzeichen ersetzen
strZeit = VBA.Replace(strZeit, DezimalZeichen, _
Application.International(xlDecimalSeparator))
'Zeitangaben am Doppelpunkt splitten und in Array speichern.
arrZeit = VBA.Split(strZeit, ":")
intK = 0
dblZeit = 0
For intJ = UBound(arrZeit) To LBound(arrZeit) Step -1
intK = intK + 1
Select Case intK
Case 1 'Sekunden
dblZeit = dblZeit + CDbl(arrZeit(intJ)) / 24 / 3600
Case 2  'Minuten
dblZeit = dblZeit + CDbl(arrZeit(intJ)) / 24 / 60
Case 3 'Stunden
dblZeit = dblZeit + CDbl(arrZeit(intJ)) / 24
Case 4 'Tage
dblZeit = dblZeit + CDbl(arrZeit(intJ))
End Select
Next
Zelle.Value = dblZeit
End If
End If
Next
End Function

Anzeige
AW: VBA: manuelle Formatierung von Zeitwerten
29.09.2016 23:13:56
Zeitwerten
Hallo Franz,
vielen lieben Dank für deine Hilfestellung. Mit deinem Lösungsvorschlag hat es wunderbar funktioniert.
Viele Grüße
JMD

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige