Wie berechne ich Nachtarbeit bei der Zeiterfassung?
Moin mittlerweile bin ich am verzweifeln....ich glaube ich nähere mich meiner Lösung schon; allerdings brauche ich noch Hilfe.
Im Zeitraum von 20:00h bis 24:00h gibt es einen Nachtzuschlag von 25%. Dieser soll nicht berechnet werden, sondern lediglich die Stunden, in dem es diesen Zuschlag gibt, sollen gezählt werden. Ich möchte also in diesem Fall als Ergebnis 4 haben.
Üblicherweise beginnt eine Nachtschicht schon um 19h. Die Stunden mit Zuschlag sollen aber erst ab 20h gezählt werden (und in diesem Falle auch nur bis 24 Uhr)
Meine Idee dazu ist
=WENN((UND(S7=20;S7>20));SUMME(24-S7))
Bei mir ist das Ergebnis einfach FALSCH. Was mache ich falsch? Oder bin ich mit der Idde total auf dem Holzweg?
Gruß PeterGri
3 Antworten
Probier mal folgende Formel. In diesem Beispiel stehen Anfangs- und Endzeit in A2 und B2. Die Zellen sind im Uhrzeit-Format.
=WENN(B2<A2;1-MAX(A2;20/24);MAX(B2-20/24;0)-MAX(A2-20/24;0))
Wenn die Zellen nicht im Uhrzeitformat sind und du Dezimalzahlen verwendest, dann lautet die Formel:
=WENN(B2<A2;24-MAX(A2;20);MAX(B2-20;0)-MAX(A2-20;0))
Du kannst gern weiter fragen, kein Problem.
Die Anzahl der Stunden zwischen 4-6 Uhr berechnest du mit dieser Formel:
=MAX(MIN(B2;6/24);4/24)-MIN(MAX(A2;4/24);6/24;WENN(B2<A2;4/24;6/24))
Probier sie mal aus. Sie ist allerdings nicht ganz idiotensicher. Falls der Arbeitsbeginn vor 06:00 ist und das -ende erst nach der nächsten Mitternacht (also mind. 18-Stunden-Schicht), liefert sie ein falsches Ergebnis. Ich weiß nicht, ob das bei dir praktisch relevant sein kann. Für "normale" Nachtschichtzeiten (z.B. 19-7 Uhr) funktioniert sie.
Die anderen Fragen beantworte ich ein andermal, falls du bis dahin noch keine Lösung gefunden hast. Vielleicht schaffst du mit dieser Vorlage auch selbst eine Lösung. Die MIN-MAX-Kombination ist immer hilfreich, wenn bei der Berechnung ein Wert eine Ober- und Untergrenze nicht über-/unterschreiten darf.
Die Formel funktioniert sehr gut!
Wenn allerdings keine Arbeitszeit in A2 und B2 steht kommt als Ergebnis trotzdem '2' heraus. Daher habe ich versucht deine Formel in eine Wenn Funktion einzubetten
=WENN(A2="";"";(MAX(MIN(B2;6/24);4/24)-MIN(MAX(A2;4/24);6/24;WENN(B2<A2;4/24;6/24))))
Doch leider ist das Ergebnis dann immer "" nichts.
Da eine 18 Stunden Schicht nicht in Frage kommt wird dieser Fall auch nicht eintreten. Aber danke für den Hinweis.
Ok ich tüftel erstmal noch selbst etwas weiter und würde mich bei Bedarf noch einmal melden.
Damit die Hilfreichste noch hilfreicher wird:
Wenn man über Mitternacht hinwegrechnen will, eignet sich folgende -sehr kurze- Formel (hier speziell für die Nacht von Kommen So (A1) auf Gehen Mo(A2)):
=REST("4:00"-A1;1)*150%+REST(A2-"4:00";1)
Sie rechnet die Zeitdifferenz von Kommen bis 4:00 und schlägt 50% auf, ausgedrückt in Zeit. Der zweite Summand rechnet dann 4:00 bis Gehen. (ist nicht ganz einfach nachzuvollziehen, aber funktioniert bei Differenzen von unter einem Tag im reinen Zeitformat. ausserdem erlaubt xl -seit wann eigentlich schon?- die Formulierung einer Zeit als "String" innerhalb von Formeln)
Soll der Zuschlag nicht in Zeit ausgedrückt werden, müssen die Formelteile separat verrechnet werden, die Erfassung bis zur /ab der Grenze bleibt dieselbe)
hab grad gelesen dass auch erst ab 20:00 gezählt werden soll: dann muss eben ein weiterer Formelteil rein mit Kommen bis 20:00.
Späteres Kommen (oder Gehen vor 4:00) braucht dann möglicherweise andere Formeln bzw doch eine zusätzliche Wenn-Einbettung (oder Max(), Min() ).
Die Formel kann so nicht funktionieren: S7 kann nicht gleichzeitig gleich 20 und größer 20 sein, daher greift die Wenn-Bedingung nie. Mit dem UND hast Du hinterlegt, dass beide Bedingungen erfüllt sein müssen. Größer/Gleich wird einfach durch S7>=20 ausgedrückt. Das kann dann zutreffend sein.
Das Ergebnis FALSCH wird angezeigt, weil Du keinen expliziten Wert angegeben hast, wenn die Bedingung nicht zutreffend ist (also den "sonst" Wert - Wenn ist ja so aufgebaut: Bedingung; Dann; Sonst)
Um eine Differenz von Uhrzeiten zu rechnen und dann in eine Zahl umzuwandeln, muss man etwas um die Ecke denken, da Excel zum Rechnen Uhrzeiten in Dezimalzahlen nimmt und nur bei der Anzeige 20:00 verwendet:
Wenn 24 Uhr das Maximum des Arbeitsendes ist, wäre es so möglich:
=WENN(S7>=20*0,04;(S7-"20:00")*24;0)
0,04 entspricht einer Stunde
0,04 entspricht einer Stunde
Aber nur wenn sich die Erde etwas schneller dreht :)
(1/24 = 0,0416666...)
Hallo Ninombre,
das ist erstmal ein guter Hinweis im ersten Absatz! Und was du im 2ten Absatz sagst leuchtet auch ein.
Habe deine Formel ausprobier und leider das Ergebnis 0:00 bekommen. Und ja in meinem Beispiel sind die Endzeiten auch nach 24:00h. Sehr üblich ist die Arbeitszeit zwischen 19:00h und 07:00h.
Zum Verständnis nochmal kurze Beispiele:
19:00 bis 07:00 (Ergebnis: 4 Stunden bekommen 25% Zuschlag)
21:00 bis 07:00 (Ergebnis: 3 Stunden bekommen 25% Zuschlag)
17:00 bis 02:00 (Ergebnis: 4 Stunden bekommen 25% Zuschlag)
23:00 bis 05:00 (Ergebnis: 1 Stunde bekommt 25% Zuschlag)
Ich habe ehrlich gesagt nicht näher beachtet, wie sich S7 in Deinem Fall ergibt und was da drin stehen kann. Ist es 23.59, 24:00 oder auch über 0:00 hinaus? Dann müsste man die Fälle noch abbilden, die habe ich nicht betrachtet.
Hallo Peter,
probier mal folgende Formel aus:
=WENN(STUNDE(C14)>=20;WENN(STUNDE(C15)=0;24;STUNDE(C15))-STUNDE(C14);4)
C14=Arbeitsbeginn
C15=Arbeitszeitende
Die erste WENN-Schleife kümmert sich daraum, dass bei einem Arbeitszeitbeginn vor 20:00 maximal nur 4 rauskommt.
Die zweite WENN-Schleife macht aus 00:00 (das wird in der Regel aus einer Eingabe von 24:00 gemacht) entsprechend umgerechnet wird.
Ich hoffe die Antwort hilft dir weiter.
Bei deiner Formel bekomme ich das Ergebnis 96:00
Habe C14=19:00 und C15=07:00
Probier mal folgende Formel:
=WENN(STUNDE(C14)<=20;WENN(STUNDE(C15)<STUNDE(C14);4;WENN(STUNDE(C14)<20;STUNDE(C15)-20;STUNDE(C15)-STUNDE(C14)));WENN(STUNDE(C15)<STUNDE(C14);24-STUNDE(C14);STUNDE(C15)-STUNDE(C14)))
Ich bin bei der Formelerstellung davon ausgegangen, dass du nur volle Stunden eingibst. Ist das korrekt?
Es kommen auch Halbe- oder Viertelstunden vor. Weiter oben konnte mir übrigens schon gut weitergeholfen werden! Trotzdem besten danke auch an dich!
Bin jetzt nooch auf der Suche nach einer Lösung für die Sonntagsarbeit, die von 0-24h geht. Dabei muss folgendes berücksichtigt werden:
Nacht von Sa auf So
19h - 7h (7x 50%)
Sonntag tagsüber
7h - 19h (12x 50%)
Nach von So auf Mo
19h - 7h (9x 50%; weil die ersten vier Stunden des Folgetages einberechnet werden)
Wow! Die Formel klappt einwandfrei. Super danke dir.
Ich habe noch weitere Berechnungen die ich mit Excel durchführen möchte. Hoffe es ist nicht unhöflich hier noch weiter zu fragen nach ähnlichen Formeln. Und zwar geht es um das Zählen der Stunden bei denen man (erneut) 25%, 40% und 50% Zuschlag bekommt:
(erneut) 25% - in der Zeit zwischen 4h und 6h. Dabei soll es wieder egal sein wann Arbeitsbeginn war
40% - in der Zeit zwischen 0h und 4h, aber nur wenn der Arbeitsbeginn vor 0h war. Außerdem kann hier auch wieder möglich sein, dass Arbeitsende zb 3h ist und als Ergebnis nur '3' rauskommt.
50% - den ganzen (Sonn-)tag. Als Bsp. von 0h-7h; von 7h-19h; 19h-0h sowie die ersten 4 Stunden des Folgetages wenn Arbeitsbeginn vor 0h war (Nacht von So auf Mo).
Hoffe das kann auch in Formeln ausgedrückt werden!
P.S.: Ich verwende das Uhrzeitformat
Gruß PeterGri