Frage von manimasu, 53

Zeitformat: Wer kann mir beim Erstellen einer Excelformel helfen?

Hallo, ich bin am Berechnen von Feldern in einer Exceltabelle und möchte, daß mir in einem Feld A2 aufgrund einer Zahl in Feld A1 (Sekunden), eine Zeit in diesem Format berechnet wird: xxhyy'zz'' oder numerisch: 5h34'20''.

Ich bin mit meiner Formel --> =GANZZAHL(A1/60)&"'"&REST(A1;60)&"''" <--

soweit, dass ich in A2 die Minuten und Sekunden angezeigt bekomme. Allerdings werden eben auch überflüssige Werte angegeben. Bei 300s erscheint 5'0''. Oder bei 30s erscheint 0'30''.

  1. Wie bekomme ich es hin, daß diese 'leeren' Werte nicht angezeigt werden?

  2. Wie bekomme ich es hin, daß Werte über 59'59'' in Stunden angezeigt werden, also z.B. 3800s nicht als 63'20'' sondern 1h3'20''?

Danke schon mal für die Hilfe!

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 44

=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(A1>=60;GANZZAHL(REST(A1;3600)/60)&"'";"")&REST(REST(A1;3600);60)&"''"

Das ist aber ein ziemlicher Aufriss nur für die Anzeige...

Kommentar von Ninombre ,

=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(UND(A1>=60;REST(A1;60)>0);GANZZAHL(REST(A1;3600)/60)&"'";"")&WENN(REST(A1;60)>0;REST(REST(A1;3600);60)&"''";"")

deckt noch den Fall ab, dass keine Sekunden oder Minuten angezeigt werden sollen, bspw. bei 3600s

3601 s sollten aber nach meinem Verständnis als 1h0'1'' angezeigt werden, oder? Die Notierung 1h1'' ist ungewöhnlich bzw. leicht missverständlich

Kommentar von Oubyi ,

DH!
Allerdings:
Ich habe mich noch nicht in Deine Formel vertieft, aber sie - zufälligerweise - gerade mit A1: 300 ausprobiert, und da bleibt die Formelzelle leer!
Ich schau selbst nochmal genauer hin, aber vlt. bist du ja schneller.

Kommentar von Oubyi ,

P.S.:
Ich glaube:
...REST(A1;60)>=0...
reicht schon.
Muss aber nochmal genauer schauen.

Kommentar von Oubyi ,

Ich glaube das war's. Also:

=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(UND(A1>=60;REST(A1;60)>=0);GANZZAHL(REST(A1;3600)/60)&"'";"")&WENN(REST(A1;60)>=0;REST(REST(A1;3600);60)&"''";"")

Wobei das >= beim Sekundenwert Ansichtssache ist, je nachdem, ob 0'' angezeigt werden soll oder nicht.

Kommentar von Ninombre ,

das reisst allerdings wieder die Lücke bei 3600, dass hier keine 1h0' angezeigt werden soll. Vielleicht so noch:

=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(UND(REST(A1;3600)>0;A1>60);GANZZAHL(REST(A1;3600)/60)&"'";"")&WENN(REST(A1;60)>0;REST(REST(A1;3600);60)&"''";"")

wobei ich es dann für heute bleiben lasse, mir raucht das Hirn

Kommentar von Oubyi ,

Stimmt!
Aber so bleibt bei 60 die Formelzelle leer
Was sich allerdings leicht mit:
=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(UND(REST(A1;3600)>0;A1>60);GANZZAHL(REST(A1;3600)/60)&"'";"")&WENN(REST(A1;60)>0;REST(REST(A1;3600);60)&"''";"")&WENN(A1=60;"60''";"")
"retten" lässt.
Und jetzt schließe ich mich Deiner letzten Bemerkung an 😉

Kommentar von manimasu ,

Danke Ninombre, diese Formel ist genial und löst fast alle meine Probleme. Ich finde es nach reiflicher Überlegung sogar klarer, wenn 0' dargestellt werden, wenn es noch Stunden und Sekunden gibt. Allerdings bleibt bei 60s und dessen Vielfachen (120s, 180s, ...) das Feld komplett leer. Wie könnte ich das verhindern?

Danke schon mal an Alle, die mir die Augen geöffnet haben, wie man sowas angeht.

Kommentar von Ninombre ,

diesen Fehler hat Oubyi im Kommentar über Dir ausgebaut

=WENN(A1>=3600;GANZZAHL(A1/3600)&"h";"")&WENN(UND(REST(A1;3600)>0;A1>60);GANZZAHL(REST(A1;3600)/60)&"'";"")&WENN(REST(A1;60)>0;REST(REST(A1;3600);60)&"''";"")&WENN(A1=60;"60''";"")

Kommentar von Oubyi ,

und dessen Vielfachen (120s, 180s, ...)

Kann eigentlich nicht sein, da die Minuten angezeigt werden. Bei meinem Test ist das jeden falls so.

Expertenantwort
von Oubyi, Community-Experte für Excel, 37

Das einfachst ist, Du formatierst A2 Benutzerdefiniert (Rechtsklick/ Zelle formatieren/ Zahlen/ Benutzerdefiniert/ Typ:) als:
h"h"m"'"s"''"
und trägst dann diese Formel ein:
=A1/24/60/60
bzw.:
=A1/86400
Allerdings werden dann auch die 0-Werte mit angezeigt.
Aber warum auch nicht?
Sonst musst Du allerdings auf Ninombres Formeln (DH!) zurückgreifen.

Kommentar von Ninombre ,

mit zunehmendem Kopfzerbrechen nimmt die Variante, einfach die Standardformatierung für Uhr/Zeit zu verwenden an Charme zu

Kommentar von manimasu ,

:)...

Expertenantwort
von Iamiam, Community-Experte für Excel, 19

wie wärs mit diesem ganz anderen Ansatz::

=TEXT(A1/24/60/60;"[h]""h ""m""' ""s""''""")

Null-Anzeige:  0h 0' 0''

Wenn die Zahl durch /24/60/60 anders geteilt wird, geht das auch über ein Bendef Zellformat. (ich finde es übrigens einfacher, Format-Experimente über diese Textformel zu machen!)

Wenn Du aber partout die 0-Werte unterdrücken willst, dann (etwas länglicher):

=WECHSELN(WECHSELN(WECHSELN(TEXT(A1/24/60/60;" [h]""h ""m""' ""s""''""");"' 0''";"'");"h 0'";"h");" 0h ";"_")

den _ kannst du auch noch rausnehmen, dann sieht man bei 0 eben überhaupt nichts mehr.

Anzeige bei Zahl

  • 1: _1''
  • 60: _ 1'
  • 61: _ 1' 1''
  • 3601: 1h 1''
  • 91111: 25h 18' 31''

ist das das, was du wolltest?

Kommentar von Iamiam ,

Das ist ein Text, den xl wieder nur mit ähnlichen Klimmzügen in eine rechenbare Zahl zurückverwandeln könnte (es ginge aber schon!). Nur:

Du hast ja noch in A1 die Ausgangszahl in Sekunden, und die als

=A1/24/60/60 ergeben eine Zahl, die sich in jedem Zeitformat darstellen lässt!

Und wenn Du negative Sekundenzahlen hättest (zB soundsoviele Sekunden vor/anschließend nach dem Start) ginge das mit einer Erweiterung auch:

...Wenn(A1<0;"-";"")&Text(abs(A1)/24...

(hab das jetzt allerdiings nur als Trockenübung gemacht)

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten