Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 08:59:38
Bedingte Formatierung
23.04.2024 08:56:21
Mehrzeilige Zellen in einzelne Zeilen aufteilen & auswer
23.04.2024 08:26:15
Rückgabewert mehrere Suchkriterien Datumsbereich
23.04.2024 08:20:37
VBA: Blatt als pdf per Mail. Blatt mit anderen pdfs vereinen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1516to1520
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1516to1520
1516to1520
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Dienstjahre berechnen

Dienstjahre berechnen
16.10.2016 19:51:23
Stefan
Hallo!
Ich habe folgendes Problem. Ich möchte von Mitarbeitern die Dienstjahre berechnen. Dabei sollen 2 Datumswerte miteinander verglichen werden.
Bsp:
In Zelle K31 wird der Beginn des Dienstverhältnisses eingetragen. In Zelle B5 das Datum wo ich wissen möchte wie viele Dienstjahre der Mitarbeiter an diesem Tag bereits hat.
Daraus ergibt sich dann eine Anspruchsprüfung, die ich für weitere Berechnungen benötige. In der Zelle J33 werden dann dieser berechnet.
bis 5 Jahre Anspruch 6 Wochen
6 - 15 Jahre Anspruch 8 Wochen
16 - 25 Jahre Anspruch 10 Wochen
über 25 Jahre Anspruch 12 Wochen
Ich habe somit in der Zelle K31 folgende Formel:
=WENN((J31="")+(B5="");"";JAHR(B5)-JAHR(J31))
Aber leider stimmt die Berechnung nicht ganz.
Trage ich in B5 das Datum 15.01.16 ein und in J31 den 10.01.11 wäre der Mitarbeiter im 6. Dienstjahr und somit müsste er mir 6 Jahre Dienstjahre berechnen, und der Mitarbeiter hätte 8 Wochen Anspruch. So zeigt er mir aber 5 Jahre an, und das ist falsch.
Zum besseren Verständnis lade ich noch eine Vorlage hoch.
Danke
https://www.herber.de/bbs/user/108815.xlsx

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dienstjahre berechnen
16.10.2016 20:25:29
Daniel
Hi
du musst noch prüfen, ob der Jahrestag des Eintritts vor oder nach dem Stichtag liegt (also Datumsvergleich ohne Berücksichtigung des Jahres)
Liegt der Einritt vor dem Stichtag, musst du zu den berechneten Jahren noch 1 hinzuaddieren:
=WENN(ANZAHL(B5;J31)<2;"";JAHR(B5)-JAHR(J31)+WENN(TEXT(J31;"MMTT")<TEXT(B5;"MMTT");1;0))
Gruß Daniel
AW: Dienstjahre berechnen
16.10.2016 20:52:33
Stefan
Hallo Daniel!
Vielen Dank für deine Antwort. Jetzt funktioniert es fast perfekt, ein kleiner Fehler ist noch drin.
Wenn ich in B5 den 15.01.16 und in J31 den 15.01.11 eintrage wäre eigentlich auch mit 15.01.16 wieder der Sprung aufs 6. Dienstjahr, er zeigt aber 5. Dienstjahre an. Das heißt man müsste noch irgendwie in der Formel einen Tag abziehen.
Weiß aber nicht wo ich das noch anpassen kann.
Anzeige
AW: Dienstjahre berechnen
16.10.2016 21:06:20
Daniel
Hi Stefan
die Formel ist doch nicht besonders kompliziert.
Ich hatte ja geschrieben, dass ich prüfe, ob der Jahrestag VOR dem Stichtag liegt.
wenn du jetzt so haben willst, dass die 1 schon hinzuaddiert wird, wen der Jahrestag vor dem Stichtag liegt oder gleich dem Stichtag ist, dann ist es doch offensichtlich, was du ändern musst.
(sorry, aber ich erwarte eigentlich das Mitdenken des Fragestellers und das er versucht, die gezeigte Lösung zu verstehen und sie nicht nur einfach kopiert und einfügt)
Gruß Daniel
AW: Dienstjahre berechnen
16.10.2016 21:45:51
Stefan
Hallo Daniel.
Sorry bei Formeln mit der Funktion TEXT, tu ich mir relativ schwer, diese richtig anzupassen. Mit der Funktion "TEXT" wandelt man ja die Zahl in den Text um und du vergleichst den Stichtag miteinander. Wenn der Wert größer ist als der andere machst du Plus 1 sonst 0. Das kapier ich ja noch, aber nicht wie ich einfach einen Tag noch abziehen kann? Oder ist mein Denkansatz falsch?
Oder meinst du einfach mit ">=" ergänzen, und die Formeln mit dem Stichtag umzudrehen? Also so?
=WENN(ANZAHL(B5;J31)=TEXT(J31;"MMTT");1;0)) So funktioniert es glaub ich? Oder hab ich jetzt wieder einen Denkfehler drin? Sorry aber ich steh grad ein wenig an.
Anzeige
AW: Dienstjahre berechnen
16.10.2016 22:01:02
Daniel
Hi
ein Vergleich von zwei Werten (Grösser, Kleiner, Gleich) funktioniert auch mit Texten.
"A" ist kleiner als "B" und auch als Text ist "1" kleiner als "2"
Text habe ich hier nur gewählt, weil man so am einfachsten die Datumswerte ohne Jahr in eine vergleichbare Form bringen kann. Solange die Anzahl der Zeichen gleich ist, ist es egal ob ich Zahlenwerte oder Texte miteinander vergleiche.
ansonsten, warum wartest du auf meine Antwort, wenn du etwas durch einfaches Ausprobieren selbst herausfinden kannst?
Ich meine, durch das Testen einer Formel macht man ja nichts kaputt, warum also zögerlich sein?
Im Zweifelsfall kann man ja immer noch vorher die Datei speichern.
Gruß Daniel
Anzeige
AW: Dienstjahre berechnen
16.10.2016 22:26:26
Stefan
Sorry aber ich kapiere es noch immer nicht ganz. Das mit dem Vergleich versteh ich ja auch was größer oder kleiner ist. Mein Denkansatz war dann eben, dass man einfach einen Tag addiert bzw. abzieht. Denn wenn der Beginn des Dienstverhältnis am selben Tag wie der Stichtag fällt, rechnet er ja dann einfach den Tag dazu und es müsste dann ja der Sprung von 5 Jahre auf 6 Jahre funktionieren.
Ich probiere bereits die ganze Zeit seit dem wir schreiben selbst herum. Nur weiß ich eben bei der Formel nicht so Recht wo ich genau ansetzen kann.
Hab es jetzt auf diese Formel geändert. Und ich glaub es funktioniert.
=WENN(ANZAHL(B5;J31)=TEXT(J31;"MMTT");1;0))
Meine Frage an dich war, ob ich noch einen Denkfehler dabei habe. Du siehst es ja sicher auf einem Blick, ob das jetzt so stimmt, oder ob ich weiter am Holzweg bin...
Hab beim Testen jetzt zumindest mal keinen Fehler gemerkt, nur hätte ich gerne das es dann wirklich passt und ich damit abschließen kann.
Anzeige
AW: Dienstjahre berechnen
17.10.2016 22:37:49
Stefan
Hallo Hardy!
Danke auch dir für deine Antwort. Ist auch eine Möglichkeit, aber dann stimmt ja die Anzeige von den Dienstjahren nicht, sondern es wird nur der Anspruch richtig gerechnet weil es über 15 Jahre sind.
Möchte aber auch das man die Dienstjahre richtig sieht.
das ist so OK
17.10.2016 08:36:38
WF
.
AW: das ist so OK
17.10.2016 22:38:58
Stefan
Hallo WF
Danke, mir ist beim testen bis jetzt auch noch kein Fehler aufgefallen. Ich hoffe das passt jetzt so.
AW: Dienstjahre berechnen
16.10.2016 20:29:32
ransi
Hallo,
ICh würds so machen:
Tabelle1

 BCDEFGHIJK
4Datum         
515.01.2016         
6          
7          
8          
9          
10          
11          
12          
13          
14          
15          
16          
17          
18          
19          
20          
21          
22          
23          
24          
25          
26          
27          
28          
29    AnspruchWochen    
30    06  Beginn DienstverhältnisDienstjahre
31    68  10.01.20115
32    1610  Anspruch
33    2612  6 
34          

Formeln der Tabelle
ZelleFormel
K31=DATEDIF(J31;B5;"Y")
J33=SVERWEIS(K31;F30:G33;2;WAHR)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
Anzeige
AW: Dienstjahre berechnen
16.10.2016 20:54:33
Stefan
Hallo ransi!
Danke auch für deine Antwort.
Das mit dem SVERWEIS ist mal eine Super Idee dann erspar ich mir die lange "Wenn Formel" zur Anspruchsprüfung. Zu deiner weiteren Lösung die funktioniert leider nicht ganz, den mit Dateif zeigt er auch 5 Jahre statt 6 Dienstjahre an.
AW: Einfachlösung
17.10.2016 18:04:47
MB12
Hi Stefan,
nur als Ansatz:
K1 oder irgendeine Hilfszelle Schriftfarbe weiß =WENN((J31="")+(B5="");"";(B5-J31)/365)
L1: =AUFRUNDEN(K31;0)
Ich weiß, dass man die beiden Formeln auch zusammenpacken könnte, aber dazu fehlt mir die Zeit.
Vielleicht hilft das trotzdem weiter.
Gruß, Margarete
Anzeige
eben nicht
17.10.2016 19:50:03
WF
Hi,
vom 15.02.2015 (J31) bis 15.02.2016 (B5) erhältst Du 1 angefangenes Jahr - es sind 2 Jahre.
WF
AW: eben nicht
17.10.2016 22:49:18
Stefan
Ja da hast du Recht, so klappt es nicht.
Ich hab es jetzt mal so gelöst.
=WENN(ANZAHL(B5;J31)=TEXT(B5;"MMTT");1;0))
Und die Formel für den Anspruch. Kann man sicher auch vereinfachen, aber es funktioniert.
=WENN((J31="")+(B5="");"";WENN(K315;K3115; K3125;"12 Wochen")))))

Warum offen wenn es funktioniert? owT
18.10.2016 06:13:20
Bernd
AW: Einfachlösung
17.10.2016 22:46:28
Stefan
Hallo Margarete!
Das funktioniert leider auch nicht ganz. Da wenn ich zb. den 15.01.16 mit Dienstverhältnis Beginn 15.01.10 vergleiche er mir dann 7 errechnet anstatt 6.
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige