[GELÖST] Auswertung Jubiläen - bedingte Formatierung und zählenwenns Funktion

[Rafter]

Hallo,
meine Tabelle gibt anhand eines Eintrittsdatums anstehende Jubiläen in einem bestimmten Zeitraum wieder.
Spalte C enthält die Eintrittsdaten
Spalte F gibt an wann das 10. Jubiläum ansteht (Eintrittsdatum + 10 Jahre)
Spalte G das 25. Jubiläum
Spalte H das 40. Jubiläum
Spalte I das 50. Jubiläum

Zelle L5 und L6 geben den auszuwertenden Zeitraum an L5=Anfang, L6=Ende.

In Zelle M9 wird über die ZÄHLENWENNS Funktion die Anzahl der 10-jährigen Jubilare im auszuwertenden Zeitraum ausgegeben.
In Zelle M10 die Anzahl der 25-jährigen Jubilare
In Zelle M11 die Anzahl der 40-jährigen Jubilare
In Zelle M12 die Anzahl der 50-jährigen Jubilare

Über die bedingte Formatierung werden die Jubilare im auszuwertenden Zeitraum (L5:L6) in den Spalten F, G, H und I jeweils farblich markiert.

Das funktioniert auch alles wie es soll!

Nun kommt aber erschwerend hinzu, dass Mitarbeiter die in den Ruhestand gehen (Austrittsgrund 54 und 34!!) bereits 5 Jahre früher, vorgezogene Jubiläumszuwendungen erhalten.
Geht z.B. ein Mitarbeiter nach 20 Jahren Betriebszugehörigkeit im Auswertungszeitraum (L5:L6) in den Ruhestand, soll er wie die Mitarbeiter die die 25 Jahre voll haben, behandelt werden: Also in Zelle M10 mit eingerechnet und über die bedingte Formatierung in Spalte G entsprechend markiert werden.
Bei Ruhestand nach 35 Jahren gilt das gleiche wie für Mitarbeiter die die 40 Jahre voll haben: Also in Zelle M11 mit eingerechnet und über die bedingte Formatierung in Spalte H markiert werden.
Bei Ruhestand nach 45 Jahren gilt das gleiche wie für Mitarbeiter die die 50 Jahre voll haben: Also in Zelle M12 mit eingerechnet und über die bedingte Formatierung in Spalte I markiert werden.

Zu beachten ist das die vorgezogenen Jubiläumszuwendungen nur bei Austrittsgrund 54 und 34 gezahlt werden.
Bei allen anderen Austrittsgründen gibt es keine vorgezogenen Jubiläumszuwendungen.

Leider stoße ich mit meinen Tabellenkalkulationskenntnissen hier an meine Grenzen und bin auf eure Hilfe angewiesen.

Viele Grüße
Rafter

[echo]

Hallo @Rafter

ich habe deine Formel aus F2 mal etwas erweitert und so umgestellt das sie nach rechts und unten kopierbar wird.
Es wird jetzt das tatsächliche Datum zum Jubiläum berechnet.

Zur Kontrolle habe ich die alten Formeln so gelassen und mit der erweiterten Formel in F16 begonnen.
Wenn es so passt kopiere die Formel aus der Bearbeitungsleiste nach F2
Sie ist dann wie gesagt kopierbar nach rechts und unten

Gruß Holger

Jubiläen.ods

(34.27 KiB) 185-mal heruntergeladen

Nachtrag:
Es hat sich leider ein Fehler eingeschlichen:
Falsch:
=WENN($Tabelle1.$C2="";"";WENN(ODER($E2=54;$E2=34);EDATUM($C2;F$1*12-60);EDATUM(C$2;F$1*12)))
Richtig:
=WENN($Tabelle1.$C2="";"";WENN(ODER($E2=54;$E2=34);EDATUM($C2;F$1*12-60);EDATUM($C2;F$1*12)))

[Rafter]

Hallo Holger,

vielen Dank für deine pauschal anwendbare Formel.

Es wird jetzt das tatsächliche Datum zum Jubiläum berechnet.
Das war doch in meiner Tabelle auch der Fall, oder irre ich mich?

Eines wird jedoch in Deiner Formel nicht berücksichtigt, aber vielleicht habe ich mich in meinem Anliegen auch nicht klar genug ausgedrückt:
Und zwar bekommt der ausscheidende Rentner die vorgezogene Jubiläumszuwendung selbstverständlich zum Zeitpunkt seines Ausscheidens (Austrittsdatum). Bloß kann dies beim Austritt mit den zwei genannten Austrittsgründen max. 5 Jahre vor Erreichen des eigentlichen Jubiläums sein.
Zwei Beispiele:
1. Eintritt=01.08.1975 ; reguläres 50-jähriges Jubil.=01.08.2025 ; Ruhestand=31.12.2024=vorgezogene Jubiläumszuwendung
2.Eintritt=01.04.1989 ; reguläres 40-jähriges Jubil.=01.04.2029 ; Ruhestand=31.10.2024=vorgezogene Jubiläumszuwendung

Daher müsste die Formel angepasst und um das Austrittsdatum erweitert werden.

Die jetzige Formel rechnet bei Eingabe eines der beiden Austrittsgründe einfach nur 5 Jahre vom eigentlichen Jubiläum zurück und berücksichtigt das Austrittsdatum nicht.

Viele Grüße
Rafter

[Mondblatt24]

Hallo,
das folgende Beispiel wird mit LibreOffice Calc ab Version 24.8. funktionieren (es sei denn, man lässt sich für Spalte P eine andere Formel einfallen), außerdem gibt es keine Gewähr darauf, dass es mit Excel funktioniert.

Jubiläen2.ods

(103.17 KiB) 250-mal heruntergeladen

Gruß
Peter

[Rafter]

Hallo Peter,

danke Dir. Spalte P sieht ja sehr kompliziert aus. Leider kann ich es mit meinen Kenntnissen nicht nachvollziehen.

Aber lt. Deiner Formel hätte z.B. der oberste Mitarbeiter (Zeile 2) sein 40. jähriges Jubiläum am 01.08.2026, trotz Rentenbeginn (Austritt) zum 31.03.2025. Faktisch ist das zwar richtig, aber wie oben beschrieben stehe dem Mitarbeiter zum Austrittsdatum eine (anteilige) vorgezogene Jubiläumszuwendung zum 31.03.2025 zu.
Und das ist es was ich aus dieser Tabelle herauslesen muss. Zum einen wann die Jubiläen im Auswertungszeitraum (im Beispiel 01.01.2025 - 31.12.2025) anfallen und zum anderen wenn Mitarbeiter im Auswertungszeitraum austreten, und ein Anrecht auf (anteilige ) vorgezogene Jubiläumszuwendung besteht (nur bei den beiden beschriebenen Austrittsgründen, 34 und 54): was bis max. 5 Jahre vor Erreichen des tatsächlichen Jubiläums der Fall ist.

Ein Mitarbeiter der z.B. nach 35 Jahren in den Ruhestand geht, bekommt also anteilig die Jubiläumszuwendung die im zum 40. jährigen Jubiläum zugestanden hätte, nach 34 Jahren Betriebszugehörigkeit aber gar nichts.
Dabei geht es nicht um die anteilige Berechnung, sondern nur um die visuelle Darstellung (Markierung über die bedingte Formatierung) das diesem Mitarbeiter zu dem Zeitpunkt des Austritts (sofern im Auswertungszeitraum) eine Jubiläumszuwendung zusteht.
Und diesen Aspekt würde ich gerne in der von Holger vorgeschlagenen Formel erweitern.

Vorgezogene Jubiläumszuwendungen gibt es nach frühestens
- 20 Jahren (25 jähriges Jubiläum)
- 35 Jahren (40 jähriges Jubiläum)
- 45 Jahren (50 jähriges Jubiläum)

Ich hoffe ich habe den Sachverhalt nun einigermaßen verständlich dargestellt.

Ich danke allen die sich mit Thema beschäftigen und mir hoffentlich bei der Problemlösung helfen.

Viele Grüße
Rafter

[echo]

Hallo
ich habe auch noch mal die Formel von mir etwas erweitert
- daneben zur Prüfung die Differenz zu deiner Liste berechnet.

Es werden jetzt entweder max. 60 Monate abgezogen oder das Renteneintrittsdatum ausgegeben.

[Rafter]

Hallo,
vielen Dank echo! Das ist nahezu perfekt. Es hapert nur noch an einer Kleinigkeit:

Es werden jetzt entweder max. 60 Monate abgezogen oder das Renteneintrittsdatum ausgegeben.

Nicht oder, und! Wenn Renteneintrittsdatum (Austritt mit Grund 34, 54) vorhanden, dann (nur dann!) Betrachtung der letzten 60 Monate vom tatsächlichen Zeitpunkt des Jubiläums (nur beim 25./40. und 50. Jubiläum, nicht beim 10. J.), ob Renteneintrittsdatum in den Zeitraum der 60 Monate vor dem jeweiligen Jubiläum fällt. Wenn ja, dann ist das Renteneintrittsdatum gleich das vorgezogenes Jubiläum.

Die 60 Monate dürfen nur in Verbindung mit dem Austrittsdatum und den entsprechenden Austrittsgründen (34, 54) abgezogen werden. Ohne Austrittsdatum oder mit Austrittsdatum und keinen der beiden Austrittsgründe muss das tatsächliche Jubiläumsdatum ausgegeben werden. Mit dem Unterschied, dass bei mit Austrittsdatum und keinen der beiden Austrittsgründe das tatsächliche Jubiläumsdatum nicht farblich markiert werden dürfte. Hier gibt es aber wohl einen KONFLIKT: Tritt der Arbeitnehmer vor dem Erreichen des regulären Jubiläums oder ohne Anspruch auf vorgezogene Jubiläumszuwendung aus, fällt das Jubiläum ja auch nicht an. Das lässt sich aber vermutlich formeltechnisch nicht abbilden und selbst wenn doch müsste die ZÄHLENWENNS Formel in Zelle M9-M12 ein mögliches Austrittsdatum berücksichtigen. Das ist glaube zu viel verlangt.

Aber es wäre großartig wenn folgender Fehler behoben werden könnte:
Lt. der jetzigen Formel hätte ein am 01.08.1990 eingetretener Arbeitnehmer regulär am 01.08.2030 das 40. Jubil. = RICHTIG
Mit Eintritt des Ruhestand zum 30.09.2025 entsteht ein vorgezogenes Jubiläum zum 30.09.2025 = AUCH RICHTIG
Bei Eintritt des Ruhestand zum 30.06.2025 wird jedoch als vorgezogenes Jubiläum der 01.08.2025 ausgegeben = FALSCH.

Denn zum 01.08.2025 wären zwar die 35 Jahre (40 Jahre - 60 Monate) erreicht, aber der Arbeitnehmer geht ja vor dem 01.08.2025 in den Ruhestand, somit außerhalb der 60 Monatsbetrachtung vom tatsächlichen Jubiläumsdatum. Demnach besteht kein Anspruch auf eine vorgezogene Jubiläumszuwendung (und natürlich auch kein Anspruch auf das tatsächliche Jubiläum zum 01.08.2030).

Kann man die Formel irgendwie um diesen Umstand erweitern?

Danach bin ich auch restlos zufrieden und wir können das Thema als gelöst betrachten.

Viele Grüße
Rafter

[echo]

Hallo Rafter
Sorry, war gerade noch anders beschäftigt.

Die Formel für dein Anliegen, mutiert bei so vielen Bedingungen zu einer "Mammut-"Formel die man (ich) kaum noch zu überblicken vermag.
Ich bin sowieso ehr ein Fan davon Zwischenschritte in Hilfsspalten zu berechnen,
Man kann sie später ja ausblenden.
Das habe ich gerade auf einem neuen Tabellenblatt gemacht.

Gruß Holger

Jubiläen 3.ods

(47.62 KiB) 210-mal heruntergeladen

[Rafter]

Hallo Holger,

grandios! Besten Dank!
Tatsächlich ist der Umfang der Bedingungen etwas aus dem Ruder gelaufen.
Umso erstaunlicher wie ihr das immer hinbekommt.

Ich brauche auch noch eine Weile bis ich die Funktionen geprüft habe. Es sieht aber auf den ersten Blick schon sehr sehr gut aus.
Lediglich die Formeln in Zelle M10-M13 muss ich noch umstellen, um einen genaueren Auswertungszeitraum auslesen zu können. Deine Formeln greifen auf das Gesamtjahr (K4) zu. Ich muss aber auch mal das Quartal oder einzelne Monate auswerten können, daher hatte ich die Zellen L6-L7 angelegt. Aber das schaffe ich hoffentlich auch selbst.

Herzlichen Dank euch allen für die tatkräftige Unterstützung!

Sobald ich die Berechnungen geprüft habe füge ich eine GELÖST Klammer hinzu.

Viele Grüße
Rafter