Office: (Office 2013) Berechnung Weihnachtsgeld nach Betriebszugehörigkeit in Jahren

Hallo liebe Community,

ich bin erst seit kurzem registrierter User hier und hatte vorher schon immer hier mal rein geschaut um das ein oder andere Excel-Problem lösen zu können. Nun steh ich bei meinem Problem ein kleinwenig auf dem Schlauch. Vielleicht könnt ihr mir ja bissl unter die Arme greifen. Und zwar....

Berechnung Weihnachtsgeld (WG) der MA nach Betriebszugehörigkeit.

in den ersten drei Jahren ist das WG folgendermaßen WG gestaffelt:
1. Jahr 110,- €;
2. Jahr 250,- €;
3. Jahr 400,- € ;
ab dem 4. Jahr kommen für jedes weitere Jahr der Betriebszugehörigkeit 50,- € dazu.

Also, ein MA der z.B. 4 Jahre im Betrieb ist bekommt 450,- €, 5 Jahre 500,- €, 6 Jahre 550,- € WG etc... Stichtag ist immer der 30.11. eines Jahres

Gegeben ist das Eintrittsdatum der MA in die Firma und Austrittsdatum
der MA die vor dem 31.03.2015 ausscheiden - diese bekommen kein Weihnachtsgeld mehr. Weihnachtgeld bekommen die Mitarbeiter erst wenn sie bereits 12 Monate dem Betrieb zugehörig sind. Stichtag hierfür ist der 30.11.2015.

Ich hab das jetzt mit einer Wenn/Dann-Funktion versucht... bei den ersten 4 Jahren etc. kein Problem... doch wie gehe ich weiter vor? Wie mache ich eine Abfrage des Datums und binde sie in meine Formel ein...

Meine "Wenn"-Abfrage in Spalte M10 zur Ermittlung des Weihnachtsgeldes ist folgende:
=WENN($K10>=$I$4;ABRUNDEN((K10-$I$4)/12;0)*$H$5+400;WENN($K10>=$I$3;"250,00";WENN($K10>=$I$2;"100,00";WENN($K10

:)

 

Hallo Krauxi,

Jetzt fehlt nur noch die Excel-Mappe, die du bitte hochladen möchtest.

Die Mehrzahl von "Datum" ist übrigens "Daten", nicht "Datums"...

 

Moin
Mal ausser Konkurrenz. Daten moegen mich nicht. ;-))
Ist,fuer mich, eher zum ausprobieren. Also Formelexperten vorran. ;-))


Tabelle1

	A	B	C	D
1	Eintritt	Datum	Betr. Zugeh.	Weihnachtsgeld
2	01.05.2010	01.02.2013	2	250
3	01.05.2010	01.02.2015	4	450

verwendete Formeln
Zelle	Formel	Bereich	R1C1 für Add In
C2:C3	=DATEDIF(MAX(DATUM(JAHR(A2);MONAT(A2);TAG(A2));DATUM(JAHR(A2);11;30));MIN(DATUM( JAHR(B2);MONAT(B2);TAG(B2));DATUM(JAHR(B2);11;30));"Y")		=DATEDIF(MAX(DATE(YEAR(RC[-2]),MONTH(RC[-2]),DAY(RC[-2])),DATE(YEAR(RC[-2]),11,30)),MIN(DATE(YEAR(RC[-1]),MONTH(RC[-1]),DAY(RC[-1])),DATE(YEAR(RC[-1]),11,30)),"Y")
D2: D3	=VERWEIS(C2;{1.2.3};{110.250.400})+WENN(C2>3;(C2-3)*50;0)		=LOOKUP(RC[-1],{1,2,3},{110,250,400})+IF(RC[-1]>3,(RC[-1]-3)*50,0)

Zahlenformate
Zelle	Format	Wert
A2:A3	'TT.MM.JJJJ	40299
B2	'TT.MM.JJJJ	41306
B3	'TT.MM.JJJJ	42036
C2	'[=1]0 "Jahr";0 "Jahre"	2
C3	'[=1]0 "Jahr";0 "Jahre"	4
D1	'TT.MM.JJJJ	Weihnachtsgeld
D2	'#.##0,00 €	250
D3	'#.##0,00 €	450

Zellen mit Format Standard werden nicht dargestellt

Tools Tabelle Forum - Hajo's Excelseiten
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary

 

Hallo *Smilie ,

zuerst einmal meinen herzlichen Dank für euere Hilfe zu meinem Problem *Smilie *Smilie.

@ hary: Ihre beiden Formeln hab ich direkt schon mal mit einer kleinen Modifizierung zusätzlich in zwei Extra Spalten (O und P) eingebaut. Funktioniert klasse. Hier fehlt jetzt nur noch die Abfrage/Prüfung der beiden Stichtage (N2 und N3) die man individuell pflegen kann, damit der Ein- und Austritt aus der Firma gekennzeichnet warden kann (siehe Zeile 17 und 18).
Was ist der Wert "11" in Ihrer Formel? Die "30" sind die Kalendertage im Monat - oder?
"=DATEDIF(MAX(DATUM(JAHR(H10);MONAT(H10);TAG(H10));DATUM(JAHR(H10);11;30));......"
Wenn ich in Ihrer Formel am Ende das "Y" in "M" verändere bekomme ich die Monate der Betriebszugehörigkeit abgerundet - ich brauche hier aber entwedder die genaue Ausgabe nur in Monaten oder in Jahren und Monaten (z.B. 2 Jahre 6 Monate) der Betriebszugehörigkeit.

@ Hasso: Danke für die Berichtigung des "Datums" im Text ...ist mir beim schreiben des Posts gar net aufgefallen *frown.gif* *cool.gif*.

Eine Beispieldatei hab ich jetzt ebenso hier hochgeladen.

Vielen Dank schon mal für die weitere Hilfe.

Herzlichen Gruß aus dem weihnachtlichen Nürnberg
Krauxi

 

Moin
Hier wird geduzt.
Die 11 ist der 11'te Monat(November), die 30 ist der Tag.
Das ist dein Stichtag
Da du ja Monate hast, musst du durch 12 teilen.Ist nicht gross getestet.


Mitarbeiter

	O	P
10	60	500

verwendete Formeln
Zelle	Formel	Bereich	R1C1 für Add In
O10	=DATEDIF(MAX(DATUM(JAHR(H10);MONAT(H10);TAG(H10));DATUM(JAHR(H10);11;30));MIN(DA TUM( JAHR(I10);MONAT(I10);TAG(I10));DATUM(JAHR(I10);11;30));"m")		=DATEDIF(MAX(DATE(YEAR(RC[-7]),MONTH(RC[-7]),DAY(RC[-7])),DATE(YEAR(RC[-7]),11,30)),MIN(DATE( YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6])),DATE(YEAR(RC[-6]),11,30)),"m")
P10	=WENN($O10<12;"Kein WG";VERWEIS(O10/12;{1.2.3};{110.250.400})+WENN(O10/12>3;(O10/12-3)*50;0))		=IF(RC15=0,"Kein WG",LOOKUP(RC[-1]/12,{1,2,3},{110,250,400})+IF(RC[-1]/12>3,(RC[-1]/12-3)*50,0))

Tools Tabelle Forum - Hajo's Excelseiten
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

edit: evtl brauchst du bei O10/12 GANZZAHL(O10/12)
Kann erst Morgen wieder.
gruss hary

 

Moin Krauxi
So, wieder da.
Jetzt wird der Geldbetrag aus der Liste genommen. Formel runterziehen.


Mitarbeiter

	O	P
10	60	500

verwendete Formeln
Zelle	Formel	Bereich	R1C1 für Add In
O10	=WENNFEHLER(DATEDIF(MAX(DATUM(JAHR(H10);MONAT(H10);TAG(H10));DATUM(JAHR(H10);11; 30));MIN(DATUM( JAHR(I10);MONAT(I10);TAG(I10));DATUM(JAHR(I10);11;30));"m");0)		=IFERROR(DATEDIF(MAX(DATE(YEAR(RC[-7]),MONTH(RC[-7]),DAY(RC[-7])),DATE(YEAR(RC[-7]),11,30)),MIN(DATE( YEAR(RC[-6]),MONTH(RC[-6]),DAY(RC[-6])),DATE(YEAR(RC[-6]),11,30)),"m"),0)
P10	=WENNFEHLER(WENN(GANZZAHL(O10/12)>3;$H+(GANZZAHL(O10/12)-3)*$H;INDEX($H:$H;VERGLEICH(O10;$I:$I;1)));"kein W-Geld")		=IFERROR(IF(INT(RC[-1]/12)>3,R4C8+(INT(RC[-1]/12)-3)*R5C8,INDEX(R2C8:R4C8,MATCH(RC[-1],R2C9:R4C9,1))),"kein W-Geld")

Tools Tabelle Forum - Hajo's Excelseiten
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary

 

Hallo hary,

vielen Dank für deine fast perfekte Lösung und die Lehrstunde diesbezüglich *Smilie.

Ich habe deine Formel nun eingesetzt und habe hier nur noch das kleine Problem, dass die Monate der Betriebszugehörigkeit in Spalte "O" nicht richtig ausgegeben warden.
z.B. vom 01.02.2015 - 29.01.2016 sind 11 Monate und nicht 1 Monat bzw. vom 01.05.2010 - 02.12.2016 ist der MA bereits 67 Monate im Betrieb und nicht nur 60 Monate. Keine Ahnung woran das liegt. Das Weihnachtsgeld wird aber richtig gerechnet *Smilie *TOP*!

Lassen sich die Zeilen in der Spalte "O und P" WG in der Formel bereits bunt darstellen oder macht man das über die bedingte Formatierung? z.B. Alle ungekündigten MA mit Monate und Betrag WG grün, alle MA mit voraussichtlichen Beschäftigungsende die noch WG bekommen orange und die die kein WG bekommen rot.

Vielen Dank.

Beispieldatei habe ich wieder hochgeladen.

Herzlichen Gruß
Krauxi

 

Moin
Schaue nachher mal in die Mappe. Ca. 2 Std.

Jepp, liegt an deinen Stichtagen. Es wird von Stichtag(30.11) bis Stichtag30.11) gerechnet.
War doch die Vorgabe oder?

gruss hary

 

Hallo Krauxi und hallo harry,

ich würde hier ja gerne helfen, aber mir stehen die Haare zu Berge.

Also bei mir sind das 9 Monate, da der Stichtag ja der 30.11.2015 sein soll. Oder aber 10 Monate, wenn der Stichtag gerechterweise der 01.12.2015 wäre, denn mit 30.11.2015 ist nach meiner Auffasung 24:00 Uhr gemeint und somit muss mit dem 01.12.2015 gerechnet werden. Aber 11 Monate kann ich bei der Vorgabe gar nicht nachvollziehen ?????

@Harry: Warum machst Du es so kompliziert:

PHP:

         DATUM(JAHR(H10);MONAT(H10);TAG(H10))


 

Moin
Mal schnell zwischem dem Kofferpacken.
Teste mal im Anhang. SpalteL ist ausgeblendet. Bedingte Formatierung drin.
gruss hary
@ Ingo:
Hatte ja,fuer die Loesung, auf dich gesetzt. Hast aber immer drumrumgeklickt. ;-) LOL
Bin froh mit den Datenformeln soweit zu kommen.
Mit H10 haste Recht *schaem*
z.B. vom 01.02.2015 - 29.01.2016 sind 11 Monate und nicht 1 Monat
Der Stichtag ist immer der 30.11.
Der MA ist eingetreten im Feb. also ist der Stichtag trotzdem der 30.11.
Ist also praktisch das Geschaeftsjahr vom 30.11. bis 30.11 Folgejahr.

 

Hallo Hary, hallo Ingo *Smilie,

hmmm... Stichtag für das Weihnachtsgeld ist immer der 30.11. eines Jahres!

Wenn ein MA zu dem Stichtag keine 12 Monate Betriebszugehörigkeit aufweisen kann, bekommt dieser kein WG.

Der MA der zum 01.02.2015 eingetreten ist und zum 29.01.2016 wieder austritt, hat er zum Stichtag keine 12 Monate Betriebszugehörigkeit, ergo, kein WG. An dieser Stelle hab ich mich wohl auch verzählt (hätt ich mal "manuell" nachgezählt *upps *wink.gif* ) - vom 01.02.2015 bis 30.11.2015 0:00 Uhr sind es 10 Monate und nicht 11 *upps. vom 01.12.2015 bis 29.01.2016 sind es dann nochmal 2 Monate - wären dann 12 Monate - aber - der Stichtag zählt. Außerdem hätte der MA das WG eh zurückzahlen müssen, da er vor dem 31.03.2016 aus der Firma ausscheidet.

Wenn jetzt z.B. ein MA am 01.02.2014 eingetreten ist, dann erfüllt er die Bedingung WG bis zum 30.11.2014 nicht und würde hier kein WG bekommen (Bedingung nicht erfüllt)! Das erste Jahr wo dieser MA dann WG bekommt ist zum (01.12.2014 bis) 30.11.2015 (110,- €) die Monate davor fallen quasi "untern Tisch". Die WG-Betriebszugehörigkeit geht also vom 01.12. bis 30.11. des Folgejahres (12 Monate). Wenn dieser MA dann vor dem 31.03.2016 aus dem Betrieb ausscheidet muß er das WG zurückbezahlen.

Soviel noch einmal zur genaueren Erklärung... *Smilie

 

Moin
Passt die letzte Mappe?
edit: fuer die Spalte Datediff()+1
gruss hary

 

Hallo hary,

die Tabelle schaut gut aus *Smilie .

Wird da meine letzte Erklärung in der Formel beachtet? Nicht das mancher MA zu viel WG kreigt ... da würde einges an zuviel bezahlten Euros zusammen kommen, weil sind nicht grad wenige MA. Falls sich da was an den Zeiträumen überschneidet. Wie bzw. wo wird den der Stichtag abgefragt?

Wo kommt das +1 hin? einfach hinten an die Formel Datedif dranhängen?

Kofferpacken? ...wo gehts den hin? *Smilie *wink.gif*

Dankesehr für deine Mühe hary.

Herzlichen Gruß
Krauxi

 

Moin
Hab bis Di. keinen zugriff auf Excel und I.-net

Jepp,musst dann mal nachrechnen.
Der Stichtag wird in der Formel hiermit
DATUM(JAHR(H10);11; 30)
zugewiesen.
Ergebnisss musst du mal anhand einiger Testdaten nachpruefen.
Es geht ab in den Sueden(PT) wo es warm ist.
gruss hary

 

Hallo Krauxi,

Deine Erklärungen lösen bei mir nur Fragezeichen aus:

Wenn hier 0:00 Uhr gilt dann sind es nur 9 Monate!!! Ich denke aber mal, dass Du 24:00 Uhr meinst, dann sind es 10 Monate.

Bei mir ist es nur 1 Monat! Da fehlt doch noch der 30.01.2016 und der 31.01.2016 damit es 2 Monate werden!

Ich kann nur schwer helfen, wenn unlogisch erklärt wird. (@hary: Deswegen hatte ich den Thread auch gemieden)

Vorab mal eine allgemeine Erklärung: Wenn ich einen ganzen Monat irgendwo beschäftigt werde, dann geschieht das beispielweise vom 01.01.2015 bis zum 31.01.2015. Als Eintrittsdatum ist grundsätzlich 0:00 Uhr (oder der tatsächliche Arbeitsbeginn) gemeint und beim Austrittsdatum grundsätzlich 24:00 Uhr (oder das tatsächliche Arbeitsende).
DATEDIF ("m") rechnet aber bei diesen beiden Daten nur eine Differenz von 0 Monaten, daher muss +1 beim Tagesdatum mitgerechnet werden.

Ob ich nun die Monate "richtig" oder auf ein Vielfaches von 12 abrunde ist ergebnistechnisch unrelevant, wenn ich beim Berechnen des Weihnachtsgeldes dann abrunde. Demzufolge wäre dies hier eine mögliche Art das richtige WG zu errechnen:
O10:

PHP:

         =WENNFEHLER(DATEDIF(H10;$N$3+1;"m");0)