KHIZHNYAKOVAbwesenheitsverwaltung in Excel: Urlaub, Krankheit und Abteilungskalender
Abwesenheitsverwaltung in Excel: Krankheit, Urlaub, Ausgleichstage, Dienstreisen, Abteilungskalender – Excel-Vorlage — Zur Vorlage →
Wer ist heute im Urlaub, wer krank, wer hat noch Resturlaub? Und fehlen nächste Woche nicht plötzlich fünf Personen derselben Abteilung? In vielen Teams werden diese Fragen aus dem Gedächtnis und aus Zetteln beantwortet.
In diesem Leitfaden bauen Sie eine Abwesenheitsverwaltung in Excel auf: ein Journal, den Resturlaub pro Person und einen Monatskalender mit Warnung bei zu vielen Abwesenden. Mit Beispiel, Formeln und den häufigsten Fehlern.
Das Abwesenheitsjournal
Das Fundament ist eine Liste, in der jede Abwesenheit eine Zeile ist: Mitarbeiter, Art (Urlaub, Krankheit, Ausgleichstag, Dienstreise), von, bis, Grundlage. Ein Urlaub in zwei Teilen sind zwei Zeilen. Daraus berechnen Sie:
- Kalendertage:
=C2-B2+1 - Arbeitstage ohne Wochenenden und Feiertage:
=NETTOARBEITSTAGE(B2;C2;Feiertage)
Pflegen Sie die Feiertage in einer eigenen Liste und verweisen Sie darauf. Ob Ihre Regeln Urlaub nach Kalender- oder Arbeitstagen abziehen, prüfen Sie vor Ort; die Formel passt sich an.
Resturlaub berechnen
Rest = Anspruch plus Übertrag aus dem Vorjahr minus genommene Tage minus geplante Tage. Mit SUMMEWENNS trennen Sie genommen und geplant über das Datum:
=Anspruch+Übertrag-SUMMEWENNS(Tage;Name;A2;Art;"Urlaub";Bis;"<="&HEUTE())-SUMMEWENNS(Tage;Name;A2;Art;"Urlaub";Von;">"&HEUTE())
Rechenbeispiel
| Größe | Tage |
|---|---|
| Anspruch | 28 |
| Übertrag | 3 |
| Genommen bis heute | 14 |
| Geplant (Weihnachten) | 6 |
| Rest | 28 + 3 − 14 − 6 = 11 |
Setzen Sie eine Schwelle für hohen Resturlaub, etwa 15 Tage vor dem Jahresende, und lassen Sie die Zelle farbig werden. So fällt auf, wenn Urlaub zu verfallen droht, was nach Ihren Regeln gilt.
Überschneidungen finden
Zwei Prüfungen verhindern die meisten Fehler:
- Datum „bis“ liegt vor „von“:
=WENN(C2<B2;"Datum prüfen";"") - Überschneidung mit einer anderen Abwesenheit derselben Person:
=WENN(SUMMENPRODUKT((A$2:A$800=A2)*(B$2:B$800<=C2)*(C$2:C$800>=B2))>1;"Überschneidung";"")
Die zweite Formel zählt alle Zeilen derselben Person, die den Zeitraum berühren. Mehr als eine, nämlich die Zeile selbst plus eine weitere, bedeutet Überschneidung.
Der Abteilungskalender
Ein Monatskalender zeigt in Zeilen die Mitarbeiter, in Spalten die Tage. In jede Zelle kommt ein Kürzel, wenn an diesem Tag eine Abwesenheit liegt:
=WENN(ZÄHLENWENNS(Name;$A5;Von;"<="&D$4;Bis;">="&D$4;Art;"Urlaub")>0;"U";"")
Analog für K (Krankheit), A (Ausgleich) und D (Dienstreise). Unter jeder Tagesspalte zählen Sie die Abwesenden mit ZÄHLENWENN. Überschreitet die Zahl Ihre Grenze, färbt die bedingte Formatierung die Zelle rot.
Häufige Fehler
- Krankheit und Urlaub in einer Spalte ohne Art: Dann stimmen die Salden nicht.
- Feiertage vergessen: Sie verfälschen die Arbeitstage.
- Geteilter Urlaub als eine Zeile: Das verhindert saubere Überschneidungsprüfungen.
- Keine Obergrenze: Wer die zulässige Zahl gleichzeitig Abwesender nicht festlegt, merkt Engpässe zu spät.
- Sensible Daten: Krankheitsdaten gehören nicht in eine frei zugängliche Datei. Prüfen Sie Zugriffsrechte und Datenschutzregeln vor Ort.
Auswertung nach Monaten und Arten
Für die Jahresplanung lohnt eine Übersicht, die Abwesenheitstage nach Monat und Art zusammenfasst, mit SUMMEWENNS über Monat und Art. Sie zeigt, in welchen Monaten Urlaub gehäuft vorkommt und ob Krankheitstage steigen. So können Sie Vertretungen rechtzeitig planen und Urlaub besser über das Jahr verteilen.
Fertige Vorlage für Abwesenheiten
Die Excel-Vorlage „Abwesenheitsverwaltung in Excel“ bringt das als fertige Mappe mit.
- „Parameter“: Jahr, Urlaubsanspruch, zulässige Zahl gleichzeitig Abwesender, Schwelle für hohen Resturlaub, Feiertage und Abteilungen.
- „Mitarbeitende“: bis zu 60 Personen mit Urlaubsübertrag, genommen, geplant, Rest, Krankheit, Ausgleichstagen, Dienstreisen, Status heute und Kontrolle.
- „Abwesenheiten“: bis zu 800 Einträge mit Art, Daten, Grundlage, Kalender- und Arbeitstagen, Status und Prüfung auf Überschneidungen.
- „Kalender“: Anwesenheit der Abteilung für jeden Monat mit den Kürzeln U, K, A, D, Zahl der Abwesenden pro Tag und Summen je Mitarbeiter.
- „Übersicht“: Abwesenheiten nach Monaten und Arten, Kontrolle nach Abteilungen und zwei Diagramme.
Im Lieferumfang sind eine leere Tabelle und ein ausgefülltes Beispiel mit erfundenen Daten. Die Datei läuft ohne Makros in Excel 2007 und neuer sowie in LibreOffice und ist kostenpflichtig. Es ist eine Excel-Tabelle mit gewöhnlichen Formeln, keine Buchhaltungssoftware; Ansprüche und Schwellen gleichen Sie mit den Regeln vor Ort ab.
FAQ
Wie sehe ich, wer an einem bestimmten Tag fehlt?
Im Kalender ein Datum des Monats wählen: Die Kürzel je Person und die Zahl der Abwesenden pro Tag erscheinen.
Wie gehe ich mit halben Urlaubstagen um?
Mit einer eigenen Zeile und dem entsprechenden Wert in der Tagesspalte. Prüfen Sie, wie Ihre Regeln halbe Tage behandeln.
Wer sollte Zugriff haben?
So wenige Personen wie möglich. Abteilungsleitungen brauchen den Kalender, Details zu Krankheitsgründen brauchen sie nicht.
