Hallo,
ich habe folgendes Datenmaterial:
„XX hours YY minutes ZZ seconds“ oder
„YY minutes ZZ seconds“.
Wie kann ich daraus die Form:
xx:yy:zz machen für beide Ausgangsformen??
Ich bin schonmal soweit gekommen:
Zellwert:
0 hours 33 minutes 5 seconds
Formelwert in anderer Zelle zum übersetzen des Zellwertes:
=WENN(C26=„0“&" hours „&„33“&“ minutes „&„5“&“ seconds";„0“&":"&„33“&":"&„5“;0)
Damit erhalte ich:
0:33:5
Soweit so gut, jetzt sind aber die Zahlenangaben immer anders. Wie kann ich das abbilden?
Und weiterhin kann es sein, dass auch manchmal die „hours“ fehlen. Kann ich das über ein verschachteltes Wenn darstellen? (es soll immer dass Format hh:mm:ss herauskommen.
Danke schonmal im Voraus
Gruß Nic
Hallo,
eine Formel wäre zwar sicherlich möglich aber extrem aufwendig und unübersichtlich. Vor allem durch die ungleiche Form des Datum (Wegfall Stundenangabe) und der Zahlenwerte (einstellig, zweistellig).
Habe daher ein VBA-Funktion geschrieben, die ein Datumswert ausgibt.
Hier der Code, den Du im VBA-Editor in ein Modul eingeben musst:
Function UhrzeitErzeugen(UhrzeitTxt As String) As Double
Dim alleTeile As Variant
alleTeile = Split(UhrzeitTxt, " ")
Select Case UBound(alleTeile)
Case 5
UhrzeitErzeugen = TimeValue(alleTeile(0) & ":" & alleTeile(2) & ":" & alleTeile(4))
Case 3
UhrzeitErzeugen = TimeValue("00:" & alleTeile(0) & ":" & alleTeile(2))
End Select
End Function
In der Tabelle kann man die wie folgt darauf zurückgreifen.
\*\*\* Tabelle: 'Tabelle1' \*\*\*
A B
1 12 hours 5 minutes 3 seconds 12:05:03
2 03 minutes 45 seconds 00:03:45
3 1 hours 55 minutes 12 seconds 01:55:12
\*\*\* benutzte Formeln \*\*\*
B1: =uhrzeiterzeugen(A1)
B2: =uhrzeiterzeugen(A2)
B3: =uhrzeiterzeugen(A3)
Alternativ kannst Du auch die Datumsangabe in mehrere Hilfsspalten aufsplitten (überMenü „Daten->Text in Spalten“, Trennzeichen: Leerzeichen) und dann folgende Formel verwenden:
\*\*\* Tabelle: 'Tabelle1' \*\*\*
A B C D E F G
9 1 hours 55 minutes 12 seconds 01:55:12
\*\*\* benutzte Formeln \*\*\*
G9: =ZEIT(A9;C9;E9)
In beiden Fällen musst Du die Ergebnisspalte noch mit dem Zeitformat „hh:mm:ss“ belegen, dass eine einheitliche Ausgabe erfolgt.
Gruß, teste.
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
noch was leichteres gefunden
Es geht auch mit folgender Formel:
\*\*\* Tabelle: 'Tabelle1' \*\*\*
A B
1 12 hours 5 minutes 3 seconds 12:05:03
2 03 minutes 45 seconds 00:03:45
3 1 hours 55 minutes 12 seconds 01:55:12
\*\*\* benutzte Formeln \*\*\*
B1: =ZEITWERT(WENN(ISTFEHLER(FINDEN("hours";A1));"00:";"")&WECHSELN(WECHSELN(WECHSELN(A1;" hours ";":";1);" minutes ";":";1);" seconds";""))
B2: =ZEITWERT(WENN(ISTFEHLER(FINDEN("hours";A2));"00:";"")&WECHSELN(WECHSELN(WECHSELN(A2;" hours ";":";1);" minutes ";":";1);" seconds";""))
B3: =ZEITWERT(WENN(ISTFEHLER(FINDEN("hours";A3));"00:";"")&WECHSELN(WECHSELN(WECHSELN(A3;" hours ";":";1);" minutes ";":";1);" seconds";""))
Ausgabespalte wieder mit Format „hh:mm:ss“ belegen.
1 „Gefällt mir“
Super und vielen Dank…
Diese einfachere Form ist was für mich und ich kann diese Anwenden.
Jetzt habe ich zusätzlich noch im den Datensätzen entdeckt, dass ich zusätzlich noch die Angabe
„ZZ Seconds“ habe (also ohne Stunden und Minuten).
Wie baue ich das in diese Form ein?
Gruß Nic
Super und vielen Dank…
Diese einfachere Form ist was für mich und ich kann diese
Anwenden.Jetzt habe ich zusätzlich noch im den Datensätzen entdeckt,
dass ich zusätzlich noch die Angabe„ZZ Seconds“ habe (also ohne Stunden und Minuten).
Wie baue ich das in diese Form ein?
=ZEITWERT(WENN(ISTFEHLER(FINDEN("hours";A1));"00:";"")&WENN(ISTFEHLER(FINDEN("minutes";A1));"00:";"")&WECHSELN(WECHSELN(WECHSELN(A1;" hours ";":");" minutes ";":");" seconds";""))
nur zur Info:
Übrigens kann man bei den Wechseln-Ausdrücken auch die „;1“ als letzten Parameter weglassen (ist optional). Dadurch ist die Formel etwas kürzer.
„Finden“ kann evtl. auch durch Suchen ersetzt werden. Suchen hat das gleiche Ergebnis, nur das Groß-Kleinschreibung unerheblich sind und Platzhalter verwendet werden können.
Gruß Nic
Viele Grüße tester
1 „Gefällt mir“