1.6k Aufrufe
Gefragt in Tabellenkalkulation von

Hallo,

ich soll eine Excel Tabelle erstellen um die Zeitzuschläge automatisch auszurechnen. Dazu würde ich dann eine Wennformel verwenden.

=WENN(UND(D14<=ZEIT(20;0;0);D14>=ZEIT(21;0;0));ZEIT(1;0;0);"")*WENN(UND(E14<=ZEIT(20;0;0);E14<=ZEIT(21;0;0));ZEIT(1;0;0);"")

Es soll halt eine Stunde extra berechnet werden wenn die Zeit zwischen 20 und 21 Uhr ist. (Eigentlich nur Samstags, aber das hab ich jetzt mal weggelassen)

So sieht die Tabelle aus:

Samstags Samstag Nachtarbeit Sonntags  Feiertage
Über- 13.00 20:00 21.00  0.00 
stunden - 20:00  - 21:00 - 6:00 - 24:00
Uhrzeit Schlüssel  anzurechn. x 30 % x 20%  x 20 % x  20 % x  25 % x  35 %
von bis von  bis nur Anzahl Stunden 15 21 21 21 19 94
00:00 00:00 00:00
00:00 00:00 00:00 00:00
00:00 00:00 00:00 00:00
20:00:00 22:00:00 02:00 02:00 02:00 #WERT!

Freue mich wenn ihr mir helfen könnt :)

36 Antworten

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo,

in der Formel war noch ein Fehler und auch ein Denkfehler sad.

Versuche es mal so:

=WENN(A10<>"Samstag";"";WENN(UND(STUNDE(E10)>=13;STUNDE(F10)<20);REST(F10-E10;1);WENN(UND(STUNDE(E10)>=13;STUNDE(F10)>20);REST(20/24-E10;1);WENN(UND(STUNDE(E10)<13;STUNDE(F10)<=20);F10-13/24;WENN(UND(STUNDE(E10)<13;STUNDE(F10)>20);7/24;0))))+WENN(A10<>"Samstag";"";WENN(UND(STUNDE(G10)>=13;STUNDE(H10)<20);REST(G10-H10;1);WENN(UND(STUNDE(G10)>=13;STUNDE(H10)>20);REST(20/24-G10;1);0))))

Gruß

M.O.

0 Punkte
Beantwortet von
Super, das klappt :-) Vielen Dank!

Allerdings zeigt er nun immer #### wahrscheinlich wegen einen negativen Wert wenn dort nichts ausgefüllt ist
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo,

anbei die verbesserte Formel:

=WENN(A10<>"Samstag";"";WENN(UND(STUNDE(E10)>=13;STUNDE(F10)<20);REST(F10-E10;1);WENN(UND(STUNDE(E10)>=13;STUNDE(F10)>20);REST(20/24-E10;1);WENN(UND(STUNDE(E10)>0;STUNDE(E10)<13;STUNDE(F10)<=20);F10-13/24;WENN(UND(STUNDE(E10)<13;STUNDE(F10)>20);7/24;0))))+WENN(A10<>"Samstag";"";WENN(UND(STUNDE(G10)>=13;STUNDE(H10)<20);REST(G10-H10;1);WENN(UND(STUNDE(G10)>=13;STUNDE(H10)>20);REST(20/24-G10;1);0))))

Jetzt wird 0:00 angezeigt, wenn nichts ausgefüllt ist.

Gruß

M.O.
0 Punkte
Beantwortet von
Moin :-)

Ein Anliegen hab ich noch. Wenn der MA von 21:30 bis 22:00 in der Nacht arbeitet. Dann wird eine Stunde angezeigt, anstatt einer halben. Woran liegt das?

Liebe Grüße
0 Punkte
Beantwortet von
Bei einer Arbeitszeit am Samstag von 9:00 - 12:00 zeigt der mir immer noch eine negative Zahl an :(

Vielen Dank für deine Hilfe!
0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo,

bei mir wird bei einer Arbeitszeit von 21:30 Uhr bis 22.00 Uhr korrekt eine halbe Stunde angezeigt.

Anbei die verbesserte Formel für den Samstag (für Zelle L7):

=WENN(A7<>"Samstag";"";WENN(UND(STUNDE(E7)>=13;STUNDE(F7)<20);REST(F7-E7;1);WENN(UND(STUNDE(E7)>=13;STUNDE(F7)>20);REST(20/24-E7;1);WENN(UND(STUNDE(E7)>0;STUNDE(E7)<13;STUNDE(F7)>=13;STUNDE(F7)<=20);F7-13/24;WENN(UND(STUNDE(E7)<13;STUNDE(F7)>20);7/24;0))))+WENN(A7<>"Samstag";"";WENN(UND(STUNDE(G7)>=13;STUNDE(H7)<20);REST(G7-H7;1);WENN(UND(STUNDE(G7)>=13;STUNDE(H7)>20);REST(20/24-G7;1);0))))

Gruß

M.O.
0 Punkte
Beantwortet von

Folgende Formel habe ich bei der Nachtarbeit:

=WENN(UND(STUNDE(E14)<=21;STUNDE(F14)>=21);F14-21/24;WENN(UND(STUNDE(E14)>=21;STUNDE(F14)>=21);F14-E14;WENN(UND(STUNDE(E14)>=21;STUNDE(F14)<=6);REST(F14-E14;1);WENN(UND(STUNDE(E14)>=21;STUNDE(F14)>6);REST(6/24-E14;1);WENN(UND(STUNDE(E14)>0;STUNDE(E14)<=6;STUNDE(F14)<=6);F14-E14;WENN(UND(STUNDE(E14)>0;STUNDE(E14)<=6;STUNDE(F14)>6);6/24-E14;0))))))+WENN(UND(STUNDE(G14)<=21;STUNDE(H14)>=21);H14-21/24;WENN(UND(STUNDE(G14)>=21;STUNDE(H14)>=21);H14-G14;WENN(UND(STUNDE(G14)>=21;STUNDE(H14)<=6);REST(H14-G14;1);WENN(UND(STUNDE(G14)>=21;STUNDE(H14)>6);REST(6/24-G14;1);WENN(UND(STUNDE(G14)>0;STUNDE(G14)<=6;STUNDE(H14)<=6);H14-G14;WENN(UND(STUNDE(G14)>0;STUNDE(G14)<=6;STUNDE(H14)>6);6/24-G14;0))))))

Im Feld G 14 steht 21:30 und im Feld H14 22:00, dann wird mir im Feld N14 eine Stunde also 1:00 angezeigt

Liebe Grüße

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo,

du hast im zweiten Teil (also nach dem +WENN..) gleich als erstes die Bedingung

.. WENN(UND(STUNDE(G14)<=21;STUNDE(H14)>=21);H14-21/24 ..

stehen. Damit wird ja, wenn sowohl G14 größer gleich 21 Uhr ist und H14 größer gleich 21 Uhr ist vom Stundenwert G14 (bei dir 22) die 21 abgezogen, was eine Stunde ergibt.

Deine nächste WENN-Formel

WENN(UND(STUNDE(G14)>=21;STUNDE(H14)>=21);H14-G14

die ja die gleichen Voraussetzungen hat - und die die halbe Stunde ergeben würde - wird also gar nicht mehr geprüft.

Gruß

M.O.
0 Punkte
Beantwortet von

Hallo :)

ich hab es jetzt erst wieder geschafft, mich damit zu beschäftigen.

Kannst du mir einmal die richtige Nachtarbeitsformel mitteilen? Irgendwie steh ich gerade total auf den Schlauch indecision

Vielen vielen Dank für deine Hilfe

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo,

ohne jetzt wieder all zu tief einzusteigen, versuche es mal so:

=WENN(UND(STUNDE(E14)<=21;STUNDE(F14)>=21);F14-21/24;WENN(UND(STUNDE(E14)>=21;STUNDE(F14)>=21);F14-E14;WENN(UND(STUNDE(E14)>=21;STUNDE(F14)<=6);REST(F14-E14;1);WENN(UND(STUNDE(E14)>=21;STUNDE(F14)>6);REST(6/24-E14;1);WENN(UND(STUNDE(E14)>0;STUNDE(E14)<=6;STUNDE(F14)<=6);F14-E14;WENN(UND(STUNDE(E14)>0;STUNDE(E14)<=6;STUNDE(F14)>6);6/24-E14;0))))))+WENN(UND(STUNDE(G14)<21;STUNDE(H14)=>21);H14-21/24;WENN(UND(STUNDE(G14)>=21;STUNDE(H14)>=21);H14-G14;WENN(UND(STUNDE(G14)>=21;STUNDE(H14)<=6);REST(H14-G14;1);WENN(UND(STUNDE(G14)>=21;STUNDE(H14)>6);REST(6/24-G14;1);WENN(UND(STUNDE(G14)>0;STUNDE(G14)<=6;STUNDE(H14)<=6);H14-G14;WENN(UND(STUNDE(G14)>0;STUNDE(G14)<=6;STUNDE(H14)>6);6/24-G14;0))))))

Gruß

M.O.
...