Urlaubsplaner in Excel bauen — Jahresmatrix, Resturlaub, Kollisionscheck

Die Struktur entscheidet, nicht das Design

Die meisten selbstgebauten Urlaubsplaner sind Monatsblätter: zwölf Registerkarten, jeweils ein Raster mit Namen und Tagen. Das sieht aufgeräumt aus und ist die teuerste Entscheidung im ganzen Aufbau. Denn sobald der Resturlaub über das Jahr gerechnet werden soll, muss jede Formel zwölf Blätter zusammenzählen — und beim Jahreswechsel wird die Datei nicht kopiert, sondern neu gebaut.

Die tragfähige Alternative ist eine einzige Jahresmatrix: 365 Spalten nebeneinander, eine Zeile je Person. Das wirkt zunächst unhandlich, ist es aber nicht — mit fixierten Fenstern und einer Spaltenbreite von zwei Zeichen bleibt der Überblick erhalten, und jede Auswertung ist ein einziger Bereichsbezug statt zwölf.

Wichtig ist, dass die Datumszeile echte Datumswerte enthält und nicht die Zahlen 1 bis 31 als Text. Nur dann liefern WOCHENTAG, TEXT und der Abgleich mit der Feiertagsliste brauchbare Ergebnisse. Der gleiche Grundsatz gilt im gesamten Aufbau des Plans — er ist in Dienstplan in Excel erstellen ausführlich beschrieben.

Zum Schluss der Struktur zwei Handgriffe, die den Unterschied zwischen benutzbar und unbenutzbar machen: Ansicht → Fenster fixieren mit der Zelle D5 als Ankerpunkt, damit Namen und Kopfzeilen beim Scrollen stehen bleiben, und eine Spaltenbreite von etwa zwei Zeichen für den gesamten Bereich D bis NC. Damit passt ein Quartal auf einen Bildschirm.

Kürzel statt Freitext — die Datenüberprüfung

Der zweithäufigste Konstruktionsfehler nach den Monatsblättern ist Freitext in der Planfläche. „Urlaub“, „U“, „u“, „Url.“ und „U (halb)“ sind für den Menschen dasselbe und für ZÄHLENWENN vier verschiedene Dinge. Eine feste Kürzelliste löst das Problem an der Quelle.

Die Liste bekommt über Formeln → Namen definieren den Namen Kuerzel. Anschließend die Planfläche D5:NC18 markieren und unter Daten → Datenüberprüfung → Zulassen: Liste als Quelle =Kuerzel eintragen. Danach nimmt die Fläche nur noch gültige Werte an, und in jeder Zelle erscheint ein Auswahlpfeil.

Die Trennung zwischen U und B ist der eigentliche Gewinn dieser Liste. Ein beantragter Urlaub ist kein genehmigter, aber er blockiert bereits einen Tag in der Planung. Wer beides mit demselben Kürzel führt, hat entweder zu viele Tage abgezogen oder eine Doppelbelegung übersehen. Mit zwei Kürzeln zeigt die Fläche beide Zustände, und der Resturlaub rechnet nur mit dem genehmigten Teil.

Farbe kommt nicht durch manuelles Einfärben, sondern über die bedingte Formatierung: je eine Regel vom Typ =$D5="U", =$D5="B" und =$D5="K" auf den markierten Bereich. Dazu die beiden Kalenderregeln =WOCHENTAG(D$3;2)>5 für Wochenenden und =ZÄHLENWENN(Feiertage;D$3)>0 für Feiertage — wie sie aufgebaut und in die richtige Reihenfolge gebracht werden, steht in Feiertage in Excel markieren. Wichtig ist nur das Muster der Dollarzeichen: Abwesenheitsregeln fixieren die Spalte ($D5), Kalenderregeln die Zeile (D$3).

Die Resturlaubsformel — und warum ZÄHLENWENN hier zu wenig kann

Die naheliegende Formel =ZÄHLENWENN(D5:NC5;"U") zählt alle Urlaubskürzel einer Zeile. Sie ist falsch, sobald jemand den ganzen August markiert: Sie zählt auch die Samstage, Sonntage und den 15. August mit. Urlaub wird aber nur für Tage verbraucht, an denen sonst gearbeitet worden wäre.

ZÄHLENWENN kann Bedingungen nur auf denselben Bereich anwenden. Gebraucht wird eine Funktion, die den Zelleninhalt in Zeile 5 und das Datum in Zeile 3 gleichzeitig prüft. Das leistet SUMMENPRODUKT:

Die drei Klammerausdrücke liefern je eine Reihe aus WAHR- und FALSCH-Werten. Die Multiplikation macht daraus Einsen und Nullen und verknüpft sie mit UND-Logik: Nur wo alle drei Bedingungen zutreffen, steht am Ende eine Eins. SUMMENPRODUKT addiert diese Einsen — fertig ist die Zahl der urlaubswirksamen Tage.

Drei Anpassungen kommen in der Praxis häufig vor:

• Sechs-Tage-Woche: WOCHENTAG(...;2)<6 wird zu <7. Dann zählt der Samstag mit, der Sonntag nicht.
• Halbe Tage: ein zusätzliches Kürzel UH aufnehmen und den Ausdruck um +SUMMENPRODUKT((D5:NC5="UH")*...)*0,5 ergänzen. Sauberer, als in einer Zelle „U/2“ zu schreiben.
• Individuelle Arbeitstage: Wer nur dienstags bis donnerstags arbeitet, verbraucht auch nur an diesen Tagen Urlaub. Statt der pauschalen Wochentagsbedingung greift dann ein Verweis auf eine Spalte mit dem persönlichen Arbeitstagemuster.

Für die Zahl in Spalte B — den Jahresanspruch — gilt bei Teilzeit die Umrechnung nach Arbeitstagen, nicht nach Stunden: Urlaubstage bei Fünf-Tage-Woche ÷ 5 × tatsächliche Arbeitstage pro Woche. Aus 30 Tagen werden bei drei Arbeitstagen pro Woche also 18. Wie sich das in einer fertigen Arbeitsmappe abbilden lässt, zeigt die Urlaubsplan-Vorlage.

Der Kollisionscheck: Wer fehlt am selben Tag?

Der Resturlaub beantwortet die Frage der einzelnen Person. Die Frage des Betriebs ist eine andere: Bleibt der Dienstplan an jedem Tag besetzt? Dafür wird unter der Matrix eine Auswertungszeile eingezogen — sie ist der eigentliche Grund, warum die Jahresmatrix den Monatsblättern überlegen ist.

Auf Zeile 23 kommt eine bedingte Formatierung mit der Regel =D23="!" und roter Füllung. Damit wandert beim Eintragen eines Urlaubs sofort ein roter Balken durch den Kalender, wenn die Besetzung unter das Minimum fällt — noch bevor der Antrag genehmigt ist.

Zwei Verfeinerungen lohnen sich, sobald das Team nicht mehr homogen ist. Erstens eine qualifikationsbezogene Zählung: Wenn an jedem Tag mindestens eine Person mit Schlüsselqualifikation anwesend sein muss, zählt eine zweite Zeile nur diese Personen — über =SUMMENPRODUKT(($E$5:$E$18="Schicht­leitung")*(D$5:D$18="")), wobei Spalte E die Qualifikation enthält. Zweitens eine Sperrfrist: Zeiträume, in denen kein Urlaub möglich ist, bekommen ein eigenes Kürzel in einer separaten Zeile und eine Regel, die Urlaubseinträge in diesen Spalten rot umrandet.

Nicht abbilden lässt sich in dieser Zeile die Rangfolge konkurrierender Anträge. Nach § 7 Abs. 1 BUrlG sind Urlaubswünsche zu berücksichtigen, es sei denn, dringende betriebliche Belange oder die unter sozialen Gesichtspunkten vorrangigen Wünsche anderer stehen entgegen. Welcher Antrag vorgeht, ist damit eine Abwägung — schulpflichtige Kinder, Pflegeverpflichtungen, wer im Vorjahr zurückstecken musste. Die Tabelle zeigt den Konflikt; entscheiden muss ihn die Führungskraft, und zwar dokumentiert.

Was die Tabelle rechtlich nicht abbildet

Ein Urlaubsplaner ist kein Urlaubskonto. Der Unterschied wird an vier Punkten teuer.

Der Mindestanspruch. § 3 BUrlG nennt 24 Werktage bei einer Sechs-Tage-Woche — umgerechnet 20 Arbeitstage bei einer Fünf-Tage-Woche. Werktage sind alle Tage außer Sonn- und Feiertagen, der Samstag zählt also mit. Wer die Vertragszahl in Spalte B einträgt, sollte einmal prüfen, ob sie diesen Boden für jede Arbeitszeitform einhält.

Krankheit im Urlaub. Nach § 9 BUrlG werden durch ärztliches Zeugnis nachgewiesene Krankheitstage nicht auf den Jahresurlaub angerechnet. In der Matrix heißt das: Das U wird für die betroffenen Tage durch K ersetzt, und die Resturlaubsformel korrigiert sich von selbst. Ohne diese Korrektur zahlt der Betrieb Tage aus, die er nicht schuldet — oder die beschäftigte Person verliert Tage, die ihr zustehen.

Die Übertragung ins Folgejahr. § 7 Abs. 3 BUrlG bindet den Urlaub an das laufende Kalenderjahr; eine Übertragung auf die ersten drei Monate des Folgejahres setzt dringende betriebliche oder in der Person liegende Gründe voraus. Die Spalte C bildet das Ergebnis ab — den Grund und seine Prüfung bildet sie nicht ab.

Und der Punkt, an dem Excel strukturell endet: die Hinweispflicht. Der Verfall zum Jahresende oder zum 31. März tritt nach der Rechtsprechung des Bundesarbeitsgerichts nur ein, wenn der Arbeitgeber zuvor seine Mitwirkungsobliegenheiten erfüllt hat — er muss jede beschäftigte Person individuell und rechtzeitig über den konkreten Reststand informieren und sie auffordern, den Urlaub zu nehmen (BAG, Urteil vom 19.02.2019 – 9 AZR 541/15, im Anschluss an EuGH, Urteil vom 06.11.2018 – C-684/16). Unterbleibt das, verfällt der Urlaub nicht. Und er verjährt auch nicht nach drei Jahren: Die Verjährungsfrist beginnt erst zu laufen, wenn der Hinweis erteilt wurde (BAG, Urteil vom 20.12.2022 – 9 AZR 266/20).

Praktisch bedeutet das: Nicht der Reststand ist das Risiko, sondern der fehlende Nachweis, dass darauf hingewiesen wurde. Eine Excel-Datei kann diesen Nachweis nicht führen. Sie zeigt eine Zahl in Spalte NG, aber nicht, ob und wann diese Zahl der betroffenen Person mitgeteilt wurde. Wer den Planer nutzt, sollte deshalb mindestens einmal im Herbst eine dokumentierte Rundmail mit individuellem Reststand versenden — und die Versandbestätigung außerhalb der Tabelle aufbewahren.

Weiterlesen

Den Grundaufbau einer Plandatei beschreibt Dienstplan in Excel erstellen, die Kalenderregeln Feiertage in Excel markieren. Wie mehrere Personen an derselben Datei arbeiten, ohne Versionen zu verlieren, steht in Excel-Dienstplan im Team teilen; die typischen Konstruktionsfehler sammelt Die häufigsten Fehler in Excel-Dienstplänen. Eine fertige Arbeitsmappe steht in der Urlaubsplan-Vorlage, weitere Raster in der Vorlagenübersicht.