Kann mir jemand bei diesem Excel Problem helfen?
Hallo an alle,
Ich habe folgende Aufgabe:
Es geht um Eine Zeiterfassung.
In Spalte B2 wird eine Anfangszeit eingetragen im Format hh:mm. Im Feld C2 wird die Endzeit eingetragen auch im Format hh:mm. Diese zeigen die Arbeitszeit. Die Endzeit kann jedoch auch nach 00:00 Uhr liegen was trotzdem nur die Arbeitszeit verlängert. Im Feld D2 brauche ich die angefangenen halben Stunden zwischen 06:00 Uhr und 22:00 Uhr. Im Feld E2 sollen die angefangenen halben Stunden nach 22:00 Uhr erscheinen. Die Endzeit kann auch nach 00:00 Uhr liegen. Somit wären es bei einer Arbeitszeit von 15:00 Uhr bis 17:15 Uhr zum Beispiel 5 angefangene halbe Stunden im Feld D2 und 0 angefangene halbe Stunden im Feld E2. Bei einer Arbeitszeit von 21:00 Uhr bis 1:45 Uhr wären es somit 2 angefangene halbe Stunden im Feld D2 und 8 angefangene halbe Stunden im Feld E2. Ich brauche die Formeln für die Felder D2 und E2.
Ich habe folgende Formeln bis jetzt:
D2:
=WENN(B2>=C2;0;WENN(C2<=ZEIT(6;0;0);0;WENN(B2>=ZEIT(22;0;0);0;WENN(B2<ZEIT(6;0;0);WENN(C2<=ZEIT(22;0;0);AUFRUNDEN((C2-ZEIT(6;0;0))*48;0);AUFRUNDEN((ZEIT(22;0;0)-ZEIT(6;0;0))*48;0));WENN(C2<=ZEIT(22;0;0);AUFRUNDEN((C2-ZEIT(6;0;0))*48;0);AUFRUNDEN((ZEIT(22;0;0)-MAX(B2;ZEIT(6;0;0)))*48;0))))))
E2:
=WENN(B2>=C2;0;WENN(C2<=ZEIT(6;0;0);AUFRUNDEN((C2-B2)*48;0);WENN(B2>=ZEIT(22;0;0);WENN(C2<=ZEIT(30;0;0);AUFRUNDEN((C2-B2)*48;0);AUFRUNDEN((C2-ZEIT(22;0;0))*48;0));WENN(C2<=ZEIT(22;0;0);AUFRUNDEN((C2-ZEIT(6;0;0))*48;0);WENN(B2<ZEIT(6;0;0);AUFRUNDEN((ZEIT(30;0;0)-B2)*48;0);AUFRUNDEN((C2-ZEIT(22;0;0)+ZEIT(24;0;0))*48;0))))))
Wie ihr am Bild sehen könnt funktioniert diese gut wenn die Anfangszeit vor 22 Uhr liegt und die Endzeit nach 22 Uhr aber VOR 0 Uhr. Sobald beide Zeiten innerhalb von 06:00 Uhr und 22:00 Uhr liegen kommt ein falsches Ergebnis. Und sobald die Endzeit nach 00:00 Uhr liegt wird einfach in beiden Spalten 0 ausgegeben.
Pls help <3
3 Antworten
OK, ich hab das mit einigen Hilfsspalten gelöst um die Formeln überschaubar zu halten:
Die Ergebnisse scheinen mir korrekt, aber ich hab das ganze auch noch nicht sehr ausführlich getestet.
Edit: inzwischen hab ich wohl alle Eventualitäten getestet und die Formeln funktionieren zuverlässig solang du nicht über 24h an Stück arbeitest.
Die Tabelle hab ich jetzt einfach Mal in mein Google Drive gepackt, ich denke es ist leichter zu verstehen wenn man sich das Konstrukt live anschaut als wenn ich jetzt jede Formel manuell erläutere
Ergänzung:Damit die Antwort auch noch Sinn ergibt wenn diese Datei irgendwann aus meinem Google Drive verschwindet werde ich doch noch versuchen die Formeln und Hilfsspalten zu erklären.
Angefangen also mit den Hilfsspalten:
H1 und I1: markieren den Beginn (abends) und Ende (morgens) des Nachtzuschlag
Endzeitkorrektur: prüft ob die Endzeit<Anfangszeit, wenn dem so ist wird der Endzeit 1 Tag zugerechnet
=WENN(C3<B3;C3+1;C3)
Beginn Nacht: ermittelt ab welcher Uhrzeit der Nachtzuschlag berechnet werden soll (wurde vor 22 Uhr begonnen ist ja Nachtzulage erst ab 22uhr), wenn nicht in der Nacht gearbeitet wurde ist das Ergebnis= der Endzeit der Nachtzulage.
=WENN(ODER($B3>$C3;$C3>$H$1);$H$1;WENN($B3<$I$1;$B3;$I$1))
Ende Nacht: ermittelt praktisch das gleiche für den Feierabend, liegt dieser in den Nachtstunden wir die Uhrzeit eingesetzt, sonst 06 Uhr.
=WENN(ODER($H$1<$C3;$C3<$I$1);C3;$I$1)
Zeit Nacht: ist jetzt nur noch eine einfache substraktion um zu ermitteln wie viele Stunden nachts gearbeitet wurde, das Wenn ist auch wieder zur Korrektur der Endzeit
=WENN(H3>I3;(I3+1)-H3;I3-H3)
Von hier aus also wieder zurück zur eigentlichen Tabelle:
Die angefangenen Halbstunden am Tag sind eine Substraktion der korrigierten Endzeit - Anfangszeit - die Stunden die in der Nacht gearbeitet wurden. Multiplikation mit 48 um das Uhrzeitformat von Excel in dezimale Halbstunden zu wandeln, Aufrunden auf 0 Nachkommastellen weil du ja angefangene Halbstunden möchtest.
Die angefangenen Halbstunden in der Nacht habe ich ja erst über 3 Hilfsspalten ermittelt. Also die Nachtarbeitszeit mit 48 multipliziert und aufgerundet ist bereits das Ergebnis
Du könntest z.B. folgendes machen Deine 2 Grenzzeiten 22:00 und 00:=00 (Tageswechsel) in zwei Zellen hinterlegen und darauf Bezug nehmen...
Uff, die Formel wirkt ziemlich intransparent.
Wäre nicht ein anderer Ansatz besser? Zum Beispiel könntest du nicht nur die Zeiten erfassen, sondern auch das Datum. Dann kannst du leichter die Differenz berechnen.
Du musst nur schauen, dass du in den Optionen bei
Datei → Optionen → Erweitert → Beim Berechnen der Arbeitsmappe → 1904-Datumswerte verwenden
das Richtige eingestellt hast.
Gäbe es nicht die Möglichkeit einfach unabhängig vom Datum die Uhrzeit nach 00:00 auch einzubeziehen? Meine große Frage ist auch warum es wenn die beiden Zeiten innerhalb von 6 und 22 Uhr liegen kein richtiges Ergebnis findet.