Supportnet / Forum / Tabellenkalkulation
Excel-Zeiten runden
Frage
Möchte Uhrzeiten, die +1 Minute über ne volle Viertelstunde gehen darauf abrunden, und welche, die +2 Minuten darübergehen auf die nächste Viertelstunde aufrunden...
Kann mir jemand helfen!??
Danke
Antwort 1 von Chochoc
Keiner ne Ahnung??
Antwort 2 von Flupo
Das Problem ist sehr schwierig zu lösen.
Das liegt daran, dass Excel Datums- und Zeitwerte besonders behandelt. Intern sind diese Werte normale Dezimalzahlen (ganz intern natürlich Binär). Vor dem Komma steht das Datum vom 0.1.1900 begonnen zu zählen. Der 2.5.2005 enspricht somit der Zahl 38474.
Nach dem Komma kommen die Uhrzeiten. Die 24 Stunden eines Tages werden linear in Nachkommastellen umgerechnet, d.h. 38474,5 entspricht dem 2.5.2005 12:00 Uhr.
Zurück zu deinem Problem:
Da sich die einzelnen Minutenwerte nur sehr gering unterscheiden, lässt sich der Wert auch nicht vernünftig runden.
Bsp.:
11:14 0,4680555555556
11:15 0,4687500000000
11:16 0,4694444444444
11:17 0,4701388888889
Wenn du diese Werte auf vier Stellen nach dem Komma rundest, wird nur 11:16 auf 11:15 abgerundet. Bei drei Stellen nach dem Komma wird 11:14 zu 11:13.
Das Verhalten ist wahrscheinlich auch bei jedem Stundenwert ein anderes - also nicht zu gebrauchen.
Um das Problem zu lösen bleibt eigentlich nur der Umweg, die Zeit in eine Zeichenkette umzuwandeln, dann die Minuten mittels WENN-Funktion runden und dann wieder zurück in das Zeitformat umwandeln.
Gruss Flupo
Das liegt daran, dass Excel Datums- und Zeitwerte besonders behandelt. Intern sind diese Werte normale Dezimalzahlen (ganz intern natürlich Binär). Vor dem Komma steht das Datum vom 0.1.1900 begonnen zu zählen. Der 2.5.2005 enspricht somit der Zahl 38474.
Nach dem Komma kommen die Uhrzeiten. Die 24 Stunden eines Tages werden linear in Nachkommastellen umgerechnet, d.h. 38474,5 entspricht dem 2.5.2005 12:00 Uhr.
Zurück zu deinem Problem:
Da sich die einzelnen Minutenwerte nur sehr gering unterscheiden, lässt sich der Wert auch nicht vernünftig runden.
Bsp.:
11:14 0,4680555555556
11:15 0,4687500000000
11:16 0,4694444444444
11:17 0,4701388888889
Wenn du diese Werte auf vier Stellen nach dem Komma rundest, wird nur 11:16 auf 11:15 abgerundet. Bei drei Stellen nach dem Komma wird 11:14 zu 11:13.
Das Verhalten ist wahrscheinlich auch bei jedem Stundenwert ein anderes - also nicht zu gebrauchen.
Um das Problem zu lösen bleibt eigentlich nur der Umweg, die Zeit in eine Zeichenkette umzuwandeln, dann die Minuten mittels WENN-Funktion runden und dann wieder zurück in das Zeitformat umwandeln.
Gruss Flupo
Antwort 3 von Aliba
Hi Chochoc,
probiers mal hiermit (Uhrzeit steht in A1)
=GANZZAHL(A1*24)/24+(WENN(REST(MINUTE(A1);15)>1;AUFRUNDEN(MINUTE(A1)/15;0)*15;ABRUNDEN(MINUTE(A1)/15;0)*15))/1440
CU Aliba
probiers mal hiermit (Uhrzeit steht in A1)
=GANZZAHL(A1*24)/24+(WENN(REST(MINUTE(A1);15)>1;AUFRUNDEN(MINUTE(A1)/15;0)*15;ABRUNDEN(MINUTE(A1)/15;0)*15))/1440
CU Aliba
Antwort 4 von Event
Hallo
=WENN(REST(B1;0,0104166)>0,0007;OBERGRENZE(B1;0,0104166);UNTERGRENZE(B1;0,0104166 ))
im Beispiel enthält B1 die Uhrzeit
Gruß
=WENN(REST(B1;0,0104166)>0,0007;OBERGRENZE(B1;0,0104166);UNTERGRENZE(B1;0,0104166 ))
im Beispiel enthält B1 die Uhrzeit
Gruß
Antwort 5 von Chochoc
Danke an alle...
Aliba und Event, Eure Antworten sind cool: einziger Makel, die Uhrzeit 0:58 wird ebenfalls augerundet auf 1:00, soll aber auf 0:45...
Wie gesagt immer volle 1/4 Stunden, wobei eine Minute drüber / drunter okay ist, zwei aber schon zur nächsten bzw vorherigen 1/4 Stunde auf-/abgerundet werden soll!
Greetz
Aliba und Event, Eure Antworten sind cool: einziger Makel, die Uhrzeit 0:58 wird ebenfalls augerundet auf 1:00, soll aber auf 0:45...
Wie gesagt immer volle 1/4 Stunden, wobei eine Minute drüber / drunter okay ist, zwei aber schon zur nächsten bzw vorherigen 1/4 Stunde auf-/abgerundet werden soll!
Greetz
Antwort 6 von Aliba
Hi chochoc,
verstehe das nicht ganz.
Du schreibst:
"Möchte Uhrzeiten, die +1 Minute über ne volle Viertelstunde gehen darauf abrunden, und welche, die +2 Minuten darübergehen auf die nächste Viertelstunde aufrunden... "
Volle 1/4 - Stunden sind für mich:
0:15 0:30 0:45 1:00 usw...
0:16 0:31 0:46 0:01 werden laut Deinem Schreiben abgerundet, alles ander wird auf 1/4-Stunden aufgerundet.
Warum soll nun 0:58 auf 0:45 abgerundet werden?
Da fehlt mir irgendwo die Logik.
CU Aliba
verstehe das nicht ganz.
Du schreibst:
"Möchte Uhrzeiten, die +1 Minute über ne volle Viertelstunde gehen darauf abrunden, und welche, die +2 Minuten darübergehen auf die nächste Viertelstunde aufrunden... "
Volle 1/4 - Stunden sind für mich:
0:15 0:30 0:45 1:00 usw...
0:16 0:31 0:46 0:01 werden laut Deinem Schreiben abgerundet, alles ander wird auf 1/4-Stunden aufgerundet.
Warum soll nun 0:58 auf 0:45 abgerundet werden?
Da fehlt mir irgendwo die Logik.
CU Aliba
Antwort 7 von Event
Hallo
@Aliba mir auch !?
Gruß
@Aliba mir auch !?
Gruß
Antwort 8 von Chochoc
Sorry, verstehe, dass das etwas komisch ist:
Es geht um Arbeitszeiten:
Wer um 16.01 Uhr kommt, der wird ab 16.00 bezahlt
Wer um 16.02 Uhr kommt, der wird ab 16.15 bezahlt
Wer um 15.59 Uhr geht, der wird bis 16.00 bezahlt
Wer um 15.58 Uhr geht, der wird bis 15.45 bezahlt
Hoffe ich hab nix vergessen...
Es geht um Arbeitszeiten:
Wer um 16.01 Uhr kommt, der wird ab 16.00 bezahlt
Wer um 16.02 Uhr kommt, der wird ab 16.15 bezahlt
Wer um 15.59 Uhr geht, der wird bis 16.00 bezahlt
Wer um 15.58 Uhr geht, der wird bis 15.45 bezahlt
Hoffe ich hab nix vergessen...
Antwort 9 von Chochoc
SPrich ich muss wohl zwischen kommen und gehen differenzieren!
MfG
MfG
Antwort 10 von Aliba
Hi Chochoc,
da hast Du recht. Du brauchst 2 Formeln, die genau das Gegenteil machen. Die Formeln , die Dir jetzt vorliegen verwendest Du für Kommen und ohne jetzt getestet zu haben, sollte es ausreichen, wenn Du in meiner Formel Auf- und Abrunden vertauscht für Gehen. bzw. OBER- und UNTERGRENZE in Events Formel.
Das ist wohl das "unternehmerfreundliche Runden"
CU Aliba
da hast Du recht. Du brauchst 2 Formeln, die genau das Gegenteil machen. Die Formeln , die Dir jetzt vorliegen verwendest Du für Kommen und ohne jetzt getestet zu haben, sollte es ausreichen, wenn Du in meiner Formel Auf- und Abrunden vertauscht für Gehen. bzw. OBER- und UNTERGRENZE in Events Formel.
Das ist wohl das "unternehmerfreundliche Runden"
CU Aliba
Antwort 11 von Chochoc
Sorry, Events Formel macht da total schlapp und deine funzt mit dem vertauschen bei mir auch nicht...
Dachte selbst, dass es so gehen sollte, aber nix..
Verstehe nicht warum, aber er scheint dabei nicht der Logik zu folgen, denn die Formel ist meiner Meinung nach richtig!
MfG
Dachte selbst, dass es so gehen sollte, aber nix..
Verstehe nicht warum, aber er scheint dabei nicht der Logik zu folgen, denn die Formel ist meiner Meinung nach richtig!
MfG
Antwort 12 von Aliba
Hi Chochoc,
man sollte halt doch erstmal testen und nicht nur annehmen:
=GANZZAHL(C1*24)/24+(WENN(REST(MINUTE(C1);15)>=14;AUFRUNDEN(MINUTE(C1)/15;0)*15;ABRUNDEN(MINUTE(C1)/15;0)*15))/1440
CU Aliba
man sollte halt doch erstmal testen und nicht nur annehmen:
=GANZZAHL(C1*24)/24+(WENN(REST(MINUTE(C1);15)>=14;AUFRUNDEN(MINUTE(C1)/15;0)*15;ABRUNDEN(MINUTE(C1)/15;0)*15))/1440
CU Aliba
Antwort 13 von Chochoc
Danke... Super!
Antwort 14 von Chochoc
Noch eine Hilfe brauch ´ ich:
Wenn Zeit kleiner als 4 h, dann aufrunden auf ganze Stunde!
Mfg
Wenn Zeit kleiner als 4 h, dann aufrunden auf ganze Stunde!
Mfg
Antwort 15 von Aliba
Hi Chochoc,
Zeit steht in F1:
=WENN(F1<4/24;(GANZZAHL(F1*24)+1)/24;F1)
CU Aliba
Zeit steht in F1:
=WENN(F1<4/24;(GANZZAHL(F1*24)+1)/24;F1)
CU Aliba
Antwort 16 von Aliba
Hi, nochmal ich
oder natürlich viel einfacher:
=MAX(F1;4/24)
CU Aliba
oder natürlich viel einfacher:
=MAX(F1;4/24)
CU Aliba
Antwort 17 von Chochoc
Hammer...letzte Frage ;-) :
Hab ne Bedingung, dass wenn ne Uhrzeit kleiner 6 Uhr morgens, also z.b. 23 uhr...dann...
Mein Problem, Excel kapiert nicht, dass das noch der Tag vorher ist und dementsprechend nix...
Wie sag ich dem Proggie, dass 6 Uhr morgens später ist als 23 Uhr - obwohl in der Formel 23<6 !?
MfG und Danke nochmal!
Hab ne Bedingung, dass wenn ne Uhrzeit kleiner 6 Uhr morgens, also z.b. 23 uhr...dann...
Mein Problem, Excel kapiert nicht, dass das noch der Tag vorher ist und dementsprechend nix...
Wie sag ich dem Proggie, dass 6 Uhr morgens später ist als 23 Uhr - obwohl in der Formel 23<6 !?
MfG und Danke nochmal!
Antwort 18 von Aliba
Hi Chochoc,
hier gehts doch scheinbar um eine tagübergreifende Nachtschicht. Willst Du hier die Differenz zwischen Kommt und Geht ermitteln??
Wenn ja, dann:
=(A1>B1)+B1-A1
Wobei in A1 der Kommt-Wert und in B1 der Geht-Wert steht.
CU Aliba
hier gehts doch scheinbar um eine tagübergreifende Nachtschicht. Willst Du hier die Differenz zwischen Kommt und Geht ermitteln??
Wenn ja, dann:
=(A1>B1)+B1-A1
Wobei in A1 der Kommt-Wert und in B1 der Geht-Wert steht.
CU Aliba
Antwort 19 von Chochoc
Hi Aliba...
Differenz wäre ja noch einfach, ich brauch das in einer Bedingung, in der ich z.b. sage, wenn 23<6 Uhr (was so ist), dann mach mir ....
D.h. ich vermute, dass ich es vorformatieren muss, denn wenn er nicht kapiert, dass 23 in dem Fall kleiner ist als 6, dann wird er die dann-funktion nicht erfüllen, verstehst Du???
MfG
Differenz wäre ja noch einfach, ich brauch das in einer Bedingung, in der ich z.b. sage, wenn 23<6 Uhr (was so ist), dann mach mir ....
D.h. ich vermute, dass ich es vorformatieren muss, denn wenn er nicht kapiert, dass 23 in dem Fall kleiner ist als 6, dann wird er die dann-funktion nicht erfüllen, verstehst Du???
MfG
Antwort 20 von Event
Hallo
vielleicht hilft Dir das weiter :
=WENN(C2-B2<0;1-B2+C2;C2-B2)
C2 ist Stopzeit , B2 ist Startzeit
Gruß
vielleicht hilft Dir das weiter :
=WENN(C2-B2<0;1-B2+C2;C2-B2)
C2 ist Stopzeit , B2 ist Startzeit
Gruß
Antwort 21 von Aliba
Hi Chochoc,
dann formuliere doch bitte mal, was Du machen möchtest, denn 23 Uhr ist nun halt mal nicht kleiner als 6 Uhr. Und wo stehen denn diese Werte? Wie ist denn deine Tabelle aufgebaut?
Erklär doch mal näher.
CU Aliba
dann formuliere doch bitte mal, was Du machen möchtest, denn 23 Uhr ist nun halt mal nicht kleiner als 6 Uhr. Und wo stehen denn diese Werte? Wie ist denn deine Tabelle aufgebaut?
Erklär doch mal näher.
CU Aliba
Antwort 22 von Aliba
Hi Chochoc,
formuliere doch bitte mal etwas näher, was denn dann geschehen soll.
Wo stehen denn diese Daten 6 und 23 ?
Was bedeuten Sie?
Eine nähere Kenntnis über die Struktur Deiner Tabelle und dessen, was Du erreichen willst wäre nun doch sehr hilfreich.
CU Aliba
formuliere doch bitte mal etwas näher, was denn dann geschehen soll.
Wo stehen denn diese Daten 6 und 23 ?
Was bedeuten Sie?
Eine nähere Kenntnis über die Struktur Deiner Tabelle und dessen, was Du erreichen willst wäre nun doch sehr hilfreich.
CU Aliba
Antwort 23 von Chochoc
Sorry wenn Du meine Erklärung nicht verstehst, rein wertmässig ist es nicht kleiner, aber zeitmässig ist es das... und ich möchte eine Bedingung erstellen, wonach der Wert 23 Uhr zwischen 22 und 6 Uhr liegen muss damit die Pause auf der Nachtschicht abgezogen wird...
Also geht es drum, dass ich Excel sage, wenn 23+ 4 NS (Normalstunden) < 6 Uhr, dann zieh mir von der Arbeitszeit 15 min ab.
Alles soweit kein Problem, bis darauf, dass ich per vorformatieren der Zelle mit 6 Uhr irgendwie sagen muss, dass sie bspw. der 3. Mai ist und nicht der zweite!!
Weiss auch, dass das geht, nur nicht mehr wie...
MfG
Also geht es drum, dass ich Excel sage, wenn 23+ 4 NS (Normalstunden) < 6 Uhr, dann zieh mir von der Arbeitszeit 15 min ab.
Alles soweit kein Problem, bis darauf, dass ich per vorformatieren der Zelle mit 6 Uhr irgendwie sagen muss, dass sie bspw. der 3. Mai ist und nicht der zweite!!
Weiss auch, dass das geht, nur nicht mehr wie...
MfG
Antwort 24 von Aliba
Hi chochoc,
das geht natürlich, aber dann musst Du bei der Erfassung auch das Datum mit erfassen. Die Formatierung der Zelle kannst Du auf hh:mm einstellen, dann wird das Datum nicht mit angezeigt, aber erfassen musst du das dann mit.
CU Aliba
das geht natürlich, aber dann musst Du bei der Erfassung auch das Datum mit erfassen. Die Formatierung der Zelle kannst Du auf hh:mm einstellen, dann wird das Datum nicht mit angezeigt, aber erfassen musst du das dann mit.
CU Aliba