KHIZHNYAKOVBetriebszugehörigkeit in Excel berechnen: Jahre, Monate, Tage und nächste Schwelle
Dienstzeit und Betriebszugehörigkeit berechnen in Excel – Excel-Vorlage — Zur Vorlage →
Die Dienstzeit eines Mitarbeiters scheint einfach: Eintrittsdatum minus heute. Sobald es aber mehrere Beschäftigungszeiträume gibt, Unterbrechungen oder Zeiten, die mit einem anderen Faktor zählen, wird die Rechnung unübersichtlich. Noch schwieriger: rechtzeitig zu wissen, wann jemand die nächste Stufe eines Dienstalterszuschlags erreicht.
Hier lernen Sie, wie Sie die Betriebszugehörigkeit in Excel zuverlässig berechnen. Mit Rechenbeispiel, Formeln für einfache und zusammengesetzte Fälle und einer Liste typischer Fehler.
Der einfache Fall: ein Zeitraum
Für Jahre, Monate und Tage zwischen zwei Daten gibt es die Funktion DATEDIF (auch im deutschen Excel unter diesem Namen). Sie ist undokumentiert, funktioniert aber:
- Volle Jahre:
=DATEDIF(A2;B2;"Y") - Restmonate:
=DATEDIF(A2;B2;"YM") - Resttage:
=DATEDIF(A2;B2;"MD")
Als Enddatum nehmen Sie bei laufender Beschäftigung den Stichtag. Achten Sie darauf, ob der Endtag mitzählen soll; in vielen Regelungen zählen Start- und Endtag beide. Dann addieren Sie einen Tag zum Ende.
Mehrere Zeiträume addieren
Bei Unterbrechungen darf man nicht einfach die Jahre aufaddieren, denn Monate und Tage müssen übertragen werden. Der saubere Weg: alles in Tage umrechnen, summieren und am Ende zurückrechnen.
- Pro Zeitraum die Tage:
=Ende-Beginn+1 - Je Person summieren:
=SUMMEWENN(Zeiträume!A:A;A2;Zeiträume!E:E) - Zurückrechnen mit einer festen Konvention, etwa 1 Monat = 30 Tage, 1 Jahr = 360 Tage:
=GANZZAHL(S/360) für Jahre, =GANZZAHL(REST(S;360)/30) für Monate, =REST(REST(S;360);30) für Tage.
Rechenbeispiel
Eine Mitarbeiterin hat zwei Zeiträume: 01.03.2015 bis 31.08.2018 und 01.02.2020 bis 31.10.2025.
| Zeitraum | Tage (volle Monate × 30 plus Rest) |
|---|---|
| 1 (42 Monate) | 1.260 |
| 2 (69 Monate) | 2.070 |
| Summe | 3.330 |
3.330 Tage ergeben 9 Jahre (3.240 Tage), Rest 90 Tage, also 9 Jahre, 3 Monate, 0 Tage.
Zeiträume mit Anrechnungsfaktor
Manche Regelungen rechnen bestimmte Zeiten mit einem Faktor, zum Beispiel 1,5. Legen Sie eine Tabelle der Zeitraumarten mit Faktor an und multiplizieren Sie die Tage:
=(Ende-Beginn+1)*SVERWEIS(Art;Arten!A:B;2;0)
Welche Zeiten wie zählen, regelt Ihre eigene Vereinbarung oder die Vorschrift vor Ort; prüfen Sie das, bevor Sie Faktoren eintragen.
Zuschlagsstaffel und nächste Schwelle
Eine Staffel besteht aus Schwellen in Jahren und Prozentsätzen, etwa: ab 0 Jahren 0 %, ab 3 Jahren 5 %, ab 5 Jahren 10 %, ab 10 Jahren 15 %. Den Satz der erreichten Stufe finden Sie mit einem ungenauen SVERWEIS:
=SVERWEIS(Dienstjahre;Staffel!A:B;2;1)
Die Staffel muss dafür aufsteigend sortiert sein. Die nächste Schwelle ist die kleinste Schwelle, die größer als die aktuelle Dienstzeit ist. Das Datum ihres Erreichens erhalten Sie mit EDATUM aus dem Stichtag plus den fehlenden Monaten. Mit einer Ampel für „in 90 Tagen“ und „in 30 Tagen“ planen Sie rechtzeitig.
Häufige Fehler
- Jahre einfach addieren: 2 Jahre 8 Monate plus 1 Jahr 7 Monate sind 4 Jahre 3 Monate, nicht 3 Jahre 15 Monate.
- Überschneidende Zeiträume: Prüfen Sie, ob das Ende nach dem Beginn liegt und sich nichts doppelt.
- Keine feste Konvention: Legen Sie fest, ob ein Monat 30 Tage oder kalendarisch zählt, und bleiben Sie dabei.
- Staffel unsortiert: Der ungenaue SVERWEIS liefert dann falsche Stufen.
Datumsprüfung nicht vergessen
Fehlerhafte Datumsangaben sind die häufigste Ursache falscher Dienstzeiten. Prüfen Sie pro Zeitraum, ob das Ende nach dem Beginn liegt und ob beide Zellen echte Datumswerte enthalten. Eine einfache Formel genügt: =WENN(ODER(B2="";C2<B2);"Datum prüfen";"OK"). Markieren Sie Fehlermeldungen rot, dann fallen vertauschte Tage und Monate sofort auf.
Fertige Vorlage für Dienstzeit und Betriebszugehörigkeit
Die Excel-Vorlage „Dienstzeit und Betriebszugehörigkeit berechnen“ macht daraus ein fertiges System.
- „Parameter“: Stichtag, Zeitraumarten mit Anrechnungsfaktor, Zuschlagsstaffel und die Fenster „bald“ und „dringend“.
- „Mitarbeitende“: bis zu 200 Personen mit Gesamtdienstzeit in Jahren, Monaten und Tagen, Zuschlagssatz, nächster Schwelle, Datum des Erreichens, Restdauer und Ampel.
- „Zeiträume“: bis zu 2000 Zeiträume mit Person, Beginn, Ende, Art, Anrechnungstagen und Datumsprüfung.
- „Übersicht“: Anzahl der Beschäftigten je Staffelstufe, Liste „Wer bald eine Schwelle erreicht“ und zwei Diagramme.
Die Tabelle rechnet jeden Zeitraum einschließlich Start- und Endtag und verwendet DATEDIF nicht, deshalb läuft sie in Excel und LibreOffice gleich. Beispielwerte sind fiktiv. Die Datei kommt mit leerer Tabelle und ausgefülltem Muster, ohne Makros, und ist kostenpflichtig. Die Berechnung ist ein Richtwert; prüfen Sie Sätze und Schwellen nach den Regeln vor Ort.
FAQ
Zählen Probezeit und Elternzeit mit?
Das hängt von Ihrer Regelung ab. Legen Sie solche Zeiten als eigene Art mit passendem Faktor an (zum Beispiel 0 oder 1).
Warum rechnet man mit 30 Tagen je Monat?
Weil sich Zeiträume so verlässlich zusammenzählen lassen. Entscheidend ist, dass Sie die Konvention einheitlich anwenden.
Wie erkenne ich, wem sich der Zuschlag bald ändert?
Über das berechnete Datum der nächsten Schwelle und die Tage bis dahin, kombiniert mit einer Ampel.
