Urlaubsplaner in Excel erstellen: die Team-Übersicht

Wer im Team Urlaub plant, braucht zwei Antworten: Wie viele Urlaubstage hat jede Person schon eingetragen, und wer fehlt an einem bestimmten Tag? Beides lässt sich mit einer Tabelle und wenigen Formeln lösen. Hier bauen wir einen Jahresplaner für ein kleines Team.

Das Grundprinzip

Der Plan ist eine Matrix: In den Zeilen stehen die Mitarbeitenden, in den Spalten die Tage des Jahres. Wer an einem Tag Urlaub hat, bekommt dort ein „U“. Zählung, Resturlaub und Besetzung ergeben sich aus Formeln, die diese Markierungen auswerten.

Schritt 1: Kopfbereich

Legen Sie links diese Spalten an:

• A – Name
• B – Anspruch (Tage) (Eingabe)
• C – Übertrag aus dem Vorjahr (Eingabe, optional)
• D – Genommen (Formel)
• E – Rest (Formel)

Die Tage des Jahres beginnen in Spalte F. In der Hilfszelle B1 steht das Jahr, etwa 2027. Die Namen stehen ab Zeile 4, Zeile 3 enthält die Daten.

Schritt 2: Die Tage des Jahres erzeugen

In F3 steht der erste Tag:

=DATUM($B$1;1;1)

In G3 und allen weiteren Spalten:

=F3+1

Ein Jahr hat 365 oder 366 Tage, Sie brauchen also bis zu 366 Spalten (F bis NG). Damit die letzte Spalte in einem Jahr mit 365 Tagen nicht den 1. Januar des Folgejahres zeigt, schreiben Sie in NG3:

=WENN(JAHR(NF3+1)=$B$1;NF3+1;"")

Über den Daten fügen Sie zwei Kopfzeilen ein: den Monat mit =TEXT(F3;"MMM") und den Wochentag mit =TEXT(F3;"TTT"). Diese Formatcodes gelten für eine deutschsprachige Oberfläche. Formatieren Sie die Datumszeile mit dem Zahlenformat T, dann steht dort nur die Tageszahl, und die Spalten bleiben schmal. Mit „Fenster fixieren“ bleiben Namen und Summen beim Scrollen sichtbar.

Schritt 3: Wochenenden und Feiertage sichtbar machen

Heben Sie Wochenenden per bedingter Formatierung hervor. Markieren Sie den Datenbereich und legen Sie eine Regel mit Formel an:

=WOCHENTAG(F$3;2)>5

Der Parameter 2 lässt die Woche mit Montag als Tag 1 beginnen; Samstag ist 6, Sonntag 7. Wählen Sie eine graue Füllung. Feiertage tragen Sie auf einem Blatt „Feiertage“ ein und markieren sie mit der Regel:

=ZÄHLENWENN(Feiertage!$A$2:$A$20;F$3)>0

Welche Feiertage bei Ihnen gelten, tragen Sie selbst ein; die Tabelle kennt sie nicht.

Schritt 4: Urlaubstage markieren

Tippen Sie an jedem Urlaubstag ein „U“ in die Zelle der Person. Mit der Datenüberprüfung können Sie die erlaubten Eingaben auf „U“ beschränken. Eine bedingte Formatierung („Zellwert gleich U“) färbt die Zellen grün.

Entscheiden Sie vorab, was bei Ihnen als Urlaubstag zählt. Wer von Montag bis Freitag frei hat, bekommt fünf Markierungen, nicht sieben. Jedes U zählt als ein Tag, also markieren Sie nur Tage, die vom Anspruch abgehen sollen. Halbe Tage sind in diesem einfachen Modell nicht vorgesehen; Sie können dafür ein zweites Zeichen „H“ einführen und mit 0,5 zählen (siehe Schritt 5).

Schritt 5: Genommene Tage und Resturlaub

In D4 zählen Sie die U der Zeile:

=ZÄHLENWENN(F4:NG4;"U")

In E4 steht der Rest:

=B4+C4-D4

Ein negativer Wert zeigt, dass mehr Tage eingetragen sind, als Anspruch besteht. Eine Regel „Zellwert kleiner als 0“ färbt ihn rot. Mit halben Tagen lautet D4:

=ZÄHLENWENN(F4:NG4;"U")+0,5*ZÄHLENWENN(F4:NG4;"H")

Wie Urlaubsansprüche zu berechnen sind, ob Resturlaub übertragen wird oder verfällt und wie Teilzeit oder Eintritt im Jahr wirken, regeln Gesetz, Tarif und Arbeitsvertrag. Die Tabelle rechnet nur mit den Zahlen, die Sie eintragen. Im Zweifel lassen Sie sich fachkundig beraten.

Schritt 6: Überschneidungen erkennen

Der Nutzen für ein kleines Team liegt in der Frage, wer an welchem Tag fehlt. Fügen Sie unter der letzten Mitarbeiterzeile (hier Zeile 18) eine Summenzeile ein:

=ZÄHLENWENN(F4:F18;"U")

Kopieren Sie sie über alle Datumsspalten. Sie zeigt die Zahl der Urlauber je Tag. In einer weiteren Zeile ermitteln Sie die Anwesenden:

=ANZAHL2($A$4:$A$18)-F19

ANZAHL2 zählt die gefüllten Namenszellen, also die Teamgröße; F19 ist die Summenzeile mit den Abwesenden. Legen Sie Ihre gewünschte Mindestzahl in B21 ab und färben Sie die Anwesenden-Zeile per Regel =F20<$B$21 rot, wenn der Wert unterschritten wird.

Beachten Sie: Diese Auswertung erfasst nur eingetragenen Urlaub, nicht Krankheit oder freie Tage laut Dienstplan. Für eine realistische Besetzung stellen Sie den Urlaubsplan neben den Schichtplan.

Schritt 7: Praktische Hinweise für den Alltag

• Ein Blatt pro Jahr. Kopieren Sie zum Jahreswechsel das Blatt, ändern Sie das Jahr in B1, löschen Sie die U und tragen Sie gegebenenfalls den Resturlaub als Übertrag ein.
• Eine Person pflegt den Plan. Mehrere gleichzeitige Bearbeiter führen schnell zu Widersprüchen.
• Anträge getrennt festhalten. Der Planer zeigt einen Stand, keine Genehmigung. Sie können „?“ für beantragte und „U“ für bestätigte Tage verwenden, denn gezählt wird nur „U“.
• Datenschutz beachten. Der Plan enthält personenbezogene Daten. Geben Sie die Datei nur an Personen weiter, die sie brauchen.
• Sicherungskopie. Speichern Sie regelmäßig eine Kopie unter neuem Namen.

Häufige Fehler

1. Der Zählbereich endet vor der letzten Datumsspalte, sodass Tage im Dezember fehlen.
2. Ein Leerzeichen hinter dem U („U “) wird nicht gezählt.
3. Datumszellen als Text eingegeben: dann funktioniert WOCHENTAG nicht. Nutzen Sie die Formeln aus Schritt 2.

Hinweis

Auf dieser Website finden Sie einen kostenlosen Stundenzettel-Rechner, der nur in Ihrem Browser rechnet: /stundenzettel-rechner. Das Dienstplan-Paket der ebats solutions UG (haftungsbeschränkt) enthält vier Excel-Arbeitsmappen (Stundenzettel, Dienstplan, Urlaubsplaner, Überstundenkonto) und eine Anleitung als PDF und HTML, zum Preis von 14,90 € inkl. 19 % USt. Es handelt sich um einen digitalen Download, es fallen keine Versandkosten an. Das Paket ist eine Rechenhilfe und prüft nicht, ob gesetzliche Vorgaben eingehalten sind.

Alle Ratgeber · Herausgeber: ebats solutions UG (haftungsbeschränkt)