Zeit in Excel aus .txt Datei richtig formatieren

Question

Hallo K. habe eine Musterdatei angehängt, wie kann ich in Excel die Spalte H Formatieren sodass in Zelle K4 und K5 die richtige Zeit angezeigt wird und nicht wie L4 und L5. Danke

Answer 1

Guten Morgen,

es wäre interessant zu wissen wie zumindest die letzten 3 Zeilen der Spalte H (die das Problem verursachen) in der txt ausgesehen haben - das muss man jedenfalls schon beim Import richten im Excel selbst ist es zu spät

Answer 2

Guten Morgen Anonym,

im Excel selbst ist es zu spät

Falsch! Schon mal was von VBA gehört?? Schleife über die Spalten und schon ist's fertig ...

Answer 3

Hallo guten Tag, leider kann ich kein vba schreiben. Danke

Answer 4

Hallo Jelena,

zunächst mal ist das Ergebnis deiner Formel korrekt. Der Erstkleinste Wert mit W in deiner Liste ist nämlich nicht 48,37 sondern 01:11,3. Eine Minute 11 Sekunden und 300 Millisekunden entspricht im Hintergrund dem Dezimalwert 0,000825463 der deutlich kleiner als 48,37 ist.

Warum hast du überhaupt mal Dezimalwerte und mal Zeitangaben in deiner Liste stehen? Wenn du beide Formate behalten willst musst du die Spalte I mit in deine Formel einbeziehen. Dann klappts auch.

=WENNFEHLER(KKLEINSTE(WENN(D2:D20="w";WENN(I2:I20="Standard";H2:H20));1);0)

Falls das nicht die gewünschte Lösung ist, solltest du vielleicht erstmal die Frage von Anonym beantworten. Der (oder die) hat nämlich völlig Recht damit, dass es auf die Quelldaten ankommt, die man natürlich schon beim Import richtig interpretieren sollte. Bei Datumsangaben z.B. kann Excel nicht wissen ob es es je nach Herkunft der Datei beim 03.05.23 um den 03. Mai oder den 05. März handelt. Sowas muss man im Importassistenten mitgeben. Mit VBA im Nachhinein an die Daten zu gehen ist zwar möglich, aber letztlich nur aufwendige Kosmetik

Gruß Mr. K.

Answer 5

Hallo Jelena,

ich denke es ist keine Frage der Formatierung. Der Inhalt der letzten drei Zellen ist genau das, was in deiner Formel als Ergebnis herauskommt. Dort stehen Werte im Minutenbereich. In den oberen Zellen sind komplette Daten, also Tag, Monat, Jahr, Stunde Minute eingetragen. Durch die Formatierung werden aber nur Minuten und Sekunden ausgegeben, deshalb sehen die so aus, wie die Werte unten, sind aber um Größenordnungen größer.

Gruß computerschrat

edit: Mr. K war schon wieder schneller :-)

Answer 6

Hallo K. Die Spalte (i) ist nur zum besseren Verständnis diese wird nicht kopiert, sie zeigt nur das Format an beim importieren. Danke

Answer 7

Hey Jelena,

du willst also nicht verraten, warum du verschiedene Zahlenformate in derselben Spalte verwendest und wie die Quelldaten aussehen? OK.

Wenn ich dich richtig verstehe, möchtest du nur die Werte in deiner Formel berücksichtigen, denen das Zahlenformat Standard zugewiesen ist. Da kommst du aber um die Hilfsspalte I leider nicht umhin. Damit du die aber nicht von Hand füllen musst, kannst du das Format auch per Benutzerdefinierter Formel auslesen. Dazu einfach in ein allgemeines Modul die folgenden 3 Zeilen schreiben:

Function Zahlenformat(r As Range)
  Application.Volatile
  Zahlenformat = r.NumberFormat
End Function

Anschließend kannst du mit der Formel =Zahlenformat(H2) das Format auslesen und die Formel runterziehen. Für die Ermittlung der KKleinsten nutzt du dann einfach die Formel aus Antwort 4. Nur, dass du nun anstatt Standard das englische Wort General verwendest. 

Achtung, die Formel aktualisiert sich nicht automatisch sofort, wenn du ein Zahlenformat änderst, sondern erst, wenn sich aus irgendeinem Grund die Formeln im Blatt neu berechnen. Das kannst du auch im Menü Formeln über das Symbol "neu berechnen" oder die Taste F9 selbst anstoßen.

Gruß Mr. K.

Answer 8

Hallo K. habe die Quelldaten (Freundschaftsrennen.txt) hochgeladen bei meinem Programm nur .txt speichern. Diese Textdaten werden dann kopiert und in Excel eingefügt.

Schnellste Zeit der Herren = PIXNER, Hannes    M    2003    TEAM Hangover    ---    47,48

Schnellste Zeit der Damen = PICHLER, Lara    W    2007    TEAM BLITZ    ---    48,37

Answer 9

Hallo Jelena,

Das sind doch hoffentlich keine Originaldaten sondern nur ein paar Beispieldatensätze, die den Aufbau der Quelldaten zeigen, oder?

Mit Verlaub: Die Quelldaten sind shit. Welcher Depp hat denn die Ausgabe zur Txt so programmiert, dass Fließkommazahlen und Zeitangaben in einer Spalte vorkommen? Da lässt sich leider doch nichts mit  dem Import-Assistenten machen. Evtl. mit Power Query, aber da kenn ich mich leider noch nicht aus. Vielleicht hat wer anders dazu Lust das zu testen? So wie ich das sehe, müssen wir doch mit Kosmetik da ran gehen.

Die Antwort auf deine Ausgangsfrage, wie man die Spalte H formatieren muss lautet wie folgt: Führe in einem allgemeinen Modul direkt nach dem Import den folgenden Code aus.

Sub Formatieren()
  
  For Each c In Range(Range("H2"), Cells(Rows.Count, "H").End(xlUp)).Cells
    If c.Value2 > 1 Then
      c.NumberFormat = "mm:ss.0"
      c.Value = CDec(1 / 24 / 60 / 60 * c.Value2)
    Else
      c.NumberFormat = "mm:ss.0"
    End If
  Next c

End Sub

Damit wird die Spalte H auf ein einheitliches Format gebracht wobei den Fließkommazahlen ein 0: vorangestellt wird, da es sich offensichtlich ebenfalls um Sekunden und Millisekunden handelt. Anschließend sollte deine Formel mit KKleinste funktionieren. Probiers mal aus.

Gruß Mr. K.

Answer 10

Hallo K. ich bekomme zwar eine Fehlermeldung doch es scheint zu funktionieren. Danke