Importierte Zeit-Werte sind ohne Doppelpunkt oder andere Trenner → zu hh:mm:ss,nnn
Mitunter kommt es vor, dass beim Import von Daten die Zeitangaben ohne Trenner sind, es fehlt der Doppelpunkt. Manche Sensoren senden solch eigenartige Werte als Protokoll. Fast immer sind das auch numerische Werte, wo dann bei Stunden bis 9:59:59h die führende Null natürlich nicht mit übertragen oder seitens Excel abgeschnitten wird. Als erschwerend kann dann noch hinzu kommen, dass hundertstel Sekunden mit angegeben werden, die teilweise mit einem Punkt, teilweise mit einem Komma separiert werden.
Um so etwas in ein „vernünftiges” Format zu bekommen, bieten sich mehrere Wege an. Wenn es nur um die Optik geht, dann reicht ein anderes Zahlenformat aus. Soll mit den Werten auch gerechnet werden, dann sollten dort auch „echte” Zeitangaben stehen. In den Beispielen wird (des möglichen „worst case” wegen) immer davon ausgegangen, dass Zeiten vor 10:00h mit einstelliger Stundenangabe nach dem Import in Excel stehen: 91020.30 bzw. 91020,30 wäre also 9 Uhr 10 Minuten 20 Sekunden und 30 hundertstel Sekunden.
Prinzipiell machen auch noch die hundertstel Sekunden bei der „normalen” Umwandlung in echte Zeit-Werte Probleme, darum werden hier auch beide Wege aufgezeigt: Mit und ohne Berücksichtigung der Sekundenbruchteile.
Nur das Zahlenformat anpassen
Ausgangslage: 91020,3 in Zelle A1. Die erste Idee, die vielen Usern gewiss kommt: =TEXT(A1;"h:mm:ss"). Nur das Ergebnis ist gewiss entschieden anders, als erwartet, probieren Sie es gerne selber aus.
Nach dem Fehlversuch soll es ein Mal in ganz kleinen Schritten voran gehen. Und das erste Ergebnis soll ohne die Sekundenbruchteile sein. Dazu müssen Sie erst einmal eventuelle Nachkommastellen entfernen, das geht mit der Funktion GANZZAHL(). Zumindest dann, denn der Dezimaltrenner genau dem entspricht, den Excel auf Ihrem System verwendet. Mit anderen Worten: Wenn Sie ein Komma als Dezimaltrenner verwenden, dann muss der importierte Wert die hundertstel Sekunden auch mit einem Komma separieren. Falls dort ein Punkt steht, muss dieser ersetzt werden (beispielsweise durch StrgH). Es gibt auch andere Möglichkeiten, das aber würde hier zu weit führen.
Abhängig von der Zeit bleiben nun 5 oder 6 Stellen/Ziffern über. Da sollen nun die Doppelpunkte gesetzt werden. Das ließe sich mit einer WENN()-Funktion machen, denn entweder ist der linke Doppelpunkt nach der ersten oder nach der zweiten Ziffer. Ich ziehe es vor, die Zeit in jedem Fall 6‑stellig darzustellen. Darum sieht meine Formel im ersten Schritt so aus: =GANZZAHL(A1)
Es wird schlicht und einfach der Nachkomma-Anteil abgeschnitten. Jetzt bleibt eigentlich nur noch, das Zahlenformat anzupassen. Als benutzerdefiniertes Zahlenformat gebe ich folgendes in das entsprechende Feld ein: 00":"00"."00 und das Ergebnis sieht genau so aus, wie ich es haben möchte. Aber Vorsicht, es ist immer noch die Zahl 91.020 (hier mit Tausendertrenner dargestellt), die sich hinter dem Wert verbirgt. Wenn Sie mit dieser scheinbaren Zeitangabe rechnen, geht das richtig schief!
Wenn Sie das Ganze mit den Sekundenbruchteilen darstellen wollen, dann ist das prinzipiell noch einfacher: Ohne Umwandlung, nur das Zahlenformat ändern: 00":"00":"00,00. Auch hier gilt natürlich, dass das Erscheinungsbild „lügt”, weil ein ganz anderer Wert dahinter steckt.
Sind die hundertstel Sekunden mit einem Punkt abgetrennt, dann ist es am einfachsten, als ersten Schritt in der Spalte den Punkt durch ein Komma zu ersetzen. Der Rest ist dann wie hierüber beschrieben, weil durch den Austausch eine Zahl nach europäischer Norm daraus wird. Das ist ja weiter oben schon angeschnitten worden.
In „echte” Zeitangaben konvertieren
Wollen oder müssen Sie mit den Werten weiter rechnen oder soll auf der Basis ein Diagramm erstellt werden, dann ist es unbedingt erforderlich, „echte” Zeiten daraus zu machen. Sie wissen wahrscheinlich, dass Zeiten immer der Bruchteil eines Tages sind und durch Excel nur anders dargestellt werden. Sie sehen 12:00, Excel rechtet mit 0,5 weil diese Zeitangabe genau die Hälfte des Tages wiederspiegelt. Etwas mehr dazu können Sie hier im Blog nachlesen.
Funktion ZEIT()
Die einfachste Lösung ist schon fast banal zu nennen. Sie baut auf Zahlenformat hierüber auf, bringt aber dennoch eine Uhrzeit zustande, mit der Excel rechnen kann. Im ersten Denkansatz mache ich aus dem Uhrzeit-Text einen Text in einem standardisierten Format: =TEXT(GANZZAHL(A1); „000000”). Davon kann ich nun mit den Textfunktionen LINKS(), TEIL() und RECHTS() die Stunden, Minuten und Sekunden extrahieren. Die Formel würde dann so lauten:
=ZEIT(LINKS(TEXT(GANZZAHL(A1); "000000"); 2); TEIL(TEXT(GANZZAHL(A1); "000000"); 3; 2); RECHTS(GANZZAHL(A1); 2))
(Die Formel gehört natürlich in 1 Zeile!) Ach ja, wahrscheinlich werden Sie das Zahlenformat von AM/PM auf europäische Norm ändern.
Funktion ZEITWERT()
Wenn Sie die Formel eben als lang empfunden haben, dann sehen Sie sich einmal die Alternative an. Hier wird die Zahl der importierten Zeitangabe in eine Text umgewandelt, der eine lesbare Zeitangabe in korrektem Format enthält. Und das ist nicht nur eine andere Ansicht, die Doppelpunkte stehen dort tatsächlich drinnen. Und die Funktion wandelt dann den Text in eine korrekte Zeit um. Hier nun die Formel, die natürlich auch in eine einzige Zeile eingegeben wird:
=ZEITWERT(WENN(LÄNGE(GANZZAHL(A1))=5; LINKS(A1; 1) & ":" & TEIL(A1; 2; 2); LINKS(A1; 2) & ":" & TEIL(A1; 3; 2)) & ":" & RECHTS(GANZZAHL(A1); 2))
Hier wird übrigens stets eine serielle Zahl zurückgegeben, die werden Sie in jedem Fall als Zeit formatieren müssen, wenn Sie eine Uhrzeit sehen wollen.
Mit VBA
Per Makro, also mit VBA geht das alles sehr direkt und sogar auch in der gleichen Spalte. Mit anderen Worten: Es bedarf keiner Hilfsspalte, die Werte werden gleich in eine Zeitangabe umgewandelt. Dieses Makro ist so ausgelegt, dass Sie den anzupassenden Bereich markieren und dann den VBA-Code beispielsweise per AltF8 oder über eine Schaltfläche starten:
Option Explicit
Sub EchteZeiten()
Dim rng As Range, c As Range
Dim rescue As Variant, Wert As Variant, Wert2 As String
Set rng = Selection
For Each c In rng
rescue = c
c.Replace What:=".", Replacement:=","
If IsNumeric(c) Then
Wert = CStr(CLng(c))
If Len(Wert) > 4 Then
Wert2 = Right("0" & Format(Wert, "000000"), 6)
Wert2 = (Left(Wert2, 2) & ":" & Mid(Wert2, 3, 2) & ":" & Right(Wert2, 2))
If IsDate(TimeValue(Wert2)) Then
c = CDate(Wert2)
c.NumberFormat = "HH:MM:SS"
Else
Call Fehlermarkierung(c, rescue)
End If
Else
Call Fehlermarkierung(c, rescue)
End If
End If
Next c
End Sub
Sub Fehlermarkierung(c As Range, rescue As Variant)
c = rescue
c.Interior.Color = RGB(255, 255, 0)
End Sub
Mit Hilfe von Power Query
Power Query ist ein Add-In für Windows-Excel, welches ab Excel 2010 eingesetzt werden kann. Ab der 2016er-Version ist es fest in Excel (Windows) eingebunden. Mehr dazu können Sie gerne hier im Blog nachlesen. Die Stärke dieses Tools liegt darin, dass Sie vieles damit durchführen können, ohne programmieren zu müssen. Und ein weiterer Vorteil ist, dass jeder Schritt solch eines Datenimports aufgezeichnet wird und bei aktualisierter Datenlage mit wenigen Klicks zu wiederum aktuellen Ergebnissen führt.
Hinweis: Der gesamte Abschnitt ist keine Anleitung zum Thema Power Query. Sie können aber anhand der aufgezeichneten Daten gewiss nachvollziehen, was dort in einzelnen Schritten passiert ist. Zugegeben, das kostet Konzentration und Sie brauchen ein gewisses Maß an Abstraktionsvermögen … Idealerweise sehen Sie es als Initialzündung, PQ auch bei eigenen Projekten künftig einzusetzen. Und im Zweifel schreiben Sie uns eine E‑Mail, wir leiten Ihre Anfrage dann gerne an unseren Sponsor GMG-CC weiter. Bis dahin ist alles kostenlos und unverbindlich.
Um das Ganze etwas nachvollziehen zu können, laden Sie bitte diese zip-Datei herunter. Dort sind 2 Files enthalten: ImportData.csv und Importierte Zeitwerte ohne Trenner.xlsx. Falls Sie für eigene Trainigsläufe die csv-Datei verwenden wollen, dann sollten Sie diese nicht per Doppelklick öffnen sondern beispielsweise entweder per copy/paste in ein Blatt einfügen oder über das Daten-Menü Externe Daten abrufen | Aus Text (!) importieren. Aber Vorsicht, diese Möglichkeit birgt Gefahren. Der ursprüngliche Wert 183920.143 wird unter Umständen zu 183.920.143; also eine Dezimalzahl (der Punkt ist hier der Dezimaltrenner) wird zu einer riesigen Ganzzahl, weil Excel den Punkt als Tausender-Trenner interpretiert. Hier wäre dann ein direkter Import durch Power Query hilfreicher. – In der Excel-Datei sind die Daten schon enthalten. Außerdem wurde noch ein Überschrift eingefügt, weil eine Liste bzw. Intelligente Tabelle eine Überschrift in jeder Spalte braucht.
Im Tabellenblatt Import sind nun diese (korrekten) Daten enthalten. Was in der Praxis (hoffentlich) nie passiert ist hier in provokanter Weise manipuliert worden: Teilweise ist ein Punkt der Dezimaltrenner, teilweise das Komma. So etwas ist schon möglich, wenn die Daten per Hand eingegeben worden sind. Und in der letzten Zeile steht ein Wert, der nie und nimmer eine Zeit darstellen könnte. „Faktor Mensch”, eben. Darum dieses exotisch anmutende Beispiel.
Wie schon erwähnt, das Ganze soll Ihnen etwas Appetit auf Power Query machen. In einzelnen Schritten zeige ich Ihnen auf, welche Ergebnisse mit (meist) einfachen Mitteln möglich sind. Sehr kurze, stichwortartige Hinweise zu den einzelnen Arbeitsblättern „gönne” ich Ihnen dennoch. 🙂
Lösung 1
Die Ergebnisse hier sind auf hh:mm:ss begrenzt, die Sekundenbruchteile werden nicht abgeschnitten sondern die Sekunden werden auf null Stellen gerundet. Abschneiden wäre natürlich auch möglich. Das gilt übrigens auch für die nächste Lösung. – In PQ worden die Daten erst in Text umgewandelt, damit sie genau so verarbeitet werden können, wie sie im Original sind. Dann sind die Punkte durch Kommas ersetzt worden und anschließend habe ich den Datentyp Ganzzahl gewählt, um die Sekunden zu runden.
Im nächsten Schritt teile ich die erste Spalte nach jeweils 2 Zeichen von rechts aus gesehen. Diesen Vorgang wiederhole ich noch einmal, womit insgesamt 3 Spalten entstanden sind. Umbenennen der Spalten und den Abfrage-Editor schließen (und die Daten natürlich in ein neues Tabellenblatt laden, was automatisch geschieht).
Im neuen, eben erstellten Tabellenblatt (gleich umbenannt) habe ich dann noch die Überschrift in Spalte D geschrieben und in D2 die Funktion eingefügt, um aus den drei Einzelwerten eine „echte” Zeit zu machen. Natürlich habe ich auch noch das Zahlenformat angepasst. Aber: Spätestens hier erkennen Sie, dass auch solch ein „Unsinn” wie in der letzten Zeile brav umgewandelt wird. Dann sind es eben 1122 Stunden …
Lösung 2
Die erste Lösung hat einen großen Vorteil: Sie brauchen nur zu Klicken, um zum Ergebnis zu kommen. Und natürlich müssen Sie den Weg zum Ziel kennen, aber das ist generell ja eine Vorbedingung. Aber es gibt auch einen kleinen Nachteil, vielleicht sogar zwei: Die Quelldaten müssen in einer Liste stehen, damit eine Aktualisierung klappt. Und wenn Daten in der Quelldatei angefügt werden, der Import also mehr Zeilen hat als der vorherige, dann müssen die überzähligen Zeilen in Spalte D auch als Zeit formatiert werden. Das hört sich vielleicht kompliziert an, aber probieren Sie gerne einmal, im Blatt Daten ein oder zwei „Text-Zeiten” anzufügen. Dann wissen Sie rasch, was ich meine.
Möchten Sie das vermeiden, dann bedarf es in PQ einer Zeile Code. Eigentlich ist es nur ein Statement, welches mittels einer Funktion die einzelnen Spalten verkettet und stets das gleiche Trennzeichen, hier ein Doppelpunkt dazwischen setzt. Der Start ist wie in der ersten Version. – Das Ergebnis ist dann ein Text, der das Aussehen einer Uhrzeit hat. Mit einem einzigen Klick den Datentyp auf Zeit ändern und Sie können die Abfrage schließen. Spätestens in dem neu erstellten Arbeitsblatt werden Sie erkennen, dass die letzte Datenzeile zwar die Einzelwerte der Zeit enthält, wegen der übermäßig vielen Stunden wird im Feld Uhrzeit aber nichts angezeigt. Und: Sie brauchen die Uhrzeit im Ergebnis-Blatt nicht zu formatieren, wenn Sie das typische 24-Stunden-Format verwenden wollen.
Lösung 3
Aufbauend auf Lösung 2 wird hier ein ähnlicher Weg beschritten. Zu Beginn wird aber nach der Umwandlung der Punkte zu Kommas die Ganzzahl dadurch erzeugt, dass der Text an der Stelle des Kommas gesplittet, geteilt wird. Dadurch wird der Nachkomma-Anteil in eine gesonderte Spalte geschrieben. Durch zwei Verknüpfungs-Vorgänge werden die einzelnen Teile dann zu einem Text zusammengefügt. – In der erzeugten Tabelle wird dann noch das Zahlenformat mit einer benutzerdefinierten Formatierung angepasst.
Epilog
Für alle Methoden per Power Query gilt, dass Sie in PQ problemlos die nicht mehr benötigten Spalten löschen können. Das Ergebnis wird dadurch nicht beeinträchtigt. – Für die Eingabe per Hand in Tabellenblätter ohne Trennzeichen gibt es beispielsweise hier Hinweise.
[NachObenLetzte Verweis=„ML: Zeitwerte ohne Doppelpunkte mit PQ”]