Neues Datum für Betriebszugehörigkeit berechnen

Question

Hallo,

wir haben einen Mitarbeiter, der bei uns gearbeitet hat, zwischendurch ausgeschieden ist und wieder neu angefangen hat.
Die Betriebszugehörigkeit beginnt jetzt aber nicht mit dem neuen Eintrittsdatum; sondern es muss die Zeit angerechnet werden, wo er schonmal da war, d. h. es muss vor dem neuen Eintrittsdatum liegen.
Ich habe dazu folgende Excel gebastelt.

http://www.file-upload.net/download-7159413/Eintrittsdatum-nach-Unterbrechung-neu-berechnen.xls.html

Das Problem, das ich habe ist, dass meine Formel diesbezüglich ungenau, da wahrscheinlich zu einfach ist: obwohl der Mitarbeiter volle Monate ausgeschieden war und er auch zum 01. des Monats beginnt, berechnet Excel mir kein Eintrittsdatum, welches auch der 01. sein müsste, sondern gibt mir zwei Tage früher an (siehe Zelle A15).
Kann mir jemand eine Formel nennen, die das berücksichtigt bzw. generell eine Formel, die ggf. untermonatige Ein- und Austritte auch richtig rechnet?

Answer 1

Kleine Korrektur: es muss in der Klammer "Zelle B15" heißen.

Answer 2

Hallo,

meinst Du so?

=B12-DATEDIF(B8;B9;"d")-1

Gruß
Rainer

Answer 3

Hallo Rainer,

erstmal danke für die schnelle Antwort.

Bei mir führt die Formel leider zu dem gleichen Ergebnis, das ich auch schon vorher hatte. Es müsste allerdings der 01.02.2011 herauskommen...

Gruß
Lars

Answer 4

Hallo,

versuch es mal mit folgender Formel:

=DATUM(JAHR(B12)-DATEDIF(B8;B10;"y");MONAT(B12)-DATEDIF(B8;B10;"ym");TAG(B12)-DATEDIF(B9;B11;"md"))

Gruß

M.O.

Answer 5

Hallo Lars,

die Formel rechnet schon richtig, allerdings kann sie Deine stillen Wünsche nicht erahnen.

Versuch's mal so

=DATUM(JAHR(B12-(B9-B8));MONAT(B12-(B9-B8))+1;1)

Gruß
Rainer

Answer 6

Hallo,

noch mal ich.
Der letzte Teil der Formel muss natürlich auch DATEDIF(B8;B10;"md")
heißen.

Hier noch einmal die verbesserte Formel:

=DATUM(JAHR(B12)-DATEDIF(B8;B10;"y");MONAT(B12)-DATEDIF(B8;B10;"ym");TAG(B12)-DATEDIF(B8;B10;"md"))

Gruß

M.O.

Answer 7

Hallo Rainer und M.O.,

danke für die Antworten.
Für mich passt die Formel von M.O. besser, da sie untermonatige Unterbrechungen mitberücksichtigt.

Ich habe mal die Datei um Spalte C und D mit zwei Beispielen erweitert und in Zeile 18 die Formel von M.O. eingetragen und in Zeile 22 die von Rainer.
Da ist in Spalte C trotz ganzmonatiger Unterbrechung immer noch eine Diskrepanz zwischen den Formeln aus Zeile 18 und 22.

http://www.file-upload.net/download-7160214/Eintrittsdatum-nach-Unterbrechung-neu-berechnen.xls.html

Gruß
Lars

Answer 8

Hallo Lars,

lösche mal in Spalte C aus Rainers Formel das +1 und sieh was passiert.

Gruß

M.O.

Answer 9

Hi M.O.,

danke erstmal.

Könntest du mir vielleicht einmal die Funktionsweise der Formel erklären? Was ym und md, habe ich bereits rausfinden können.

Allerdings verstehe ich noch nicht ganz die Vorgehensweise der Formel, z.B. welche Argumente in der Formel werden wie in welcher Reihenfolge umgesetzt. Was macht sie genau an welcher Stelle, damit sie am Ende das geünschte ergebnis bringt.

Ich hoffe du weißt, wie ich das meine. Ich möchte ein tieferes Verständnis für sie bekommen, auch vor dem Hintergrund, dass ich sie vielleicht selbst erweitern kann, wenn z. B. ein Mitarbeiter mehrere Ein- und Austritte hat.

Gruß
Lars

Answer 10

Hallo Lars

mit DATUM (Jahr, Monat, Tag) wird ein Datum erstellt.
Die Formel =DATUM(2012,12,31) ergibt somit den 31.12.2012

In B12 steht ja dein neues Eintrittsdatum.
JAHR(B12) liefert nur das Jahr dieses Datums. Davon abgezogen werden die mit DATEDIF ermittelten Jahre zwischen dem 1. Eintrittsdatum und dem Beginn der Unterbrechung.

DATEDIF berechnet den Zeitraum zwischen zwei Daten
=DATEDIF(Anfangsdatum;Enddatum;"Zeiteinheit").
Folgende Zeiteinheiten stehen zur Verfügung:
y - Anzahl kompletter Jahre
m - Anzahl kompletter Monate
d -Anzahl der Tage
md - Unterschied in Tagen, wobei Monate und Jahre ignoriert werden
ym - Unterschied in Monaten, Tage und Jahre bleiben unberücksichtigt
yd - Unterschied in Tagen, wobei die Jahre ignoriert werden

Somit ermittelt
JAHR(B12)-DATEDIF(B8;B10;"y")
das neue Jahr des fiktiven Eintrittsdatums.

Entsprechend funktioniert das mit dem Teilen MONAT und TAG.
Mit DATEDIF werden nur die jeweiligen Monate und Tage ermittelt und vom Monat und Tag des Eintrittsdatums abgezogen.

Beachte bitte, dass der Ersteintritt vom Beginn der Unterbrechung abgezogen wird, da der letzte Arbeitstag ja auch noch als Betriebszugehörigkeit gilt.

Gibt es mehrere Unterbrechungen, so kannst du die Formel erweitern:
=DATUM(JAHR(Eintrittsjahr)-DATEDIF(Jahre 1. Unterbrechnung)-DATEDIF(Jahre 2. Unterbrechung) usw.

In eine Excel-Formel umsetzt könnte das so aussehen (2 Unterbrechungen):
B30: erstes Eintrittsdatum
B32: Beginn 1. Unterbrechung
B34: zweites Eintrittsdatum
B36: Beginn 2. Unterbrechung
B38: letztes Eintrittsdatum

=DATUM(JAHR(B38)-DATEDIF(B30;B32;"y")-DATEDIF(B34;B36;"y");MONAT(B38)-DATEDIF(B30;B32;"ym")-DATEDIF(B34;B36;"ym");TAG(B38)-DATEDIF(B30;B32;"md")-DATEDIF(B34;B36;"md"))

Ich hoffe, die Erklärung ist ausreichend, falls nicht, frage einfach nach :-).

Gruß

M.O.