Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1716to1720
1716to1720
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Lohnzuschläge ermitteln nach Uhrzeiten und Tag

Forumthread: Lohnzuschläge ermitteln nach Uhrzeiten und Tag

Lohnzuschläge ermitteln nach Uhrzeiten und Tag
08.10.2019 17:16:27
Matthias
Liebe Excel-Profis,
ich habe keine Idee, wie ich das lösen kann. Abhängig von Arbetsbeginn und Arbeitsende will ich aus einer Tabelle die Zuschläge ermitteln. Zur Berechnung will den Arbeitsbeginn, das Arbeitsende und die Pausendauer heranziehen. Das soll dann aus der Tabelle automatisch rechnen. Ein Zuschlag von 1 bedeutet hier dann Normalstunde.
Damit ich sicherer Rechnen kann, verwende ich Datum&Zeit für die Zellen.
Ich habe die Beispieldatei beigefügt: https://www.herber.de/bbs/user/132415.xlsx
Sie sieht so aus:
von_____bis_____total Pausenzeit
Sa 17:30 So 02:30 09:00 00:45
Konditionen Zuschläge nach Arbeitsstunden
Beginn___Ende____ 0:00 3:00 7:00 8:00 09:00
Mo 00:00 Mo 05:59 1,25 1,25 1,25 1,25 1,50
Mo 06:00 Mo 21:59 1,00 1,00 1,00 1,25 1,50
Mo 22:00 Di 05:59 1,25 1,25 1,25 1,25 1,50
Di 06:00 Di 21:59 1,00 1,00 1,00 1,25 1,50
Di 22:00 Mi 05:59 1,25 1,25 1,25 1,25 1,50
Mi 06:00 Mi 21:59 1,00 1,00 1,00 1,25 1,50
Mi 22:00 Do 05:59 1,25 1,25 1,25 1,25 1,50
Do 06:00 Do 21:59 1,00 1,00 1,00 1,25 1,50
Do 22:00 Fr 05:59 1,25 1,25 1,25 1,25 1,50
Fr 06:00 Fr 21:59 1,00 1,00 1,00 1,25 1,50
Fr 22:00 Sa 23:59 1,25 1,40 1,50 1,50 1,50
So 00:00 Mo 00:00 1,50 1,50 1,50 1,50 1,50
Mo 00:00 Mo 06:00 1,25 1,25 1,25 1,25 1,50
Die Herausforderung: die Arbeitszeiten können die Bereiche überlappen...
Ich habe es schon mit Verweis probiert oder verschiedenen wenn-Bedingungen, aber bisher nichts gelöst. Ginge es mit einer Matrix-Funktion und wen ja, wie?
Ich freue mich auf Hinweise!!!
Vielen Dank schon einmal, LG, Papavonmaxi
Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
08.10.2019 17:38:25
Daniel
Hi
mal ne Idee:
1. erstelle die Liste für jede Stunde jeden Tag der Woche von Montag bis einschließlich nächster Montag
(für jede Stunde eine Zeile)
2. verwende als Datum in der ersten Spalte die die Kombination aus 1 bis 8 + Uhrzeit (Wochentag als Zahl + Uhrzeit
die 8 dann für den Montag in der Folgewoche
3. schreibe für jede Stunde einzeln den Zuschlag auf
4. wandle Start- und Endtermin nach dem Schema um: =Wochentag(Datum)+Uhrzeit
5. ermittle mit der Vergleichs-Funktion die Zeilennummer für Start- und Endtermin in der Liste
6. bilde die Summe zwischen diesen beiden Zeilennummern in der jeweiligen Spalte
ist zwar ein bisschen "brute-Force", aber per Formel allein das Thema "übergreifende Bereiche" abzudecken ist auch sehr aufwendig.
wenn du eine Arbeitszeit von Sonntag auf Montag hast, musst du beim Montag zum Wochentag (7) noch 1 hinzuaddieren.
Gruß Daniel
Anzeige
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
08.10.2019 17:43:35
onur
Bist du dir sicher?
Normalerweise heben sich Zuschläge nicht gegenseitig auf - soll heissen:
25% Nachtzuschlag 22:00-6:00
25% Samstagszuschlag 00:00-23:59
50% Sonntagszuschlag 00:00-23:59
25% Überstundenzuschlag für die Stunden über 8,00 Stunden.
Wenn Jemand von Samstag 22:00 bis Sonntag 7:00 arbeitet, bekommt er für die ersten beiden Stunden 50% (Samstags- und Nachtzuschlag), für die Zeit von 00:00 bis 6:00 75% Zuschlag (Sonntags- und Nachtzuschlag)
und für die letzte Stunde 75 % (Sonntags- und Überstundenzuschlag) Zuschläge.
Anzeige
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
08.10.2019 17:47:33
Matthias
Hallo Onur,
ja, ich bin sicher, die Zuschläge sind so in einem Haustarif mit der IG Metall geregelt. Feiertage habe ich hier extra mal rausgenommen.
LG, Matthias
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
08.10.2019 17:55:48
onur
Aber im Tarifvertrag steht bestimmt nicht so eine Tabelle wie Deine, sondern eindeutige Regeln:
Sonntagszuschlag xy% von: bis:
Nachtzuschlag xy% von: bis: usw usw.
DIESE Regeln brauchen wir und nicht so eine Tabelle für 1 Stunde, 3 Stunden usw.
Anzeige
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
09.10.2019 10:06:59
Matthias
Hier die Regeln als Text:
Bei Mehrarbeit fallen folgende Zuschläge auf die vereinbarten Basissätze an:
Montag bis Freitag ab der 2.Überstunde
o für die erste Überstunde (am Tag die 8. Stunde) 25%
o für die weiteren Überstunden 50%
Nachtzuschlag von 22:00 bis 06:00 Uhr 25%
Samstags für die ersten beiden Stunden 25%
Samstags ab der 3. Stunde 40%
Samstags ab der 7. Stunde 50%
Sonntag 50%
Feiertag 150%
Anzeige
AW: Lohnzuschläge ermitteln nach Uhrzeiten und Tag
09.10.2019 10:29:06
Daniel
Hi
ich schätze mal, das wird mit reinen Excelformeln schwierig, weil du im Prinzip jede einzelne Stunde prüfen musst und dabei eine ganze Reihe von Bedingungen prüfen musst, welche Teilweise auch noch aufeinander aufbauen.
hier müsstet du dir wahrscheinlich mit VBA was programmieren.
Gruß Daniel
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Lohnzuschläge ermitteln nach Uhrzeiten und Tag

Schritt-für-Schritt-Anleitung

  1. Datenstruktur erstellen: Lege eine Excel-Tabelle an, die die gewünschten Spalten für von, bis, Pausenzeit und die Konditionen enthält. Die Konditionen sollten die verschiedenen Zuschläge enthalten, z.B. für Nacht, Samstag und Sonntag.

  2. Zuschläge definieren: Erstelle eine separate Tabelle für die Zuschläge. Diese Tabelle könnte wie folgt aussehen:

    | Beginn     | Ende       | 0:00 | 3:00 | 7:00 | 8:00 | 9:00 |
|------------|------------|------|------|------|------|------|
| Mo 00:00  | Mo 05:59   | 1,25 | 1,25 | 1,25 | 1,25 | 1,50 |
| Mo 06:00  | Mo 21:59   | 1,00 | 1,00 | 1,00 | 1,25 | 1,50 |
| ...        | ...        | ...  | ...  | ...  | ...  | ...  |

  3. Formel zur Berechnung: Nutze die Excel-Funktion WVERWEIS oder SVERWEIS, um die Zuschläge basierend auf den Arbeitszeiten zu ermitteln. Beispiel:

    =WVERWEIS(DATUM; TabelleZuschläge; Spalte; FALSCH)

  4. Bedingungen erfassen: Berücksichtige die Bedingungen für Zuschläge, z.B. ab welcher Uhrzeit Nachtschichtzuschlag anfällt. Für die IG Metall gelten die folgenden Zuschläge:

    • Nachtzuschlag von 22:00 bis 06:00 Uhr: 25%
    • Samstagszuschlag für die ersten beiden Stunden: 25%
    • Sonntagszuschlag: 50%

  5. Überstunden auswerten: Berechne die Überstunden anhand der festgelegten Werte in der IG Metall Überstundenzuschlag Tabelle. Beispiel für eine Überstunde:

    =WENN(Arbeitsstunden > 8; (Arbeitsstunden - 8) * 1,5; 0)

  6. Ergebnisse zusammenfassen: Addiere alle Zuschläge und Überstunden, um die Gesamtsumme zu ermitteln.

Häufige Fehler und Lösungen

  • Fehler bei den Zeitangaben: Stelle sicher, dass die Zeitangaben im korrekten Format (hh:mm) vorliegen. Andernfalls können Berechnungen fehlerhaft sein.

  • Zuschläge nicht korrekt berechnet: Überprüfe, ob die Bedingungen in den Formeln korrekt sind. Nutze die WENN-Funktion, um verschiedene Szenarien abzudecken.

  • Falsche Zuordnung von Zuschlägen: Achte darauf, dass die Übergänge zwischen den Tagen (z.B. von Samstag auf Sonntag) korrekt berücksichtigt werden.

Alternative Methoden

  • VBA zur Automatisierung: Wenn Du regelmäßig mit Zuschlägen arbeiten musst, kann es sinnvoll sein, ein VBA-Skript zu erstellen, das die Berechnungen automatisiert und verschiedene Szenarien berücksichtigt.

  • Pivot-Tabellen: Nutze Pivot-Tabellen, um die Daten zu analysieren und die Zuschläge schnell zu berechnen.

Praktische Beispiele

  • Beispiel 1: Ein Mitarbeiter beginnt am Samstag um 22:00 Uhr und endet am Sonntag um 07:00 Uhr. Berechne die Zuschläge:

    • Nachtzuschlag: 25% für 22:00 - 00:00
    • Samstagszuschlag: 25% für 22:00 - 00:00
    • Sonntagszuschlag: 50% für 00:00 - 07:00

  • Beispiel 2: Ein Mitarbeiter hat am Freitag von 17:00 bis 23:00 Uhr gearbeitet. Die Berechnung für den Freitag würde wie folgt aussehen:

    • Normalstunden: 5 Stunden
    • Überstunden ab 8 Stunden: 0, da nur 5 Stunden gearbeitet wurden.

Tipps für Profis

  • Nutzung von Excel-Formeln: Vertraue auf die SUMMEWENN und SUMMEWENNS Funktionen, um die Zuschläge effizient zu summieren.

  • Bedingte Formatierungen: Setze bedingte Formatierungen ein, um die verschiedenen Zuschläge visuell hervorzuheben.

  • Zugriff auf IG Metall Tabellen: Halte die aktuellen IG Metall Zuschläge Tabellen griffbereit, um sicherzustellen, dass Du die korrekten Sätze verwendest.

FAQ: Häufige Fragen

1. Ab welcher Uhrzeit gilt der Nachtschichtzuschlag?
Der Nachtschichtzuschlag gilt von 22:00 bis 06:00 Uhr.

2. Wie berechne ich die Zuschläge in Excel?
Verwende die genannten Excel-Formeln und achte darauf, die richtigen Bedingungen in Deine Berechnungen einzufügen.

3. Wo finde ich die IG Metall Zuschläge Tabelle?
Die IG Metall Zuschläge Tabelle findest Du auf der offiziellen Website oder in den Tarifverträgen.

4. Was ist der Unterschied zwischen Überstunden- und Nachtschichtzuschlag?
Überstunden werden für Stunden berechnet, die über die vertraglich festgelegte Arbeitszeit hinausgehen, während Nachtschichtzuschläge für Arbeiten in der Nacht gelten.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige