HERBERS Excel-Forum - das Archiv

Thema: Arbeitszeiten ohne gewisse Zeiten rechnen

Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Guten Tag Zusammen

Ich bräuchte eure Hilfe weil ich keine Praktikable Lösung für meine Probleme gefunden habe.
Es geht darum, dass ich gewisse Zeiten bei Arbeitszeiten berechnen muss.
Dort habe ich das Problem, dass die Zeiten über Mitternacht mit einem Zusatz «+1» angezeigt werden.
Ich habe mal eine Lösung über mehrere Zellen gefunden, indem ich die Zeiten nach Mitternacht umgewandelt habe. Also zb. 18:30 wäre 1850 und 01:00 wäre dann 2500.
Sieht für euch sicherlich katastrophal aus. Ich weiss nicht, wie ich die Formeln alle in eine Zelle schreiben könnte.

Das eigentliche Problem ist jetzt aber, wie kann ich die Zeiten, welche nicht dazuzählen, wie z.B. wenn ein Mitarbeiter Bezug einer Zeit macht, außer Acht lassen kann. Das kann irgendwo in der Reihe sein.

Ich hoffe die Beispieldatei ist aussagekräftig genug.


Lange Rede: Ich wäre mega froh, wenn ihr mir auf die Sprünge helfen könntet.

Wenn etwas unklar ist, Bitte einfach Fragen und jetzt schon mal vielen vielen dank.


https://www.herber.de/bbs/user/175592.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
"wenn ein Mitarbeiter Bezug einer Zeit macht" ? Was heisst denn das genau?
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Ich würde ausserdem den Quatsch mit +1 als Text weglassen (mit Text kann man nicht rechnen) und es der Formel zur Arbeitszeitberechnung überlassen, das zu korrigieren.
Guckst du hier:
https://www.herber.de/bbs/user/175594.xlsx

Egal ob die Zeit über Mitternacht geht oder nicht, es wird korrekt berechnet.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Hallo Onur.

Leider ist das mit dem +1 systembedingt weil die Daten aus einem anderen Programm so hineinkopiert werden.
Ich kann das leider nicht ändern.

Gruss
Benj
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
"weil die Daten aus einem anderen Programm so hineinkopiert werden" ?
WELCHE Daten? Die auf "63h Kontrolle" oder die auf "SPX Kopie" ?
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Die Daten auf dem Sheet SPX Kopie sind die Rohdaten, welche aus dem anderen Programm hineinkopiert wurden.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Ich habe mal in AW8:AW10 meine Formeln eingetragen. Sie berücksichtigen "Bezug" und "+1".
Teste sie mal:
https://www.herber.de/bbs/user/175595.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Oh mega. Es ist fast Perfekt. Mega.

AX20 ist weg. aber AX21 ist noch da.
Könntest du das auch noch entfernen? Dass immer beide Zeiten, Start und Ende des Bezuges weg sind.

Viiiielen dank.

https://www.herber.de/bbs/user/175597.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Sorry Nochmals. hab gerade gesehen, das AX17 fehlt... Mitternacht wird mit 00:00+1 angezeigt.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
"AX20 ist weg. aber AX21 ist noch da. " ???
Verstehe dich nicht - Was ist weg und was ist noch da ?
Was soll das denn bedeuten?
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Entschuldige. Evt. habe ich etwas falsch interpretiert.
Ich meine folgendes:

AX20 bis AX23 beschreibt den Bezug der Überzeit.
Die Startzeit steht in AX20 und die Endzeit steht in AX21.
Mit deiner Formel ist AX20 (00:00+1) leer aber bei AX21 steht noch 01:00.

Ich meinte zuerst, dass Fehlen bei AX20 sei so gewollt, damit diese bei der Berechnung der Zeiten gleich ignoriert würden.
Aber so wie ich das jetzt sehe, ist das Problem bei Mitternacht mit der 00:00+1, dass die Zelle leer bleibt und nicht wegen dem Bezug.

Würde das gehen, dass, wenn unten Bezug steht, dass die oberen Zellen, wie hier im Beispiel AX20 und AX21 leer bleiben würden?
Wäre das eine Lösung um den Start und Ende des gesamten Tages zu berechnen?

AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Du hast mir ja auch nicht erklärt, was genau passieren soll, wenn dort "Bezug " steht - ganz im Gegenteil:
"wie kann ich die Zeiten, welche nicht dazuzählen, wie z.B. wenn ein Mitarbeiter Bezug einer Zeit macht, außer Acht lassen kann."
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Das tut mir sehr Leid, dass ich es zu wenig gut erklärt habe. Ist manchmal schwierig, wenn man zu fest in der Materie ist, etwas zu erklären, weil man es selber als gegeben erachtet, aber für jemanden Außenstehenden eigentlich nicht klar ist.

ZB: Die Arbeitszeiten für den Mitarbeiter wären geplant gewesen:
17:30 - 02:00.
Der Mitarbeiter fängt jetzt aber erst 18:30 an und arbeitet bis 02:00. Von 17:30 bis 18:30 bezieht er eine Stunde von seinem Überzeitkonto (Bezug UEZ).
Somit wäre seine Dienstschicht von 18:30 bis 02:00.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Das WAR doch schon bei der ersten Formel berücksichtigt, ich hatte es nur nachher geändert, weil es dir nicht gefiel.
Siehe dir mal die Formel in den gelben Zellen an:
https://www.herber.de/bbs/user/175600.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Es zeigt die Zeiten perfekt an. Auch Mitternacht und die Zeiten danach. Vielen Dank. Mega.

Hast du eine Idee, wie man mit diesen Zeiten jetzt Dienstschichtlänge berechnen könnte?
Also eben ohne den Block "Bezug XY" und den Block "WR_ERFASST" (Wie in der Zelle BD8:BD11).

In dem Beispiel in der Spalt AX ist die Früheste Zeit 16:30. Die späteste ist Zeit ist Mitternacht. Somit wären das 7h30min. Nachher kommt der Block Bezug der nicht mehr dazu gezählt werden darf.

Das gleiche Problem mit dem Berechnen habe ich dann beim Berechnen der Ruheschichtlänge.
Bei der Ruheschichtlänge sollte stehen, wie lange es von Dienstschluss des Vortages bis zum Dienstbeginn des aktuellen Tages geht. Aber ohne Bezüge von Zeiten. Das heisst wenn Mitarbeiter am Anfang oder am Ende zb. Bezug UEZ macht, ist der Start/Ende dann jeweils Nach/Vor dem Zeitpunkt.
Aber nur, wenn am Vortag gearbeitet wurde. Wenn am Vortag F (Ferien), -, +, UEZ, etc steht, hat der Mitarbeiter nicht gearbeitet und es muss nichts berechnet werden.

Nochmals vielen Dank für dein Wissen, deine Zeit und deine Arbeit.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
"Hast du eine Idee, wie man mit diesen Zeiten jetzt Dienstschichtlänge berechnen könnte? " ?
Siehe Zeile 41.
https://www.herber.de/bbs/user/175603.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
OMG. Zauberei. Mega.
Perfekt.

Nur noch eine letzte Bitte, dann lass ich dich in Ruhe:

Ich bräuchte den jeweils den frühesten und in einer anderen Zelle die späteste Zeit. Auch wieder ohne "Bezug XY" und "WR_ERFASST".
Wie kann ich bei Min und Max das auch machen, wie du es mit der Formel der Arbeitsschichtlänge gemacht hast?
Wenn ich mit Min/Max eingebe kommt natürlich alles. Ausserdem kam bei mir bei dem Tag der über Mitternacht hinausging (Reihe AO), die grösste Zahl des eigentlichen Tages, also 22:00 statt 2:00 bei dem Tag 17:30 - 02:00.

Nochmals vielen Dank.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Dafür musste ich bei allen "Bis"-Zeiten die Formeln ändern, so dass eine 1 dazu addiert wird, wenn sie KLEINER als die Von-Zeit ist (wg Datumssprung).

Min/Max-Zeiten sind in Zeilen 51/52.

https://www.herber.de/bbs/user/175606.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Bei den Tagen welche nicht über Mitternacht gehen, funktioniert es perfekt.
Leider bei den Tagen, welche über Mitternacht gehen, geht es leider noch nicht.
Als Beispiel die Reihe BO:
Dort ist die Zeit 21:30-06:00.
Für die früheste Zeit Min steht dort 01:30 statt der Start 21:30. Beim Max steht 25:30 statt die letzte Zeit 06:00.

Oder in der Reihe BT mit der Zeit 16:30 – 01:15. Dort wäre das Min 16:30 und Max 01:15.
Gibt es eine Lösung für das Problem mit dem über Mitternacht? Oder habe ich einen Fehler gemacht beim Eintragen?

https://www.herber.de/bbs/user/175607.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Geht die Zeit immer chronologisch in den Zeilen? Soll heissen, wenn Endzeit Block1 über Mitternacht geht, dann die anderen Blöcke darunter auch?
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Ich habe auch mal deine bedingten Formatierungen etwas aufgeräumt - die Farben kannst du ja anpassen.

https://www.herber.de/bbs/user/175610.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Super. In deiner Excel funktioniert es perfekt.
Ich wollte das Sheet in meine Excel Kopieren, wo es noch andere Berechnungen auf anderen Sheets hat. Der Aufbau der anderen Excel ist gleich.
Habe das Sheet als ganzes kopiert.

Irgendwie schaff ich das nicht. Es rechnet nicht mehr richtig.

Ich wollte die Formel auf die neue Mappe anpassen, aber irgendwo mach ich einen Fehler.
Habe von:
=LET(zz;'C:\Users\u209662\Downloads\[175610_1.xlsm]SPX Kopie'!AV4;ze;WENN(ISTTEXT(zz);ZEITWERT(WECHSELN(zz;"+1";""));zz);i;REST(ZEILE();4)+3;lbis;BEREICH.VERSCHIEBEN(BJ9;-i;0);we;WENN(lbis>1;ze+1;ze+1*(BJ6>1));WENN(ze<>"";we;""))

folgendes gemacht:
=LET(zz;'SPX Kopie'!AV4;ze;WENN(ISTTEXT(zz);ZEITWERT(WECHSELN(zz;"+1";""));zz);i;REST(ZEILE();4)+3;lbis;BEREICH.VERSCHIEBEN(BJ9;-i;0);we;WENN(lbis>1;ze+1;ze+1*(BJ6>1));WENN(ze<>"";we;""))

Also ohne "C:\Users\u209662\Downloads\[175610_1.xlsm]"

Jetzt zeigt es die Zeit nicht mehr richtig an. Kannst du mir sagen, was mein Fehler ist?
Nochmals danke für deine Hilfe.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Keine Ahnung, denn ich sehe DEINE Datei nicht.
Poste sie mal.
Füge mal vor Zeile 7 eine neue leere ZEILE ein. Ich musste es auch machen.
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Ich hab den Fehler gefunden. Im Sheet SPX Kopie, war eine Reihe weniger als in meiner Ursprünglichen Excel. Jetzt funktionierts glaub ich wieder.

Daaaanke Vielmals für dein Geduld und deine Arbeit. Mega.

Alles gute dir.
Gerne !
Onur
AW: Gerne !
Beni
Hallo Onur

Sorry dass ich nochmals störe. Ich dachte ich schaff das selber, aber ich stehe irgendwo an.

Wollte von der MAX und Min Zeit darstellen lassen, welche Schichtart das es sich handelt. Teilweise geht es, aber irgendwo ist ein (oder mehrere) Fehler.
So wie ich gesehen habe, funktioniert meine Formel bei Früh, Mittel und Spät. Bei den anderen aber nicht welche Nach Mitternacht sind.

In Reihe 52 ist der MIN Wert und in 53 der MAX Wert

=WENN(UND(AW52>=WERT("04:00");AW52<=WERT("05:59"));"Früh";WENN(UND(AW52>=WERT("06:00");AW53<=WERT("20:00"));"Mittel";WENN(UND(AW53>=WERT("20:01");AW53<=WERT("24:00"));"Spät";WENN(UND(AW53>=WERT("00:01");AW53<=WERT("02:00"));"Nacht Früh";WENN(UND(AW53<=WERT("02:01");AW53>=WERT("03:59"));"Nacht";"0")))))

Das Resultat sollte eigentlich sein:
Früh= Wenn Arbeitsbeginn MIN zwischen 04:00 und 05:59 liegt
Mittel= Wenn die ganze Zeit von MIN bis MAX zwischen 06:00 und 20:00 liegt
Spät= Wenn Arbeitsschluss MAX zwischen 20:01 und 24:00 ist
Nacht_Früh= Wenn Arbeitsschluss zwischen 00:01 und 02:00 ist
Nacht= Wenn die Arbeitszeit die Zeit zwischen 02:01 und 03:59 berührt. Das heisst, wenn der Zeitraum entweder Start, Ende oder irgendwo zwischendrin ist. Zb 21:30-06:00 oder 17:30-02:01 oder 02:30-10:00 wäre Nacht

https://www.herber.de/bbs/user/175630.xlsm
AW: Gerne !
Beni
Zauberei. Wahnsinn.
Funktioniert perfekt.

Ich trau mich gar nicht zu fragen, aber wenn ich nicht frage trübt mich die Ungewissheit, darum frage ich dich jetzt trotzdem;
Wäre es möglich, dass du mir noch die letzte (wirklich die letzte) Berechnung machen könntest?
Wenn es dir zu viel wird kein Problem, dann frag ich weiter. Du hast schon so viel für mich gemacht.

Der letzte Punkt wäre die Ruheschichten zwischen den einzelnen Arbeitstagen. Ich müsste wissen, wie viele hh:mm zwischen dem Ende des Vortages (wieder ohne Bezug und WR_Erfasst) und dem Arbeitsbeginn des aktuellen Tages (ohne Bezüge und WR_erfasst) liegen.
Habs versucht, aber ich scheitere wieder an dem +1. und auch an dem, wenn der Vortag 16:30 endet und aktueller Tag 17:30 startet. Dann zeigts mir natürlich immer nur 1h an.

zb:
03.12.24 endet der Dienst um 02:01+1 (Sprich am 04.12.24 um 02:01)

am 04.12.24 startet der Dienst um 16:30. Somit wären es 14:29min.

Falls du das als letztes machen könntest, wäre ich wunschlos glücklich.
Ich denke ohne Hilfe würde ich das nicht hinkriegen.
Nochmals vielen Dank



AW: Gerne !
Onur
Du hast doch schon (fast) die Lösung, nur einen Denkfehler:
MAX 2:01 ist eigentlich, durch meine Formeln bedingt, 26:01 Uhr
Wenn du jetzt 16:30 auch noch eine 1 (bzw 24:00) dazu addierst (da ja Folgetag), hast du 40:30.
Wenn du von 40:30 die 26:01 abziehst, hast du deine 14:29
AW: Gerne !
Onur
Du darfst nicht vergessen: Was die Zelle durch die Formatierung bedingt anzeigt, ist nicht unbedingt das, was drin steht.
Wenn da 12:00 steht, kann da in Wirklichkeit 0,5 oder 1,5 oder 2,5 usw stehen, aber da als Uhrzeit formatiert, wird 12:00 angezeigt. Was da wirklich steht, siehst du, wenn du die Formatierung kurzfristig auf "Standard" umstellst.
AW: Gerne !
Onur
Also MIN+1 - MAX(VORTAG)=Ruhezeit für akt. Tag.
AW: Gerne !
Beni
Hammer. Jetzt ist es komplett. Ein Danke ist nicht genug. Aber trotzdem muss ich es sagen: VIELEN Dank.
Ich wünsche dir alles gute.
Gerne !
Onur
AW: Gerne !
Beni
Hallo Onur

Konnte schon sehr viel aus der Excel herausholen.
Ich habe es mit verschiedenen Daten durchgetestet.
Jetzt habe ich etwas festgestellt. Leider zeigt mir die Min und Max Werte nicht immer richtig resp. gar nicht an. Da ich natürlich keine Ahnung von deiner Formel habe, muss ich mich trotzdem nochmals an dich wenden.

Beispiel Reihe BC und BY wo die gleichen Werte drin sind.
Oder BL und BN. Dort steht jetzt jeweils 02:00 bei Min und Max.
Ich sehe keinen Unterschied bei den Formeln.
Es tut mir wirklich leid, dass ich dich nochmals belästigen muss, aber vielleicht kannst du mir einen Tipp geben, wie ich es selber korrigieren kann (falls ich genug Excel-Wissen haben könnte).

https://www.herber.de/bbs/user/175649.xlsm
AW: Gerne !
Onur
Zeile 6 muss immer leer bleiben.
https://www.herber.de/bbs/user/175669.xlsm
AW: Gerne !
Beni
Ahso. Danke vielmals. Funktioniert einwandfrei.mega
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Ja genau. Ist immer Chronologisch von oben nach unten. Die Oberste Zeit ist die früheste (Startzeit) und die Unterste ist die späteste (Schlusszeit).

17:30 Startzeit des Spätdienstes
21:30
04:00
Arbeitszeit mit Leistung
21:30
22:00
00:30
Pause TPO
22:00
02:00+1 Endzeit des Spätdienstes
04:00
Arbeitszeit mit Leistung

Oder

21:30 Startzeit Nachtdienst
01:30+1
04:00
Arbeitszeit mit Leistung
01:30+1
02:00+1
00:30
Pause TPO
02:00+1
06:00+1 Endzeit des Nachtdienst
04:00
Arbeitszeit mit Leistung


AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Onur
Sorry, da war noch ein "*" zu viel:

https://www.herber.de/bbs/user/175604.xlsm
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Hallo Onur

Danke für die Rückfrage.

Wenn der Mitarbeiter einen Bezug einer Zeit macht, dann arbeitet er in dieser Zeit nicht, sondern bezieht Zeit von einem Zeitkonto. Wie in diesem Beispiel ist es sein Überzeitkonto wo er Stunden oder Minuten oder auch ganze Tage beziehen kann.

Gruss
Beni
AW: Arbeitszeiten ohne gewisse Zeiten rechnen
Beni
Noch vergessen zu schreiben; es gibt noch andere Zeitkonten. Der Text fängt aber immer mit "Bezug" an. z.B. Bezug TP, Bezug ZSG etc.