excel formel für verschiedene zuschläge?
Liebe Excel Profis,
seit anfang April werden meine beschäftige Samstag mit 20% zuschlag vergütet.
Problem ist jetzt folgendes. Geht nur für das Wochenende (Sa+So).
Es gibt Samstags die ich von 16 Uhr bis 1:30 Uhr Sonntags arbeiten, und in dieses fall muss ich 3 verschiedene zuschläge berechnen um zwar: Samstag bis 22 Uhr gibt 20% samstag zuschlag, ab 22 Uhr bis 0:00 gibt 25 % Nachtszuschlag, und ab 0:00 Sonntags bis 1:37 uhr die 50% Sonntag zuschlag. Hab eine screenschot meine excel tabelle beigefügt. welche formel gibt für all diesen berechnungen? Muss ich noch weitere zeile einfügen?
Besten Dank
2 Antworten
Ich wüsste jetzt nicht wofür, die Daten reichen ja um alles zu berechnen was du brauchst, wenn es außerhalb von den zwei Zuschlägen keine weiteren gibt?
Also ich habe mich mit deiner Datei einige Zeit beschäftigt, die war ja doch etwas verstrickter als gedacht, vor allem aber sind im meinen Augen auch einige Fehler drin.
zu diesen möchte ich mal als erstes kommen
da ich mit deiner gewaltig veränderten Schriftgröße auf Teilweise 36 nicht zurecht kam, hab ich mir das in eine eigene Tabelle kopiert, daher nicht wundern, wenn die Größenverhältnisse und Farben etwas anders sind.
Rückfragen:
Also entweder ist hier der Text falsch oder die Formel falsch, denn hier rechnest du ebenso den Samstag mit ein, ich habe dies in meiner Auswertung angepasst auf den Text, der Samstag ist also nicht mit verrechnet.
hier ziehst du die Stunden die unter der Woche (Mo-Fr) geleistet werden von den Pflichtstunden ab, warum beziehst du nicht auch die anderen Stunden mit ein? Denn auch die an Feiertagen oder Sonntagen geleisteten Stunden zählen ja in das Stundensaldo.
Einzig und alleine frage ich mich wie du den Unterschied machen willst zwischen Überstunden die bereits mit 25/50/100% Zuschlag vergütet wurden und Überstunden die noch noch gar nicht mit 20% Zuschlag vergütet wurden, verstehst du was ich meine? Oder wird der Überstunden Zuschlag noch auf Bonus Zuschlag von Feiertag etc. draufgezahlt? Egal wie hier müsstest du wissen wie Euer Buchungssystem bei der Ermittlung von Überstunden agiert.
wofür dient die Spalte Feiertag ohne Beschäftigung, sowie Krank und Urlaub als Stunden, ich habe jetzt hier in meiner Berechnung einfach mal 8h für einen Krankheitstag und Urlaubstag angenommen, ggf. müsstest du das gemäß des Vertrages anpassen.
ÄnderungenDa ich deine Auswertung und auch den Aufbau der Tabelle für äußerst ineffektiv halte für eine effektive Auswertung habe ich das Prinzip mal ein wenig geändert.
Du hast nun 3 Tabellen
Eintragungen: hier trägst du ganz normal deine Stunden in Beginn;Ende;Pause ein. Das Datum geht dabei für ein gesamtes Jahr im voraus.
Wenn die Endzeit eines Tages auf 00:00 Uhr endet, habe ich hier 24:00 eingetragen, optisch steht aber noch immer 00:00 in der Zelle, guckst du in die Berarbeitsungsliste, wirst du sehen, dass eigentlich 1 00:00 drin steht, das macht es einfacher zu rechnen besonders bei der Verrechnung mit Leerzeilen, denn die enthalten leer auch 0 alternativ kannst du einfach 1 eingeben, das wird auch in 1 00:00 umgewandelt. Denn 24 Uhr ist ja exakt 1 Tag.
die X und O dienen zum ein und Ausblenden in der Tabelle Auswertg, die gelben Werte oben sind nur die Summe der jeweiligen Spalte für mich und werden in der fertigen Tabelle entfernt, diese dienten nur dazu um besser Änderungen zu verfolgen zu können.
Auswertung: hier wird die Tabelle Eintragungen mit einem Filter abgeholt und gemäß den Kriterien (momentan nur Monat, gefiltert, so dass du immer nur einen Monat auswertest, unterschreiben und einreichen kannst. Ebenso habe ich die Möglichkeit eingebunden einzelne Spalten ausblenden zu können, z.B. denke ich das Nachtschichtbeginn und Ende nicht zwingend dort sichtbar sein muss. Vorsicht beim ausblenden von Spalten die für die Berechnung von Nöten sind, tust du da das werden einzelne Werte #NV als Ergebnis liefern.
Ebenso habe ich eine Bedingte Formatierung eingebaut, damit Sa, So, Wochentage und Feiertage unterschiedlich hervorgeheben werden. Urlaub und Krank habe ich noch nicht implementiert, das kannst du ja gerne hinzufügen und bei Bedarf die Farben ändern oder entfernen.
Sondertage: hier trägst du alles ein, wie z.B. Krank, Urlaub oder Feiertage
Urlaub und Krank wird dabei nach dem Prinzip von bis eingetragen, eine zusätzliche Spalte stapelt diese Werte nun in einer Liste übereinander.
... damit diese Liste mit den Datumwerten in der Originaltabelle verglichen werden kann und All jene Werte automatisch eingetragen werden können.
Ich habe hier und da ein paar Eintragungen vorgenommen, alle Eintragungen, die ich vorgenommen habe, habe ich in gelb markiert, damit du sie jederzeit schnell finden und rauslöschen kannst.
Kommen wir nun zu den Formeln, da hier ein Haufen Formeln zum Einsatz kommen halte ich es nicht für Hilfreich dies via Formeltext Formel in einem Screenshot anzuzeigen ich liste die daher hier auf:
IN EINTRAGUNGEN:
A5
=SEQUENZ(TAGE(DATUM(JAHR(C1)+1;1;1);DATUM(JAHR(C1);1;1));;C1)
Dies listet alle Daten des gesamten Jahres auf.
B5
=WENN(ISTZAHL(XVERGLEICH(A5#;XVERWEIS(WAHR;Sondertage!A2:A50>0;Sondertage!A2:A50):XVERWEIS(WAHR;Sondertage!A2:A50>0;Sondertage!A2:A50;;;-1)));"Feiertag";"")
der 2 und 3 XVERGLEICH dient dabei dazu die erste und letzte Spalte des Datenbereichs zu ermitteln, da die Anzahl der Feiertage oder individueller ggf. freiwillig gewährter Urlaubstage in ihrer Länge ja stets variieren kann. Ich habe daher einen Bereich von fast 50 Zeilen genommen und kürze ihn ein auf den Tatsächlich genutzten, solltest du also hier über 50 Zeilen kommen, müsstest du diesen Teil der Formel anpassen und beide A2:AXXX das XXX auf so hoch wie eben nötig setzen.
Der anschließende xvergleich, ermittelt nun noch ob das aktuelle Datum Teil dieser Feiertagsliste ist, falls das so ist gibt es eine Zahl aus, an wievielter Stelle der Eintrag gefunden wurde und schreibt auf Grund der WENN Prüfung das Feiertag in die Liste, theo. kannst du dies mit Urlaub und Krank exakt genauso machen und hier hinzufügen.
F5
=REST(D5-C5;1)-E5
Dies sind die gesamten Arbeitsstunden des jeweiligen Tages, abzüglich der Pause, im Gegensatz zu dir habe ich hier alle Tage einbezogen.
Das Rest kannst du theoretisch weglassen, ist bei mir Macht der Gewohnheit, da man dann weniger Probleme mit Tageübergreifenden Einträgen bekommt, wo z.B. 18:00 bis 06:00 gearbeitet wird, ohne den Rest gäbe es hier einen Fehler, da Excel keine negativen Stunden darstellen kann (1900 Datumformat).
G5
=WENN(B5="Feiertag";REST(D5-C5;1)-E5;0)
hier ermittle ich die Arbeitsstunden an einem Feiertag
H5
=WENNS(UND(C5:D5="");0;WOCHENTAG(A5;11)=6;MAX(0;MIN(M5;D5)-C5)-E5;1;0)
hier exakt das gleiche nur für Samstage, die Pause wird dabei immer in dem Bereich bis 22 Uhr abgezogen.
I5
=WENN(WOCHENTAG(A5;11)=7;D5-C5-E5;0)
hier das Gleiche für Sonntage.
O5
=LET(von;BEREICH.VERSCHIEBEN(A5#;;2);bis;BEREICH.VERSCHIEBEN(A5#;;3);
WENNS(WOCHENTAG(A5#;11)=7;0;bis>M5;bis-WENN(von>M5;von;M5);von<N5;WENN(bis<N5;bis;N5)-von;1;0) )
Bei den Nachtstunden habe ich eine Arrayformel gewählt, da ich die irgendwie einfacher zu händeln fand. Ich denke die Funktion muss ich nicht groß erklären, lediglich LET ist hier besonders, mit LET kann man Daten oder Datenbereiche als Variabelnnamen abspeichern und diesen dann immer wieder verwenden. A5# gibt die Gesamte Spalte A ab der Zeile A5 wieder., somit kann ich mich durch Bereich verschieben immer auf die gesamte Tabelle beziehen, da dessen Länge ja zwischen Schaltjahren um 1 variieren kann.
P5
=WENNS(UND(C5:D5="");0;B5="Feiertag";(MAX(0;MIN(M5;D5)-C5-E5)*1);WOCHENTAG(A5;11)<=5;(MAX(0;D5-MAX(C5;M5)-E5)*0,25)+(MAX(0;MIN(D5;N5)-C5)*0,25);WOCHENTAG(A5;11)=6;(MAX(0;MIN(M5;D5)-C5-E5)*0,2)+(MAX(0;D5-MAX(C5;M5))*0,25)+(MAX(0;MIN(D5;N5)-C5)*0,25);WOCHENTAG(A5;11)=7;(MAX(0;MIN(M5;D5)-C5-E5)*0,5))
Hier verrechne ich in einer neu kreierten Spalte alle geleisteten Stunden mit Sondervergütung direkt mit ihren passenden Werten.
IN AUSWERTUNG:
A6
=SPALTENWAHL(Eintragungen!A4:P4;ZUZEILE(WENNS(Eintragungen!A3:P3="x";SPALTE(Eintragungen!A3:P3));2))
dies sorgt für die Spaltenkopfnamen und blendet die ausgewählten Spalten aus
A7
=SPALTENWAHL(FILTER(Eintragungen!A5:P369;MONAT(Eintragungen!A5#)=Auswertung!B1);ZUZEILE(WENNS(Eintragungen!A3:P3="x";SPALTE(Eintragungen!A3:P3));2))
Das macht exakt das gleich mit der gesamten Tabelle außerdem Filtert es die original Tabelle nach dem in B1 ausgewählten Monat (im Screenshot 4 (April)).
S10
=SUMME(INDEX(A7#;;VERGLEICH("Arbeitsstunden";A6#;0))*(WOCHENTAG(INDEX(A7#;;VERGLEICH("Datum";A6#;0));11)<=5))*24
Summiert alle Stunden von Mo-Fr
S11
=SUMME(INDEX(A7#;;VERGLEICH("Arbeitsstunden";A6#;0))*(WOCHENTAG(INDEX(A7#;;VERGLEICH("Datum";A6#;0));11)<>6))*24
Summiert alle Stunden ausgenommen Samstag?
S12
=SUMME(INDEX(A7#;;VERGLEICH("Nachtstunden";A6#;0)))*24
Summiert alle Nachtstunden
S13
=SUMME(INDEX(A7#;;VERGLEICH("Stunden "&ZEICHEN(10)&"Samstag";A6#;0)))*24
S14
=SUMME(INDEX(A7#;;VERGLEICH("Stunden "&ZEICHEN(10)&"Samstag";A6#;0)))*24
Summiert alle Samstage und Sonntage
S15
=SUMME(INDEX(A7#;;VERGLEICH("Urlaub";A6#;0)))*24
S16
=SUMME(INDEX(A7#;;VERGLEICH("Krank";A6#;0)))*24
Summiert Urlaub und Krank
die ganzen Aufsummierungen habe ich unverändert gelassen, daher liste ich diese hier nicht gesondert auf
Ich habe allerdings noch 2 Alternativen erstellt ohne den Zwischenschritt über die Stunden zu gehen, sondern das ganze direkt in Euros umzurechnen
V25
=SUMME(INDEX(A7#;;VERGLEICH("Mehrarbeit";A6#;0)))*24*S20
Summiert die Mehrarbeit auf, dies beinhaltet alle Sondervergüteten Stunden außer Überstunden.
V26
=LET(Datum;INDEX(A7#;;VERGLEICH("Datum";A6#;0));Beginn;INDEX(A7#;;VERGLEICH("Beginn";A6#;0));Ende;INDEX(A7#;;VERGLEICH("Ende";A6#;0));
SUMME(WENN(WOCHENTAG(Datum;11)<=6;WENN(Ende>=S30;Ende-WENN(Beginn<S30;S30;Beginn);0)+WENN(Beginn<=S31;WENN(Ende>S31;S31;Ende)-Beginn;0);0)*24*S23*S20) )
Summiert alle Nachstunden in Euros
V27
=LET(Beginn;INDEX(A7#;;VERGLEICH("Beginn";A6#;0));Ende;INDEX(A7#;;VERGLEICH("Ende";A6#;0));
SUMME(WENN(WOCHENTAG(INDEX(A7#;;VERGLEICH("Datum";A6#;0));11)=6;WENN(Ende>S30;S30;Ende)-Beginn-INDEX(A7#;;VERGLEICH("Pause";A6#;0));0)*24*S25*S20) )
Summiert alle Samstagszuschläge in Euros
V28
=SUMME(WENN(WOCHENTAG(INDEX(A7#;;VERGLEICH("Datum";A6#;0));11)=7;(INDEX(A7#;;VERGLEICH("Ende";A6#;0))-INDEX(A7#;;VERGLEICH("Beginn";A6#;0))-INDEX(A7#;;VERGLEICH("Pause";A6#;0)))*24*S26*S20))
Summiert alle Sonntagszuschläge in Euros
V29
=SUMME(WENN(INDEX(A7#;;2)="Feiertag";(INDEX(A7#;;VERGLEICH("Ende";A6#;0))-INDEX(A7#;;VERGLEICH("Beginn";A6#;0))-INDEX(A7#;;VERGLEICH("Pause";A6#;0)))*24*S26*S20))
Summiert die Feiertagszuschläge in Euros
ACHTUNG:
Beim Umbenennen der Spaltenköpfe die meisten Formeln beziehen sich auf den Spaltenkopfnamen, damit man die Formeln unbeschadet kopieren oder der Spalten beliebig verschieben kann.
- - -
alte Antwort
ich sehe hier tatsächlich nur ein relativ langes WENNS als Formel.
Sinnbildlich
WENNS((Wochentag=6)*(Uhrzeit<22Uhr);20%;Wochentag=6;25%;(Wochentag=7)*(Uhrzeit<=1:37Uhr);1;0%)
Die 1 am Ende steht nur für die Definition des Standardwertes, alternativ kannst du auch WAHR statt 1 schreiben, aber 1 ist halt kürzer, spricht trifft keine der Bedingungen zu wird dieser Wert gewählt.
Verstehe ich es richtig, ab Sonntag 1:37 gibt es keinerlei Zuschlag mehr.
Kein Problem, jeder hat mal angefangen, kannst du mir vielleicht mal einen Ausschnitt deiner Datei zur Verfügung stellen, damit ich mir die nicht nachbauen muss, dann stelle ich dir gerne eine Lösung zur Verfügung.
Welche Excel Version kommt bei dir zum Einsatz?
Denn man muss ja noch zwischen Arbeitsbeginn und Arbeitsende unterscheiden. Da aber Excel nicht Minute für Minute auswertet sondern sich nur auf die Start und Endzeit beziehen kann. Muss man noch den Fall abprüfen wenn der Tag gewechselt wird oder eine gewisse Uhrzeit überschritten wird. (In deinem Fall 22 und 0 Uhr)
Alternativ könnte man ein dynamisches Array erzeugen mit jeder Minute zwischen Start und Endzeit. Das geht aber richtig schön erst ab Excel 2021 ggf. sogar erst mit Excel 2024 müsste ich tatsächlich mal ausprobieren.
Hi Gipfelstuermer, ich nutze Excel 2021. Ich weiß nicht ob das zulässig ist, aber ist es besser wenn ich meine excel datei direkt beifüge. Hab auf Mega.nz hochgeladen
https://mega.nz/file/JBkXkK4a#kEE0FtdWoG7Fl1j9Il8kVx5hmrR-YhKukbuFpSUfnzw
bevor ich die Datei wieder hochlade, möchte ich nochmal kurz beschreiben was ich getan habe, auf Grund von fehlender Möglichkeit Bilder in Kommentaren einzubetten, werde ich hier für meine Antwort nutzen.
Wow das ist ein riesen glotz für mich als Anfänger. Muss ich eine komplette neue Tabelle erstellen. Na ja muss ich mich auf die Arbeit machen, mit der hoffnung das ich alles hinbekommen. Besten Dank.
Ich kann dir die gerne zurück schicken, dann kannst du mal reinschauen.
Mir wäre deine Tabelle mit den ganzen Einzelspalten zu unübersichtlich ich wollte dir aber nicht alles weglöschen jedem wie er es mag. Ich würde das aber in die Auswertung verlagern durch Logikabfragen.
Ich verstehe was du meinst. Aber für eine anfänger ist es bisschen zu chaotisch, klar Ich habe keine 2 linke hände. Wäre für dich zu kompliziert all diese formel, auf meine tabelle einzufüge oder eine deine schon fertig tabelle die du für mich mit all diese formel getestet hast? Vielen Dank
Guten abend Gipfelstuermer, wolltest mir was schicken?
Sorry hab ich wohl übelesen die letzte Antwort ja schick ich rüber.
https://uploadnow.io/f/Yk54Z5t
hier die Tabelle, kannst ja mal mit meiner Erklärung auf dem 2. Bildschirm drüber schauen, ob du verstehst was ich meine und das auch in Zukunft bedienen kannst, ansonsten fühle dich frei erneut zu fragen.
Hi Gipfelstuermer, entschuldige mich für meine wiederholung, bin eine Anfänger. Meine Arbeitszeit (Sa+So) ist von 16:20 Uhr Samstag, bis 01:37 Uhr Sonntag. Für das WE, gibt 3 verschiedene zuschläge: Samstag von 16:20 bis 21:59 uhr gibt das 20% zuschlag. Samstag ab 22:00 uhr bis 23:59 gibt das 25% nachtzuschlag (wird immer das höchste zuschlag berechnet). Ab 0:00 springt der Sonntag (Sonntag zuschlag, egal ob Sonntag Nacht ist oder Tag, das höchste ist 50%, ausser ist ein Feiertag (100% Zuschlag). In meine screenshots ist die Tabelle ok, oder muss ich eine weitere zelle oder spalte einfügen? Wo muss ich genau deine Formel einfügen?