Office: (Office 2011 Mac) Problem mit Zeitformatierung und -Darstellung

Hallo Ihr Lieben,

wir erstellen Podcasts (Audioaufnahmen zum Anhören über das Smartphone) und um unseren Hörern das Navigieren durch die einzelnen Folgen zu erleichtern, bieten wir ihnen bei jeder unserer Folgen eine Kapitelübersicht. Nun ist es so, dass das Erstellen dieser Kapitelansichten per Schreiben in HTML immer sehr aufwändig ist, weshalb ich es gerne durch eine Excel-Formel erleichtern würde. Da wir einfach nur Textbausteine kombinieren müssen, sollte das eigentlich gut funktionieren.

Womit wir arbeiten

Unser Schnittprogramm spuckt uns eine CSV-Datei aus, in welcher jedes Kapitel dann eine eigene Zeile bekommt, die so aussieht:

"M1,Vorstellungsrunde und Einführung ins Thema,4:21.000,," (Also im Prinzip: "Marker-Nummer, Titel, Zeitcode,,")

Erste Schritte zur Aufteilung

Mit Hilfe von "Text in Spalten" kriege ich es schon mal hin, dass die Zelle aus der CSV-Datei aufgeteilt wird, sodass ich die Zeitangabe und den Titel in unterschiedlichen Zellen verfügbar habe. Anschließend brauche ich nur die unterschiedlichen HTML-Textbausteine in unterschiedliche Zellen eintragen und per &-Formel bzw. Verketten zusammenfügen.

Das geht soweit auch ganz gut, das einzige, was mir partout nicht gelingen will, sind die Zeitcodes der Kapitel. Die Darstellung im fertigen HTML-Baustein muss Minuten:Sekunden lauten bzw. Stunden:Minuten:Sekunden, falls eine Aufnahme mal die Stunde überschreitet.

Mit "=LINKS(C3;LÄNGE(C3)-4)" streiche ich in der Zelle mit der Zeitangabe (4:21.000) dann schon mal die letzten vier Stellen, da es sich dabei ja um die Zehntel, Hundertstel und Tausendstel handelt, die ich nicht brauche. Damit hätte ich in der Zelle dann "4:21". Nun beginnen die Probleme.

Problem 1: Formatierung der Zeiten

Ich möchte nun eine Sekunde von diesen Zeiten abziehen, da ein Kapitel ja von "Beginn Kapitel A bis Beginn Kapitel B minus 1 Sekunde" geht. Allerdings habe ich partout Probleme, Excel beizubringen, dass "4:21" demnach 4 Minuten und 21 Sekunden sind und "1:09:49" entsprechend 1 Stunde 9 Minuten und 49 Sekunden. Problem 1 liegt glaube ich darin, dass wenn ich die Zelle mit 4:21 als "[h]:mm:ss" formatiere, Excel denkt, die 4 stünde bei den Stunden und nicht bei den Minuten.

Wenn ich in die Zelle daneben "00:01" eintrage und diese von der Nachbarzelle abziehe, funktioniert es zumindest nicht, sondern nur, wenn alle Zellen mit "[h]:mm:ss" formatiert sind, wobei ich dann als Ausgabe auch 4 Stunden 20 Minuten und 59 Sekunden bekomme.

Problem 2: Darstellung der Ergebnisse

Wenn ich mit diesen Ergebniszellen nun arbeiten möchte und sie per "&" in die Formelbildung einbeziehen, wird nicht etwa "4:20" angezeigt (noch nicht mal 4:20:59), sondern eine ewig lange Kommazahl (für 4:20:59 zum Beispiel "0,181238425925926").

Interessante Randnotiz: wenn ich die ursprüngliche Zelle mit "4:21.000", also den Kapitelbeginn, wo ich die Zehntel, Hundertstel und Tausendstel noch nicht getilgt habe, mit einem "&" einbette, zeigt er es in der Formel richtig als 4:21 an.


So, sehr lange Rede, für ein vielleicht etwas abstraktes Problem. Aber wie schaffe ich es also, dass Minuten und Sekunden richtig erkannt und formatiert werden und das Ergebnis mit abgezogener Sekunde in besagter Zelle zur Formelerstellung richtig dargestellt wird? Ich habe euch meine Beispieldatei dazu mal angehangen.



Danke und Gruß

JayEmKay83

 

Moin!
Markiere nach Text in Spalten die Spalte C

• Strg+h
• Suchen nach: . (Punkt)
• Ersetzen durch: , (Komma)

Jetzt hast Du Zeiten im Format hh:mm,0
Lasse die Spalte markiert und ändere das Format auf Uhrzeit.

Gruß Ralf

 

Das ist schon mal ein sehr guter Teilschritt, danke! Aber trotzdem will das Ganze noch nicht funktionieren:

Wenn ich versuche, die Werte in diesen Zellen für den Bau der Zelle mit dem HTML-Code via &-Funktion zu entnehmen, schreibt er mir die vollen Werte rein – dort steht dann also "HTML-TEXT 4:21,959 usw." anstatt "4:21". Wenn ich das mit "=LINKS(D10;LÄNGE(D10)-4)" löse, indem ich einfach die letzten vier Stellen löschen lasse, ist dies für diesen Teil der Aufgabe okay, dann trägt er nur die "4:21" in die Zelle mit dem HTML ein.

Allerdings geht das Problem dann weiter: Wenn ich mir dann in eine Zelle ein "00:00:01" schreibe und diese auch mit Uhrzeit formatiere, um es dann von den Zeiten abzuziehen, funktioniert das einfach nicht.

Wenn ich die 00:00:01 von den "4:21,959" abziehe, bleibt die Zahl einfach unverändert. In meiner mit Uhrzeit formatierten Zelle steht dann "00:04:21".

Wenn ich die 00:00:01 von den "4:21" abziehe, kommen nur so lange richtige Zahlen heraus, wie die Minutenanzahl bis maximal 24 reicht, danach werden die Zahlen völlig falsch. Offensichtlich behandelt er die gekürzten Uhrzeiten schlicht wieder so, dass er bei "4:21" die vier als Stunden und die 21 als Minuten einordnet. Wenn ich dann aber zu Zahlen über 24 gelange (die damit größer als das Maximum eines Tages von 24 Stunden sind), wird die Zahl falsch.

Eine Idee, wie ich also mit diesen als Uhrzeit formatierten und richtigen Zahlen nun das Ende eines Kapitels berechnen kann, indem ich eine Sekunde davon abziehe und diese anschließend in meinen HTML-Code bekomme?

Danke und Gruß

JayEmKay83 :-)

 

Ja klar! ;)
Du musst noch den Zeitstempel auf eine Sekunde runden. (ob abrunden oder runden, musst Du entscheiden.
Da eine Sekunde ein 86.400stel Tag ist:
=UNTERGRENZE(A1;1/86400)
oder
=RUNDEN(A1*86400;0)/86400

Gruß Ralf

 

Hallo Ralf,

man, auf so etwas wäre ich nie gekommen – vielen Dank! Hast du auch noch eine Idee, wie man die dann subtrahierten Uhrzeiten so in die HTML-Formel gezogen bekommt, dass dort auch "04:20" steht? Wenn ich das per "=A9&B9&G9 usw." zusammenfüge, wird aus "00:04:20" auf einmal eine ganz lange Kommazahl.

Also wenn ich es dort direkt rausziehe, macht er eine lange Kommazahl draus. Wenn ich mit
"=TEXT(G9;"h")" die Stunden
"=TEXT(G9;"m")" die Minuten und
"=TEXT(G9;"s")" die Sekunden

in je einer Zelle extrahiere, bringt mich das der Lösung sehr nahe, aber wenn ich dort dann die Inhalte rausziehe, wird aus "04" halt auch "4".

Das wäre der letzte Meter, dann ist es geschafft ;-)

Vielen Dank und Lieben Gruß

JayEmKay83