Zeitüberschneidung: Nachtschichtzuschlag berechnen
Überblick
Um die zeitliche Überschneidung von zwei Zeiträumen zu berechnen, müssen wir sechs verschiedene Konstellationen berücksichtigen. So können sich die Zeiträume gänzlich, nur zum Teil oder gar nicht überschneiden. Mehr dazu später.
Zeitüberschneidung berechnen
In unserem Beispiel haben wir zwei Zeiträume, die jeweils eine Anfangszeit und eine Endzeit haben. Diese werden hier mit A_1, E_1 und A_2, E_2 abgekürzt. Wir möchten berechnen, wie groß die Überschneidung ist. Wenn wir einen Zeitraum von 10 bis 12 Uhr haben und einen von 11 bis 13 Uhr, dann liegt die Überschneidung dieser Zeiträume bei einer Stunde.
Die Formel ist lang und ein bisschen unübersichtlich, deswegen habe ich die hier erst einmal die 5 bzw. 6 verschiedenen Fälle visuell dargestellt. Der gelbe Balken stellt unseren Zeitraum1 mit der Anfangszeit A_1 und der Endzeit E_1 dar. Der blaue Balken ist Zeitraum2 mit der Anfangszeit A_2 und Endzeit E_2.
In Fall 1 liegt Zeitraum2 in Zeitraum1.
In Fall 2 liegt Zeitraum1 in Zeitraum2.
In Fall 3 startet Zeitraum1 früher, überschneidet sich jedoch mit Zeitraum2.
In Fall 4 startet Zeitraum2 früher, überschneidet sich jedoch mit Zeitraum1.
In Fall 5 liegt Zeitraum1 vor Zeitraum2 oder Zeitraum1 vor Zeitraum2. Es gibt hier keine Überschneidung.
Um auf diese verschiedenen Fälle zu prüfen, benötigen wir die WENNS-Funktion. Mit ihr gehen wir die 5 Fälle durch und vergleichen die Position von A_1 und A_2 sowie von E_1 und E_2 zueinander, um somit die Dauer der Überschneidung richtig zu berechnen.
Da wir in den Zellen eine Uhrzeit stehen haben, bekommen wir Probleme, wenn die Endzeit am nächsten Tag liegt. Z.B. beginnt der Zeitraum um 17:00 und endet am nächsten Tag um 3:00. Hier müssen wir auf die Endzeit +1 addieren, damit klar ist, dass es sich um den nächsten Tag handelt. In der Zelle bekommen wir nach wie vor die gleiche Uhrzeit angezeigt. Wenn man die Uhrzeit jedoch in eine Zahl umwandelt, sieht man den Unterschied.
E_1: WENN(B3<A3;B3+1;B3)
E_2: WENN($F$3<$E$3;$F$3+1;$F$3)
=WENNS(
UND(A3<=$E$3;WENN(B3<A3;B3+1;B3)>=WENN($F$3<$E$3;$F$3+1;$F$3));WENN($F$3<$E$3;$F$3+1;$F$3)-$E$3+1;
UND(A3>=$E$3;WENN(B3<A3;B3+1;B3)<=WENN($F$3<$E$3;$F$3+1;$F$3));WENN(B3<A3;B3+1;B3)-A3+1;
UND(A3<=$E$3;$E$3<=WENN(B3<A3;B3+1;B3);WENN($F$3<$E$3;$F$3+1;$F$3)>WENN(B3<A3;B3+1;B3));WENN(B3<A3;B3+1;B3)-$E$3+1;
UND($E$3<=A3;A3<=WENN($F$3<$E$3;$F$3+1;$F$3);WENN(B3<A3;B3+1;B3)>WENN($F$3<$E$3;$F$3+1;$F$3));WENN($F$3<$E$3;$F$3+1;$F$3)-A3+1;ODER($E$3>WENN(B3<A3;B3+1;B3);A3>WENN($F$3<$E$3;$F$3+1;$F$3));
1)-1
Da diese Schreibweise jedoch sehr unübersichtlich ist, nutzen wir die LET-Funktion, mit der wir sich wiederholende Berechnungen wie die Abänderung der Endzeiten E_1 und E_2 einmalig als Variabel definieren und dann in der Formel wiederverwenden können. Zudem habe ich hier auch die Anfangszeiten als Variablen definiert, damit die Schreibweise einheitlich ist.
=LET(
A_1;A3;
A_2;$E$3;
E_1;WENN(B3<A_1;B3+1;B3);
E_2;WENN($F$3<$E$3;$F$3+1;$F$3);
WENNS(
UND(A_1<=A_2;E_1>=E_2);E_2-A_2;
UND(A_1>=A_2;E_1<=E_2);E_1-A_1;
UND(A_1<=A_2;A_2<=E_1;E_2>E_1);E_1-A_2;
UND(A_2<=A_1;A_1<=E_2;E_1>E_2);E_2-A_1;
ODER(A_2>E_1;A_1>E_2);0))
Hier definieren wir also die Variablen A_1, A_2, E_1 und E_2. Anschließend verbauen wir diese in der WENNS-Funktion. Diese prüft jetzt auf die 5 Fälle und wenn einer dieser Fälle wahr ist, wird die Dauer der Überschneidung berechnet.
Z.B. in Fall 1, wenn A_1 kleiner gleich A_2 und E_1 größer gleich E_2 ist, wird E_2 - A_2 gerechnet.
Nachtzuschlag berechnen
Um einen Nachtzuschlag zu berechnen, nutzen wir die Formel, um die Dauer der Überschneidung auszurechnen.
Zuerst rechnen wir die Dauer in C3 mit =WENN(B3<A3;B3+1-A3;B3-A3) aus. Hier wird also wieder berücksichtigt, ob der Zeitraum am nächsten Tag endet. Anschließend multiplizieren wir die Dauer mit dem Stundensatz. Da die Zahl, welche die Dauer angibt, der Bruchteil eines gesamten Tages ist, müssen wir die Dauer hier mit 24 multiplizieren, um die Stundenanzahl zu bekommen. Also C3*24 und dann noch mit dem Stundensatz aus D3 multiplizieren, um auf den Verdienst ohne Zuschlag zu kommen.
Um die Dauer des Nachtschlags zu berechnen, nehmen wir die Formel, welche oben bereits beschrieben wurde. Diese Dauer multiplizieren wir wieder mit 24 und dem Nachtzuschlag Stundensatz. C7*24*D7 und addieren dann den Verdienst ohne Zuschlag hinzu.
EXCEL, Power BI, ChatGPT
ONLINE TRAINING
