[Excel] Berechnen von Zuschlägen (Lohnabrechnung)

[Prisoner.o.Time]

Hallo zusammen,

hab mal wieder ein Problem und stehe so dermaßen auf dem Schlauch, dass ich nicht weiß wo ich anfangen soll.

Was ich möchte ist relativ leicht erklärt und steht vom Prinzip her schon in der Überschrift. Ich habe eine Datenbank wo meine genauen Arbeitszeiten eingetragen werden. Nun möchte ich mir für jeden Tag (den jeweiligen Monat) ausrechnen wieviel ich von welchen Zuschlägen (in Stunden) bekomme.

Zur Auswahl stehen 3x (4x) unterschiedliche Steuerarten:

Stunden normal versteuer: 06.00 bis 23.00
Stunden 25% Steuerfrei: 23.00 - 00.00 und 04.00 - 06.00
Stunden 5% Steuerpflichtig: 23.00 - 00.00 und 04.00 - 06.00
Stunden 30% Steuerfrei: 00.00 bis 04.00

Ziel ist es quasi mit wenigen klicks meine Gehaltsabrechnung auf Richtigkeit zu überprüfen. Aber wo fange ich an?

Zur veranschauung im Anschluss mal mein Lohnzettel (leicht geschwärzt) incl. aktueller Tabelle, leider noch ohne Formeln.

PS: Meine excel Skills halten sich mehr wie in Grenzen, daher würde ich leicht verständliche Antworten äußerst begrüßen. Habe auch keine Probleme mit Recherchearbeiten, jedoch muss ich dafür halt die Antwort auch verstehen.

PPS: Meine aktuellen Excel Skils kann man recht gut an dem forhergehenden Thema beurteilen:
https://www.computerbase.de/forum/threads/excel-mehrere-filter-gleichzeitig-setzen-makro.1833775/




NACHTRAG:

Hab da grad was hilfreiches gefunden.

https://www.ms-office-forum.net/forum/showthread.php?t=273200

Versuch ich mal zu studieren.

http://www.office-loesung.de/ftopic327169_15_0_asc.php

Verucht auch zu studieren, aber einfach zu hoch^^

Im Endeffekt bin ich mit beidem nicht Schlauer geworden. Erstes ist in meinen Augen schon mal ungeeignet. Zweites vielleicht, aber da blick ich einfach nicht durch

 

[Pyrukar]

vielleicht liegts an meiner Unerfahrenheit mit Steuererklärungen, aber mit den % angaben komme ich nicht zurecht ... kannst du vielleicht das noch etwas ausformulieren, was genau du haben willst

Wenn ich das jetzt richtig interpretiere hast du Arbeitszeiten rund um die Uhr und bekommst je nach Uhrzeit verschiedene Zuschläge bzw Steuerbefreiungen ... willst du jetzt einfach nur eintragen von wann bis wann du gearbeitet hast und die Excel Tabelle soll das in die Entsprechenden Kategorien einsortieren und dir am Schluss einen Gesamtbetrag mit allen Zuschüssen und Steuerbefreiungen ausspucken? Habe ich das so richtig verstanden?

EDIT: Okay ich habe mal eine Kleine Excel Tabelle erstellt, die das Problem zumindest in einfacherer Form löst. Ich habe jetzt nur mal die Unterscheidung zwischen Normalen Arbeitszeiten (6-23 Uhr) und Nachtarbeitszeit (23-6) Uhr gemacht. (vgl Anhang). Jetzt ist natürlich wichtig, dass du auch verstehst wie ich das gemacht habe, denn nur diese Formeln bringen dich wohl nicht weiter.


Ersteinmal ist wichtig zu wissen, wie Excel Urzeiten Speichert. Dafür kannst du gerne mal selbst ein bisschen experimentieren 1. Einfach eine Urzeit im Format h:mm eingeben. 2. Das Format sollte von Excel automatisch auf Uhrzeit umgestellt werden. 3. Das Format manuell wieder auf Standard zurückstellen und sehen was in der Zelle steht. 2 für mein Beispiel Wichtige Zahlen waren 6:00 und 23:00 die als 0,25 und 0,958333333333333 von Excel gespeichert werden. Grundsätzlich sind bei Excel 0-24 Uhr als 0-1 definiert. Wichtig bei der Eingabe von Arbeitszeiten über den Tageswechsel ist also dass du nicht mit 0:00 weiter machst sondern mit 24:00 etwas unintuitiv aber sonst versteht Excel es nicht.

Unintunitiv ist ebenfalls ein gutes Stichwort für die ganzen Formeln die ich verwendet habe und im Folgenden versuche aufzuschlüsseln. (ich erhebe keinen Anspruch darauf die Schönste oder am leichtesten Verständliche Version gefunden zu haben, aber sie Funktioniert soweit)

Also die Grundfunktionen (Wenn,Und,Oder) sollten Grundsätzlich bekannt sein (ansonsten gibts sicherlich genügend tutorials)

Formelerklärung am Beispiel Start Kategorie 1.

=WENN(UND(ODER(D12>0,25;D12>1,25);D12<0,958333333333333);D12;WENN(UND(D12>0,958333333333333;E12<1,25);0,958333333333333;0,25))*WENN(D12=0;0;1)

Hier überprüfe ich im ersten Wenn ob der Arbeitsstart vor 6 Uhr oder 6 Uhr (am zweiten Tag) liegt. Wenn ja wird 6:00 aka 0,25 eingetragen. In der zweiten Wenn-Funktion überprüfe ich, ob der Arbeitsanfang zwischen 23:00 und 6 Uhr am Zweiten Tag liegt, wenn Ja wird ebenfalls 0,25 Eingetragen. Nur Wenn beide Wenn-Funktionen Falsch sind wird der Wert aus der Zelle verwendet, denn dann ist der Arbeitsbeginn definitiv zwischen 6 und 23 Uhr. Die Letzte *Wenn-Funktion dient hauptsächlich der Kosmetik und trägt bei leeren Spalten eine 0 ein

Wie du wahrscheinlich an diesem Beispiel bereits sehen kannst ist dein Vorhaben mMn absolut nicht Trivial, denn du kannst dir wahrscheinlich denken, wie das ganze mit 4 Kategorien aussehen würde. Andererseits gibts vielleicht auch einen Anschaulicheren Weg dein Problem zu lösen, den ich jedoch nicht kenne. Wenn ich eine solche aufgabe Lösen müsste hätte ich wohl VBA angestrengt.

Wenn ich dir die Anderen Formeln auch noch erklären soll oder du an dieser erklärung etwas nicht verstanden hast dann markiere mich bitte mit @ damit ich das in meine Infobox bekomme

 

[Prisoner.o.Time]

@ Pyrukar

Vielen Dank für deine Antwort. Hab diese mal überflogne (bin gerade auf dem Weg ins Bett), werde sie mir aber nach der Arbeit mal genauer ansehen. Aber auf die Schnelle habe ich jetzt keinen Punkt gefunden wo effektiv die Anzahl der Zuschläge in Stunden angezeigt werden.

Ich hab mal meine bisherige "Arbeit" oben angehangen. Sieht etwas spartanisch aus, liegt aber daran dass ich mir fürs erste nicht besser zu helfen gewusst habe.

Die eingetragenen Daten sind der Ist Stand. Da bei uns im 15min. Takt abgerechnet (gebucht) wird, entsprechen die Daten zwar dem aus dem Monat Februar, wurden aber auf den 15min. Takt angeglichen. Des weiteren habe ich die Arbeitszeit gesplittet. Liegt daran, dass ich wärend der Pause keine Zuschläge beziehe. Das sollte es etwas leichter gestalten. Die entsprechenden Zuschläge wurden ebenfalls notiert.

Plan ist jetzt eine passende Formel zu finden mit der ich für den Jeweiligen Tag dann die einzelnen Zuschläge in Stunden angegeben bekomme. Danach kann ich via Pivot Tabelle mir hoffentlich ein "Formular" erstellen wo mir dann immer der "aktuelle" Monat angezeigt wird und ich auf einen Blick sehe ob Amazon meinen Lohn korrekt berechnet hat.

Zum Vergleich hänge ich auch mal einen leicht geschwärzten Lohnzettel fom selben Monat an. Hab auch fix extra die für mich relevanten Stellen farblich markiert.

So jetzt muss ich aber wirklich dringend ins Bett ;-)

 

[Pyrukar]

@Prisoner.o.Time Hättest du die Anhänge nicht gleich dazu liefern können, ich hab das ziemlich falsch verstanden, was du brauchst

Okay also wenn ich es jetzt richtig verstehe (sicher bin ich mir immer noch nicht) möchtest du eine Excel Tabelle in der du die Schicht einträgst und das dir dann sagt, welchen Zuschlag du bekommst bzw ob darauf Steuern gezahlt werden müssen oder eben nicht
Ich hatte das so interpretiert, als ob du sehr unregelmäßig Arbeitszeiten hast und diese dann Minutengenau bestimmen möchtest ... aber da hatte ich das dann wohl falsch verstanden.

Um weitere Missverständnisse auszuräumen, würde ich dich Bitten nochmal exakt zu beschreiben, was du brauchst und was das Excel Dokument können soll.

Du sprichst von 15min Takt, gibtst letztlich aber nur 6 verschiedene Schichtarten (mit jeweils festen Stundenzahlen) an. Auch wann deine Pausen sind ist hieraus nicht erkenntlich? Kann an einem Feiertag auch Überstunden möglich sein? Bei Überstunden gibts ja auch eine Wochenregel, weshalb die Darstellung in Kalendarischer Monatsübersicht sehr fragwürdig ist. Sinnvoller wäre hier wohl eine Kalenderwochen Darstellung um die gesamten Überstunden der Woche bestimmen zu können.

Wenn ich das aber Richtig sehe arbeitest du fast ausschließlich nachts dh. die Kategorie früh und mittelschicht können eigentlich ausgeblendet werden, oder? Ebenso die Geschichte mit den 25% steuerfrei in der Nachtschicht, da das ja nur für unregelmäßige Nachtschichten gilt. Du bekommst ja, soweit ich das sehe, durchgehend 30% zuschlag komplett steuerfrei?!

das sind sehr viele Fragezeichen die da gerade noch sind, ich hoffe auf etwas mehr imput zur klärung

 

[Prisoner.o.Time]

@ Pyrukar

Soweit ich das beurteilen kann, müsste doch alles in meiner Excel Tabelle drinnen stehen?

Haha ja würde es auch wenn ich die richtige Tabelle hochgeladen hätte.

Tja, war in der Früh halt nicht mehr soo fit. Da kann das mal passieren. Jetzt sollt aber alles passen bzw. hoffentlich auch alles selbsterklärend sein.

Aber um auf das was ich will bzw. die Excel Tabelle soll noch einmal zurück zu kommen. Ich möchte die Excel Tabelle regelmäßig pflegen indem ich meine getätigten Arbeitszeiten eintrage damit mir dann am Ende des Monats eine Pivot Tabelle sagt ich bekomme in diesem Monat xy Stunden welche 30% steuerfrei sind, xy Stunden welche 25% steuerfrei sind und xy Stunden welche 5% steuerpflichtig sind. Sprich damit ich auf einen Blick erkennen kann ob das rot markierte auf meinem Lohnzettel richtig berechnet wurde.

Nice to have wäre noch, wenn alles was über die reguläre Arbeitszeit hinaus geht, also entweder vor oder danach mit Überstundenzuschlag in vorm von 15% Steuerpflichtig berechnet wird. Aber das ist die kleinste Baustelle und kommt zu mindest bei mir sehr selten vor. Würde die Tabelle aber gern auch anderen Kollegen zur verfügung stellen.

 

[Prisoner.o.Time]

Mein Problem ist einfach, dass ich nicht weiß wie man mit Uhrzeiten rechnet. Vom Prinzip müsste die Formel so aufgebaut sein:

Wenn Arbeitszeit in Zeit von Zuschlag X enthält dann nimm die darin enthaltene Zeit und multipliziere diese mit 24

Oder

Wenn Arbeitszeit in Zeit von Zuschlag X enthält dann zeige die darin enthaltenen Stunden an (Dezimalwert)

Sprich z.B.

Wenn 21:40 bis 24:30 in 23:00 bis 24:00 enhalten, dann nimm die enthaltene Zeit (23:00 bis 24:00) und multipliziere mit 24

Oder

Wenn 21:40 bis 24:30 in 23:00 bis 24:00 enhalten, dann zeige die enthaltenen Stunden an (Dezimalwert)

Danach sollte ich quasi einen Stundenwert in Dezimal haben.

 

[Pyrukar]

Mein Problem ist einfach, dass ich nicht weiß wie man mit Uhrzeiten rechnet. Vom Prinzip müsste die Formel so aufgebaut sein:

Das habe ich dir doch ausführlich oben erklärt excel definiert 0-24 Uhr als 0-1 demnach ist z.B. 6:00 0,25 12:00 wäre 0,5 und 18:00 wäre 0,75. Alle zwischenwerte einfach als Uhrzeit eingeben und zu Standart umformatieren. Schon steht der entsprechende Wert da. man kann auch vergleichend mit Uhrzeiten rechnen, wie mein Beispiel zeigt. nur in der Formel muss man afaik auf die Dezimalschreibweise zurückggreifen
um an Arbeitsstunden zu kommen subtrahiere einfach die Startzeit von der Endzeit und schwupps kapiert Excel dass du eine Stundenangabe hast (auch in meinem Beispiel zu sehen )

später versuche ich vll den rest mal umzusetzen

 

[Prisoner.o.Time]

Hallo, hab noch nen Heißen Tipp von nem Kollegen bekommen. Muss ich mir die Tage mal ansehen bin nur gerade etwas angeschlagen, weshalb mir auch ohne Excel schon der Kopf raucht. ;-)

BTW.

 

[Prisoner.o.Time]

Aktueller Stand. Scheint soweit zu funktionieren. Zu mindest ist laut meiner Auswertung der Monat Februar richtig berechnet.

 

[wut123]

@

Pyrukar​

hallo, genau das was du als Bsp. hochgeladen hast brauche ich, verstehen tu ich nur Bahnhof.
Mein Hauptproblem wie du auf die 0,25 0,5 usw. kommst habe ich kapiert, aber was mache ich:

a. was gebe ich bei 23:00Uhr ein (23:00 bis 03:00 Geisterschichtzuschlag)
b. wenn 2 Schichtzuschläge zum tragen kommen, wie bekomme ich es hin das er den höheren nimmt?

Hättest du da einen Tipp?

Gruß
Marcus

 

[Pyrukar]

a. 23:00-27:00 klingt komisch ist in excel aber so

b. ohne jetzt nochmal in mein Uralt beispeil geschaut zu haben ... entweder mit Max (solange du anhand von Zahlenwerten entscheidest, oder Wenn() falls du manuell den höheren entscheiden musst)

 

[steve1da]

Hola,
für Nachtschichten gibt es eigene Formeln da muss man das Rad nicht unnötig neu erfinden.
https://www.herber.de/excelformeln/pages/Nachtschichtarbeitszeit_ermitteln.html
Gruß,
steve1da

 

[wut123]

wow, danke für die schnelle Hilfe

 

[wut123]

Bekomme das irgendwie nicht mit den beiden Formel-Varianten hin. Das Bild ist nur eine optische Darstellung welche Berechnungsschemen es gibt in unserer Firma.

 

[wut123]

habe jetzt alles irgendwie hinbekommen, mit Hilfsspalten usw. nicht schön aber es funktioniert nur nicht wie ich das Sonntags- und Feiertagsarbeiten. Mit dem Sonntag und Feiertag gilt 24h plus von 0 bis 4Uhr wenn ich vor 0Uhr angefangen habe?

 

[cumulonimbus8]

Mir kommt das immer so vor, dass Excel manchmal unmögliche Wunderdinge aus dem Handgelenk macht , aber bei normalen Dingen täglicher Anforderungen eine Bauchladung praktiziert.
Zeit und Datum scheinen da ganz besonders weit mit vorne zu sein.

Möglicherweise liegt der Hase im dem Pfeffer begraben, dass man jeden Zeitpunkt komplett als Datum plus Uhrzeit erfassen muss um (oh weh…) mit eigenen Formeln und teils komplexen Bedingungen reale Zeitdifferenzen zu erfassen. Selbst mit VBA-Logik ist das einige Arbeit.
(Ich habe einige Fantasie wie man ins Schlingen kommen kann…)

Ich denke, dass Zeiterfassungsprogramm hier die bessere Lösung sind nicht »das Rad« sondern ein ganzes Getriebe neu erfinden zu müssen.

CN8