Berechnung: monatl. Bruttovergütung (Gehalt-/Stundenlohnempfänger) mit Kürzung/Anstieg bei Veränderung

[Rafter]

Liebe Forengemeinde,

in meiner Tabelle wird die Bruttovergütung pro Monat bei Gehaltsempfängern (N19-N30) ab einem Startzeitpunkt/Beginn (B4) berechnet. Durch die Eingabe eines zweiten Startzeitpunkts (B5), (bei einer Gehaltsveränderung), wird das Gehalt entsprechend kalendertäglich hochgerechnet bzw. heruntergebrochen.
D4 enthält das Monatsgehalt + evtl. Zuschläge H4 zum ersten Startzeitpunkt,
D5 und H5 das Monatsgehalt zum 2. Startzeitpunkt + evtl. Zuschläge.

Liegt der Startzeitpunkt im laufenden Monat wird das Gehalt, entsprechend der Kalendertage gekürzt.
Das funktioniert auch soweit wunderbar!
Beispiel: Eintritt zum 10.01.22 mit Gehalt 4.100,- € (10.01.-19.01.22) 10 Kalendertage (4.000/31*10)+ ...
Gehaltserhöhung zum 20.01.22 mit Gehalt 8.100,- € (20.01-31.01.22) 12 Kalendertage (8.000/31*12)
Siehe angehangene Tabelle

Die Formel zur Berechnung des Bruttovergütung hat mir mikele aus diesem Forum erstellt.
Vielen Dank nochmal an dieser Stelle dafür!

Nun möchte ich, dass eine Kürzung des Gehalts auch bei Eingabe eines Austrittsdatums (B11) erfolgt und die Bruttovergütungen nachfolgender Monate entsprechend mit 0,- € ausgegeben werden.

Kann mir bitte jemand die Formeln (N19-N30) dahingehend erweitern?
Am oberen Beispiel: Austritt zum 28.01.22
(10.-19.01.22) = 10 KT (4.000/31*10) + (20.-28.01.22) = 9 KT (8.000/31*9)
Ab 01.02.22 Gehalt = 0,- €

Im nächsten Schritt hätte ich gerne, dass dasselbe Prinzip bei Stundenlohnempfängern (wenn D4 leer) mit festen Arbeitszeiten (M4-M10 und N4-N10) angewendet wird:
Dazu bitte die bestehenden Formeln aus N19-N30 erweitern, da die Tabelle auch für Stundenlohnempfänger genutzt wird.
Startzeitpunkt/Beginn Stundenlohn = B6, Höhe des Stundenlohns = D6, evtl. Zuschlag zum Stundenlohn = H6.
2. Startzeitpunkt Stundenlohn = B7, Höhe des 2. Stundenlohns = D7, evtl. Zuschlag zum Stundenlohn = H7.

Durch die Eingabe der täglichen Arbeitszeit in den Zellen M4-M10 (zum ersten Startzeitpunkt) und N4-N10 (zum zweiten Startzeitpunkt) werden die Arbeitsstunden für jeden Tag des Jahres in den Hilfstabellen AV2-BR32 (für Gehaltsempfänger) und BW2-CS32 (für Stundenlohnempfänger) automatisch gefüllt. Dabei werden Eintritt bzw. Austritt bereits berücksichtigt (Tage vor Eintritt bzw. Tage nach Austritt werden automatisch auf 0 Stunden gesetzt).

Im Zellbereich J19-J30 werden die Arbeitsstunden unter Berücksichtigung von Eintritt (B10) und Austritt (B11) eines jeden Monats bereits korrekt ausgegeben.
Mit den täglichen Arbeitsstunden in der Hilfstabelle BW2-CS32 (Stundenlöhner) sollten sich also die Bruttovergütungen der Stundenlöhner berechnen, Kürzungen bei Eintritt bzw. Austritt berücksichtigen bzw. eine Veränderung des Stundenlohns (B7, D7 und H7) einbeziehen lassen.

Im letzten Schritt wäre es großartig wenn bei Eingabe eines Start- und Enddatums (unbezahlte Fehlzeiten) in den Minitabellen
Kind krank mit Krankengeld = AO3-AQ12
Krank mit Krankengeld = AO19-AQ22 sowie
Elternzeit = AO29-AQ32, die ausgegebenen Arbeitsstunden in den Hilfstabellen (AV2-BR32 und BW2-CS32) in den angegebenen Zeiträumen entsprechend auf 0 Stunden gesetzt werden.
Dazu müssten die Formeln der Hilfstabellen ergänzt werden und die Bruttovergütungen bei Gehalts- und Stundenlohnempfängern N19-N30 ebenso um die Zeiträume gekürzt werden.

Ich hoffe ich habe mich verständlich ausgedrückt und es findet sich hier jemand der meine Vorstellungen umzusetzen will/kann. Ich habe mich schon viele Stunden damit herumgequält, bekomme es aber leider einfach nicht hin.
Vielen Dank schon mal an alle, die sich der Sache annehmen.

Viele Grüße
Rafter

[mikele]

Hallo,
deine ersten Wünsche hatte ich dir damals bereits erfüllt. In der neuen Tabelle hast du einige Formeln aber nur teilweise übertragen.
Damit z. B. das Austrittsdatum beachtet wird, änderst du die Formel in BQ36 zu

Code: Alles auswählen

=WENN(ISTLEER(B11);DATUM(A1;12;31)+1;B11+1)

[Rafter]

Hallo mikele,
prima, besten Dank! Das war ja einfach. Wenn man nur weiß wie...

Die fehlenden Formeln wurden nun übertragen. Leider wird das SV-Brutto (N19-N30) und der tägliche Stundenlohn in der Hilfstabelle (AW2-BS32) nur dann korrekt berechnet, wenn beide Startzeitpunkte (B4+B5 & D4+D5 bei Gehalts-/, und B6+B7 & D6+D7 bei Stundenlohnempfängern) und die Tabelle für die entsprechenden Arbeitszeiten M3-M10 und N3-N10, Werte enthalten.
Die Kürzung des SV-Bruttos, bei Eingabe eines Austrittsdatums, erfolgt auch nur, wenn jeweils beide Startzeitpunkte Werte enthalten.
Die Felder für den 2. Startzeitpunkt (B5+D5+ggf. H5 + Arbeitszeit N3-N10 bei Gehalts-/, und B7+D7+ggf. H7 + Arbeitszeit N3-N10 bei Stundenlohnempfängern) werden jedoch nur befüllt wenn es auch tatsächlich eine Gehalts-/Stundenlohnveränderung gab/gibt.
Ohne eine Gehalts-/Stundenlohnveränderung werden bei Gehaltsempfängern nur B4+D4+ggf. H4 und die dazugehörige Arbeitszeit M3-M10 und bei Stundenlohnempfängern B6+D6+ggf. H6 + die Arbeitszeit M3-M10 mit den jeweiligen Angaben befüllt.
In diesen Regelfällen wird das Brutto jedoch falsch ausgegeben und bei Eingabe eines Austrittsdatums, keine Kürzung des SV-Bruttos vorgenommen.
Das Problem kann man zwar umgehen, indem man beide Startzeitpunkte und beide Arbeitszeittabellen mit den gleichen Werten füllt. Das macht es aber etwas umständlich und führt zu Irritationen.

Kann man die Formeln zur Berechnung des SV-Bruttos bzw. die Angabe des täglichen Stundenlohns in der Hilfstabelle so anpassen, das man nicht zwingend beide Startzeitpunkte und beide Arbeitszeitspalten (M4-M10, N4-N10) mit Werten befüllen muss, um eine korrekte Berechnung zu erhalten, wenn es keine Gehalts- oder Lohnveränderung gab/gibt?

Die Berücksichtigung von unbezahlten Fehlzeiten lässt sich wohl nicht ohne weiteres integrieren?

Vielen Dank

Freundliche Grüße
Rafter

[mikele]

Hallo,
ich habe mal an deiner Tabelle herumgefeilt.
Die Berechnung bzw. Entscheidung ob eine Kalendertag ein Arbeitstag ist (Beachtung Feiertage, Tage ohne Lohnfortzahlung) erfolgt in den Hilfstabellen AX2:BU32 (Gehalt) und BY2:CV32 (Lohn). Bei Gehalt wird ein fiktiver Tageslohn und bei Lohn, der an dem Tag gültige Stundenlohn berechnet bzw. angegeben.
Daraus erfolgt dann die Berechnung der Monatszahlungen (ohne Beachtung der Zuschläge und Überstunden - da fehlen mir die Informationen, wie diese Daten zu verabeiten sind).
Darüber hinaus habe ich die Formeln im Kalender vereinfacht. Den benutzten Formatvorlagen für Urlaub, Krankheit etc. habe ich sinnvolle Namen gegeben und für die Spaltenköpfe benutzt. Gleichzeitig habe ich die bedingten Formatierungen im Kalender formeltechnisch vereinfacht.
Für eine einfachere Ermittlung der gültigen Arbeitsstunden pro Tag habe ich zwei Hilfsspalten eingefügt. Hier taucht auch die in meiner letzten Antwort gegebene Formel wieder auf, nun aber in P3.
Damit sollte soweit erstmal vieles beachtet werden. Der Teufel steckt aber im Detail - testen, testen, testen
Die Angabe "Eintritt" in B10 erscheint mir auch noch ein bisschen mysteriös. Im Prinzip ist sie doch gleich dem Wert in B4 bzw. B6 und damit überflüssig???

[Rafter]

Hallo mikele,
erstaunlich! Auf den ersten Blick sieht die Berechnung der Monatszahlungen (O19-30) gut aus. Nun auch mit Angabe von nur einem Startzeitpunkt und unbezahlte Fehlzeiten werden auch soweit korrekt gekürzt.

Leider funktioniert die Berechnung der Urlaubstage (D15-D29) nicht mehr.

Und mit Eingabe des Gehalts (D4) werden die Arbeitsstunden (J19-J30) zerschossen.

Der Teufel steckt im Detail. Wie Recht Du doch damit hast.

Die Zuschläge bzw. Überstunden werden einfach anhand des Stundenlohns (D6 oder D7) berechnet.
Bei Stundenlohnempfängern ist D6 immer angegeben, bei Stundenlohnerhöhung auch D7.
Bei Gehaltsempfängern wird der Lohnsatz ebenfalls in D6, bei Gehaltserhöhung in D7 eingetragen, beide Werte jedoch OHNE Startzeitpunkt B6 oder B7.
Werden Zuschläge gewährt, werden die dort eingetragenen Arbeitsstunden einfach mit dem Lohnsatz und dem jeweiligen Zuschlag multipliziert. Überstunden genauso, nur eben ohne Zuschlag. Hier kommt es aber zu keinen Kürzungen, da immer mit den angegebenen Werten K19-K30, L19-L30 und N19-N30 multipliziert wird. Daher kriege ich die Ergänzung der Formeln der Monatszahlungen um die Zuschläge/Überstunden wohl selbst hin.

Vielen Dank für die Vereinfachung des Kalenders und der bedingten Formatierungen.

Die Angabe Eintritt in B10 steht für den Eintritt im Unternehmen und dient lediglich als Information, seit wann die Beschäftigung ausgeübt wird. Nach der zweiten Gehalts-/Lohnerhöhung gibt der erste Startzeitpunkt (B4 oder B6) nicht mehr den Beschäftigungsbeginn an.

Ich danke Dir vielmals für Deine Mühe und hoffe das Du die nun eingeschlichenen Probleme auch noch beheben kannst.
Am Wochenende nehme ich mir etwas mehr Zeit und schaue mir die Änderungen nochmal im Detail an.

[mikele]

Hallo,

Leider funktioniert die Berechnung der Urlaubstage (D15-D29) nicht mehr.

Sollte nun wieder richtig sein. Ich nutze dazu die Hilfstabelle für Lohn, d. h. auch für Gehaltsempfänger müssen die täglichen Arbeitszeiten eingetragen sein. Damit werden Feiertage, Wochentage mit 0 Stunden, Zeiten ohne Lohn/Gehalt nicht als Urlaubstage gezählt.

Die Angabe Eintritt in B10 steht für den Eintritt im Unternehmen...

Damit mussten die Formeln in AX2:BU32 verändert werden (Beginn der Zahlung steht in N3, i. d. R. also der 1.1.).

Und mit Eingabe des Gehalts (D4) werden die Arbeitsstunden (J19-J30) zerschossen.

Da wird es nun richtig spannend. Was soll für einen Gehaltsempfänger hier als Arbeitstage bzw. Arbeitsstunden gezählt werden? Gehalt bekommt er auch an Wochenenden bzw. Feiertagen (in der Hilfstabelle erscheinen diese als fiktive Arbeitstage). Eine Angabe von Arbeitsstunden an diesen Tagen ist aber wiederum unsinnig. Was soll also gezählt werden? Intuitiv würde ich sagen, es müssten die Zahlen eines Lohnempfängers eingetragen werden (sowohl Tage als auch Stunden). Überstunden dürfte es in dem Fall nicht geben (wie sollte das gerechnet werden), es sei denn, bei Gehaltsempfängern würde zusätzlich noch ein Stundenlohn angegeben werden, der dann zur Berechnung herangezogen wird ...
Es gibt also noch genügend Fälle, die nicht erfasst sind.

[Rafter]

Hallo mikele,
wunderbar. Das sollte passen. Ich hatte die zweite Hilfstabelle für die Stundenlohnempfänger (BY2-CV32) angelegt, weil die Berechnung der Monatszahlungen für Stundenlohnempfänger auf Basis der Stunden in der Hilfstabelle erfolgt.
In der Hilfstabelle für Gehaltsempfänger (AX2-BU32) werden die Stunden an Feiertagen richtigerweise auf 0 gesetzt, ohne das daraus eine Kürzung der Monatszahlung erfolgt. Stundenlohnempfänger erhalten für Feiertage, wenn sie denn auf einen regulären Arbeitstag fallen, Feiertagslohn (in gleicher Höhe wie den normalen Stundenlohn) für die ausgefallenen Stunden, daher werden in der Hilfstabelle für Stundenlohnempfänger die Stunden an diesen Feiertagen nicht auf 0 gesetzt. Evtl. Zuschläge zum Feiertagslohn oder Nachtzuschläge werden über die Zuschlagsspalte hinzugerechnet.
Die Berechnung des Urlaubs (D15-D29) wurde bei Gehalts-/ und Stundenlohnempfängern auf Basis der Hilfstabelle für Gehaltsempfänger (AX2-BU32) berechnet, damit Stundenlohnempfängern bei Urlaub an Feiertagen, wenn der Feiertag auf einen regulären Arbeitstag fällt, kein Urlaubstag für den Feiertag angerechnet wird. Die Arbeitszeiten werden bei Gehaltsempfängern und Stundenlohnempfängern mit festen Arbeitszeiten immer eingetragen.

Damit mussten die Formeln in AX2:BU32 verändert werden (Beginn der Zahlung steht in N3, i. d. R. also der 1.1.).

Das ist okay. Liegt der Eintritt im selben Jahr, trage ich im ersten Startzeitpunkt (B4 Gehalts-/, B6 Stundenlohnempfänger) eben auch das Eintrittsdatum ein. Das wird dann nach N3 übertragen. Wenn es aber ohne weiteres machbar ist, kannst Du es auch gerne in die Formeln AX2:BU32 einbauen, dass der erste Startzeitpunkt gleichzeitig das Eintrittsdatum darstellt, wenn im selben Jahr.

Da wird es nun richtig spannend. Was soll für einen Gehaltsempfänger hier als Arbeitstage bzw. Arbeitsstunden gezählt werden? Gehalt bekommt er auch an Wochenenden bzw. Feiertagen (in der Hilfstabelle erscheinen diese als fiktive Arbeitstage). Eine Angabe von Arbeitsstunden an diesen Tagen ist aber wiederum unsinnig. Was soll also gezählt werden?

Bei Gehaltsempfängern sollen die tatsächlich anwesenden Tage/-stunden gezählt werden, Urlaubstage gelten als Anwesenheitstag, Feiertage jedoch nicht und unbezahlte Fehlzeiten sollten auch nicht mitgezählt werden. Wie in der vorigen Version der Tabelle. Ich brauche die Anzahl der Arbeitstage zur Berechnung der Entgeltfortzahlung im Krankheitsfall. Das Personalstammblatt wird vorwiegend dafür genutzt. Der Übersichtlichkeit halber habe ich den unteren Teil des Personalstammblatts in der die Ausfallzeiten eingetragen und die daraus berechnete Entgeltfortzahlung dargestellt wird, herausgeschnitten. Die Angabe der Arbeitsstunden bei Gehaltsempfängern ist zwar nicht zwingend notwendig. Ich hätte aber schon gerne das die Stunden ebenso gezählt werden. Wenn das jedoch zu aufwendig ist, reicht es auch aus, dass die Arbeitstage gezählt werden. Bei Stundenlohnempfängern müssen die Arbeitsstunden gezählt werden, da die Erstattung nach Ausfallstunden erfolgt, bei Gehaltsempfänger je nach Berechnungsmethode nach Arbeitstagen, Kalendertagen oder dreißigstel-Methode (für jeden Monat werden dabei 30 Tage angesetzt).

Intuitiv würde ich sagen, es müssten die Zahlen eines Lohnempfängers eingetragen werden (sowohl Tage als auch Stunden). Überstunden dürfte es in dem Fall nicht geben (wie sollte das gerechnet werden), es sei denn, bei Gehaltsempfängern würde zusätzlich noch ein Stundenlohn angegeben werden, der dann zur Berechnung herangezogen wird ...

Die Arbeitszeiten werden auch bei Gehaltsempfängern eingetragen.
Korrekt. Bei Gehaltsempfängern wird der durchschnittliche Stundenlohn der letzten 3 Monate in D6, bei Gehaltsveränderung in D7 eingetragen. Mit dem Unterschied, dass dabei kein Startzeitpunkt B6 und B7 eingetragen wird.
Dieser wird nur zur Berechnung der Zuschläge/Überstunden herangezogen. Den durchschnittlichen Stundenlohn berechne ich selbst und trage diesen entsprechend in D6 und D7 ein. Wenn das aber ein Problem darstellt, dass der Stundenlohn bei Gehaltsempfängern in D6 und D7 eingetragen wird, weil andere Formeln dadurch beeinflusst werden, kann ich den durchschnittlichen Stundenlohn auch in einer anderen Zelle eintragen.

Viele Grüße
Rafter

[Rafter]

Die Berechnung der Urlaubstage (D15-D29) funktioniert leider nur bei Gehaltsempfängern.
Für Stundenlohnempfänger mit festen Arbeitszeiten werden immer 0 Urlaubstage ausgegeben.

Viele Grüße
Rafter

[mikele]

Hallo,
die Berechnung stimmte überhaupt nicht, da die Berechnung der Arbeitsstunden in der Lohnhilfstabelle falsch war. Die Formel habe ich korrigiert und etwas lesbarer/verständlicher gemacht. Wichtig ist ja, dass du sie verstehst.
O3 musst auch koorigiert werden. Dort muss (für die Indexfunktion in der Hilfstabelle) immer ein Datum stehen. wenn es nun kein 2. Datum gibt, wird das Ende angenommen (=P3).
Noch eine Erläuterung zur Berechnung der Urlaubstage (abgeändert und vereinfacht, da in der Hilfstabelle nun der Studenlohn nur noch an Arbeitstagen angegeben wird):

Code: Alles auswählen

=WENN(B15="";"";SUMMENPRODUKT($R$2:$AO$32>=A15;$R$2:$AO$32<=B15;$BY$2:$CV$32>0)/2)

Der Teil

Code: Alles auswählen

$R$2:$AO$32>=A15;$R$2:$AO$32<=B15

prüft, ob der Kalendertag aus R2:AO32 innerhalb des Urlaubs liegt (liefert 1, wenn innerhalb, ansonsten 0)

Code: Alles auswählen

$BY$2:$CV$32>0

prüft, ob der Kalendertag ein Arbeitstag wäre (also Arbeitsstunde zu leisten wären)
Der Bereich R2:AO32 enthält jeden Kalendertag und in der Hilfstabelle BY2:CV32 stehen jeweils Arbeitsstunden und Lohn für den einen Tag. Dadurch würden immer doppelt so viele Urlaubstage berechnet werden. Daher noch geteilt durch 2.
Ich habe noch eine dunkelgrüne bedingte Formatierung für die Feiertage eingefügt.

[Rafter]

Hallo mikele,
danke für die Erläuterung. Aber leider kommt ich damit nicht weiter. Was ich nicht erwähnt habe: Seit ich Anfang des Jahres den Arbeitgeber gewechselt habe, bin ich dazu verdammt mit Excel zu arbeiten. Da ich auf dem Firmenrechner kein LibreOffice installieren darf.
Deine zuletzt erstellten Formeln für die Berechnung des Urlaubs und die Formeln in der Hilfstabelle Stundenlöhner funktionieren in Excel merkwürdigerweise nicht. Bei den Urlaubstagen erhalte ich immer als Ergebnis 0 und in der Hilfstabelle Lohn werden die täglichen Arbeitsstunden der unbezahlten Fehlzeiten (kind krank mit Krankengeld, krank mit Krankengeld und Elternzeit) in den angegeben Zeiträumen nicht auf 0 gesetzt. Die Berechnung der Entgeltfortzahlung, die ich zuvor wegen der Übersichtlichkeit ausgeschnitten habe, wird mit den Änderungen auch zerschossen.
Andererseits werden in der Hilfstabelle Lohn die Arbeitsstunden an Feiertagen, wenn sie auf einen regulären Arbeitstag fallen, z.B. Karfreitag und Ostermontag, auf 0 gesetzt, was fasch ist, denn die ausgefallenen Stunden an diesen Tagen werden ja auch bezahlt.
Ich brauche also die Stunden in beiden Hilfstabellen. Bei Gehaltsempfängern werden die Stunden an Feiertagen nicht mitgerechnet, bei den Stundenlohnempfängern aber schon, daher können nicht beide in einer Tabelle abgebildet werden.
Gibt es nicht eine andere Möglichkeit die Tage ohne Bezüge herauszurechnen ohne die Arbeitsstunden in der Hilfstabelle Gehalt zu überschreiben? Was ist z.B. mit dem ISTFEHLER(VERGLEICH(U3;Feiertage!$A$8:$A$19;0))*... der in der Hilfstabelle Gehalt die Feiertage ausschließt? Ich habe die Funktion in der Hilfstabelle Lohn auf die Zellen AO3-AQ3 angewendet, und es funktioniert. Allerdings bekomme ich die weiteren Zellen AO4-AQ4 und AO32-AQ32 und alle dazwischenliegenden nicht mit eingeschlossen. Zum anderen müsste selbige Funktion in der Hilfstabelle Gehalt neben dem Ausschluss der Feiertage auch die unbezahlten Fehlzeiten mit einschließen.
Ich hänge mal die gesamte Tabelle im .xlsx Format an und hoffe dadurch hier nicht gesteinigt zu werden.
Darin sind 4 Musterfälle: Ein Lohnempfänger ohne Lohnerhöhung, ein Lohnempfänger mit Lohnerhöhung, ein Gehaltsempfänger ohne Gehaltserhöhung und ein Gehaltsempfänger mit Gehaltserhöhung.
Bis auf die Kürzung der unbezahlten Fehlzeiten bei Lohn- und Gehaltsempfängern und
die Kürzung bei Eintritt/Austritt bzw. Berücksichtigung einer Lohnerhöhung im Teilmonat bei Lohnempfängern funktionieren die Formeln alle.
Sorry, dass ich mit der Tabelle so viel nerve.