Zeitzuschläge berechnen?
Hallo Community, ich stehe vor einem Problem bei dem ich einfach seit Wochen nicht weiterkomme. Ich brauche Excel-Formeln mit denen ich folgende Zeitzuschläge berechnen kann:
Nachtarbeit von 21:00 - 06:00 Uhr
Samstagsarbeit: 13:00 - 21:00 Uhr
Sonntagsarbeit: egal wann Beginn und Ende
Feiertag: egal wann Beginn und Ende
Ich brauche nur die Stunden!
Beispiel 1: von 14:00 bis 23:00 Uhr an einem normalen Wochentag gearbeitet, dann sind das 2 Stunden Nachtarbeit.
Beispiel 2: von 18:00 samstags bis 6:00 Uhr sonntags, dann 3 Stunden Samstagsarbeit ; 3 Stunden Nachtarbeit; 6 Stunden Sonntagsarbeit.
Beispiel 3: Sonn- oder Feiertag von 8:00 bis 14:00, dann 6 Stunden Sonn/Feiertagzuschlag.
Wenn jemand eine Tabelle erstellen könnte in der solche Berechnungen möglich sind und die Beginn, Ende, Pause und Gesamtzeit beinhaltet wäre das sehr hilfreich. Das Problem bei der Pause ist, dass diese nicht immer 30 Minuten beträgt. Mitarbeiter könnten auch mehrere Stunden Pause machen (z.B. während der Nachtschicht).
Wäre toll wenn mir jemand dabei helfen könnte, denn so langsam bin ich echt am verzweifeln :(
3 Antworten
Hier mal die Berechnung für die Nachtarbeit:
In B2 steht der Arbeitsbeginn (z.B. 5:00), in C2 das Arbeitsende (z.B. 22:00)
(Wichtig, dass es sich um reine Zeiten handelt, also ohne Datum!)
in E2 kommt dann die Formel für die Nachtarbeit:
=(C2-B2)-(MIN(C2;21/24)-MAX(B2;6/24))
Erklärung:
Der EInfachheit halber wird von der Gesamtarbeitszeit (C2-B2), jene Arbeitszeit abgezugen, die außerhalb der Nachtarbeitszeit liegt.
21/24 -> 21:00
6/24 -> 6:00
ich würde wirklich - wie in meiner Antwort schon dargelegt, über eine Hilfspalte nachdenken, die den Wochentag extrahiert.
Deiner Sonntagsarbeits inkludiert schon die Feiertag nicht - dafür braucht es nochmal eine Abfrage.
Sämtliche Sondernstundenformeln prüfen dann ihrerseits welcher Wochentag vorhanden ist. Je nach Umfang der Tabelle ergeben sich sehr sehr viele Wiederholungen.
Eine ausgeblendete Hilfspalte die ein einziges Mal ja Datum ermittelt welcher Tag vorliegt spart da viel Rechenzeit
Du kannst gerne nachdenken, worüber du willst - ich mache das keine Sekunde lang und verwende die Hilfsspalten ohnehin.
Es geht hier darum, verständlich aufzuzeigen, wie verschiedene Dinge in Excel umgesetzt werden können. Wenn jemand von mir eine komplette Arbeitszeitaufzeichnung / Auswertung haben will, die alle Eventualitäten berücksichtigt, so mache ich das gerne. Er braucht mir nur das vollständige Lastenheft zu seiner Aufgabe schicken und ich unterbreite ihm ein Angebot.
Aber schön, dass wir darüber gesprochen haben!
Die Formel klappt an sich schon, wie kann ich Excel aber sagen, dass wenn Dienstreisen etc gemacht werden , die Spalte in E2 leer bleiben soll?
Abkürzungen:
Dienstreise = D
Urlaub = U
Krankheit = K
Überstundenausgleich = A
Arbeitszeitverkürzung = F
Schule = S
Habe versucht das ganze mit einer Wenn-Funktion zu verknüpfen, klappte aber nicht.
Das sieht nach einer größeren Geschichte aus, für die ich etliche Tage brauchen werde und immer mal wieder rückfragen muss -wenn ich es überhaupt schaffe! (hatte vor zwei Jahren mal was ähnliches nicht hingekriegt, inzwischen aber einiges dazugelernt).
Ich habe ja in der AW von Jackie schon mal ein paar Ansätze angerissen, aber die Überlappungen werden das Ganze sehr verkomplizieren.
Ein neues Problem wird durch die Pausen aufgeworfen: von welcher Zeit rechnet man die ab, wenn die Anwesenheit Freitag abend bis Sa früh oder Sa abd bis So früh oder So abd bis Mo früh?
Kann jemand nach Mitternacht kommen und vor 6:00 gehen?
Ist es möglich, dass Sie ein ganzes Zeitblatt erstellen? Dann würde ich Ihnen sagen, welche Infos rein müssen, wie die Zuschläge berechnet werden und sonstige wichtige Informationen. Die Formatierungen könnten Sie so frei wählen und selbst entscheiden was am einfachsten ist. Wobei es schön wäre, wenn die Zeiten im normalen Uhrzeit-Format ausgeworfen werden.
Liebe Grüße
Bitte zunächst einmal die Frage sortieren. Du fragst nach Nachtarbeit, Samstagsarbeit und Sonn/Feiertagsarbeit - bringst dann aber nur ein Beispiel für Nachtarbeit und keinen Hinweis wie deine Tabelle überhaupt aufgebaut ist. Was ist denn zB wenn jemand von 20 Uhr samstag 02. Oktober - 6 Uhr Sonntag 3. Oktober arbeitet? Sind dann dann 1 Stunde Samstagsarbeit (bis 21 Uhr) 2 Stunden Nachtarbeit (Bis 0 Uhr) 6 Stunden Sonn-/Feiertagsarbeit oder doch Nachtarbeit?? Du bringst den Algorythmus und wir helfen dir den in Excel einzugeben. Zudem: gibt es Pausenzeiten? Gibts es nur die Eingabe von bis oder wird auch der Arbeitstag eingegeben (statt nur nummeriert)? Zunächstmal musst du Excel beibringes welches Feiertage sind, das geht nur händisch, da es sich ja noch nach dem Bundesland richtet. Im Grund kannst du dann (verdeckt) einen Index für den Wochentag in einer Spalte nebendem Datum ermitteln (zB 1 = Samstag, 2 = Sonnt/Feiertag und 0 = keine Besonderheiten). Danach kann man mit Wenn und/oder Wahlfunktionen die Teilstunden extrahieren. Dazu braucht man natürlich eine Prioliste, welche Stunden "stärker" wiegen oder ob es gar Kombinationen gibt (wie Nachtarbeit Sonntags)
Genau diese Fragen sind offen! Dazu noch:
Werden die Zeiten als Datum+Zeit in einer Zelle eingegeben oder werden Datum und Zeit getrennt?
am einfachsten sind
- nur Nachtarbeit, wenn Kommen vor Mitternacht liegt und nur die Uhrzeit in den beiden Zellen steht(Spalte K: = Kommen, Spalte L = Leave, J= Datum Kommen)
=REST(MIN(L23;"6:00"*1)-MAX(K22;"21:00"*1);1)
- Sonntagsarbeit (Kommen UND Gehen am Sonntag):
=(L22-K22)*(Rest(J22;7)=1)
- Für Feiertage brauchst du eine Datumsliste der Feiertage, benannt FeiertageAktJ
=(L22-K22)*Wennfehler((Vergleich(J22;Feiertage;0)>0);0)
Da ich aber nicht weiß, ob meine Voraussetzungen zutreffen, führe ich das nicht weiter aus
Das zu Kombinieren erfordert dann schon einige Klimmzüge, weiß noch nicht, wie komplex das werden kann.
Wenn die Nachtarbeit von Samstag auf Sonntag geht wird ab 0:00 der Sonntagszuschlag berechnet anstatt Nachtarbeit. Also wäre es wie du es gesagt hast richtig; 1 Stunde Samstagsarbeit, 2 Stunden Nachtarbeit und 6 Stunden Sonntagszuschlag.
Bezüglich der Pausen: es wird ab 6 Stunden eine 30 Minuten Pause von der Arbeitszeit abgezogen. Diese Zeit wird als Stunde:Minuten eingegeben (0:30).Eine genaue Info von wann bis wann die Pause genommen wurde, gibt es nicht.
Samstagsarbeit: (in A2 steht das Datum):
=WENN(WOCHENTAG(A2)=7;MIN(C2;21/24)-MAX(B2;13/24);)
Sonntagsarbeit:
=WENN(WOCHENTAG(A2)=1;C2-B2;)
Anmerkung:
Die Ergebnisse sind in Stunden im Zeitformat. Wenn man diese in "echten" Stunden haben will, sind die Werte noch mit 24 zu multiplizieren.