AW: Aus Arbeitszeiten Sonderzeiten aussplitten
22.02.2015 20:33:08
Ulrich
Hi Brigitte,
die reine Excel-Lösung ohne VBA könnte so aussehen:
für die Arbeitszeitberechnung:
"=WENN(A#="";"";WENN(B#<A#;(Tabelle2!H1-A#+1/1440)+B#;B#-A#))"
für die Mehrarbeitszeitberechnung:
"=WENN(A#="";"";WENN(A#<Tabelle2!B1;WENN(A#<=Tabelle2!A1;1/24;A#-Tabelle2!A1);0)+WENN(B#>
Tabelle2!C1;WENN(B#>=Tabelle2!D1;1/24;Tabelle1!E1-Tabelle1!B#);WENN(B#<A#;WENN(A#<=Tabelle2!C1;
1/24;WENN(A#<Tabelle2!D1;Tabelle2!D1-A#;0)))))"
für die Überstundenzeitberechnung:
"=WENN(A#="";"";WENN(A#<Tabelle2!F1;Tabelle2!F1-A#;0)+WENN(B#>Tabelle2!G1;B#-Tabelle2!G1;
WENN(B#<Tabelle2!F1;(7/24)+B#;0)))"
wobei sich in Tabelle2 folgende Informationen befinden:
[Zelle A1]: 6:00 | [Zelle B1]: 7:00 | [Zelle C1]: 16:00 | [Zelle D1]: 17:00 (Mehrarbeitszeiten)
[Zelle E1]: 0:00 | [Zelle F1]: 6:00 | [Zelle G1]: 17:00 | [Zelle H1]: 23:59 (Überstundenzeiten)
Erklärung:
das Zeichen "#" steht in den Formeln für die Zeilenzahl
jede Formel prüft zunächst, ob eine Anfangszeit in Spalte A eingetragen ist, Wenn nicht, wird nix angezeigt
Die Zellen müssen im Uhrzeitformat (sinnvollerweise "00:00") formatiert sein.
Die Formel für die reine Arbeitszeitberechnung prüft zunächst, ob bis nach Mitternacht gearbeitet wurde. Ist dies der Fall, wird die Zeit vom Arbeitsbeginn errechnet und mit der Endzeit addiert. Wurde nicht bis nach Mitternacht gearbeitet, wird nur die Differenz aus Endzeit und Anfangszeit ermittelt.
Die Formel für die Mehrarbeitszeit besteht aus zwei Teilen. Die Formel im ersten Teil prüft zuerst, ob die Startzeit vor der regulären Arbeitszeit liegt. Ist dies der Fall, wird geprüft, ob die Startzeit vor dem Beginn der Mehrarbeitszeit liegt. Wenn Ja wird eine Stunde ausgegeben, wenn Nein, wird die Differenz zwischen regulärer und Startzeit berechnet. Ist von alldem nichts der Fall, wird 0 ausgegeben. Im zweiten Teil wird geprüft, ob die Endzeit nach der regulären Arbeitszeit liegt. Ist dies der Fall, wird geprüft, ob die Endzeit größer oder gleich dem Ende der Mehrarbeitszeit ist. Wenn Ja, wird eine Stunde ausgegeben, wenn Nein, wird die Differenz zwischen Endzeit und Ende der regulären Arbeitszeit ausgegeben. Liegt jedoch die Endzeit vor dem Ende der regulären Arbeitszeit, wird geprüft, ob die Endzeit vor der Startzeit liegt (Arbeiten bis nach Mitternacht). Wenn JA, wird geprüft, ob die Startzeit vor dem Ende der regulären Arbeitszeit liegt oder dieser gleicht. Wenn Ja, wird eine Stunde ausgegeben, wenn Nein, wird geprüft, ob die Startzeit vor dem Ende der Mehrarbeitszeit liegt. In diesem Fall wird die Differenz aus dem Ende der Mehrarbeitszeit und der Startzeit errechnet und ausgegeben. Ist dies nicht der Fall, wird 0 ausgegeben. Die beiden Teilergebnisse werden miteinander addiert und das Ergebnis der Addition wird angezeigt.
Die Formel für die Überstunden besteht ebenfalls aus zwei Teilen und ist der eben geschilderten Funktion ähnlich (nur etwas einfacher). Liegt die Startzeit vor dem Ende der "Überstundenzeit" (also vor 6:00), wird die Differenz aus dem Ende der Überstundenzeit und der Startzeit berechnet und ausgegeben. Wenn nicht, wird 0 ausgegeben. Liegt die Endzeit nach dem Beginn der Überstundenzeit (>17:00), wird die Differenz aus Endzeit und Beginn der Überstundenzeit berechnet und ausgegeben. Ist dies nicht der Fall, wird überprüft, ob die Endzeit kleiner ist, als die Überstundenendzeit (bei Arbeiten bis nach Mitternacht). Ist dies der Fall, wird die Summe aus sieben Stunden (17:00 ... 24:00) und der Endzeit gebildet und ausgegeben. Ist dies nicht der Fall, wird 0 ausgegeben. Wieder werden beide Teilergebnisse zum Formelergebnis addiert und angezeigt.
Ich bin mir sicher, dass das Ganze mit VBA eleganter zu lösen ist, aber das ist mir auf die Schnelle eingefallen und hilft erst mal weiter. Auch könnten diese Berechnungen für eine VBA-Lösung als Grundlage dienen, da auch hier ähnliche Berechnungen angestellt werden müssten.
Die Formeln kann man ganz einfach einmal einfügen und die "#" durch die entsprechende Zeilenzahl ersetzen. Wenn man jetzt die Formeln nach unten kopiert, wird Excel zwar alle Zellenangaben anpassen - was dazu führt, dass die Formlen nicht mehr funktionieren, da diese in leere Zellen zeigen, aber mittels (z.B.) "Suchen & Ersetzen" kann man hier in den (markierten) Spalten nach "Tabelle2!E*-" (als Beispiel für die erste Formel) suchen und durch "Tabelle2!E2-" ersetzen lassen.
HTH
Ciao Uli