Dienstplan in Excel: Schichtplan und Arbeitsplan selbst erstellen

Kurz gesagt: Je Person eine Zeile, je Tag eine Spalte, in jeder Zelle ein Schichtkürzel aus einem Dropdown. Ein zweites Blatt legt fest, wann jede Schicht beginnt und endet. Die Wochenstunden rechnet =SUMMENPRODUKT(ZÄHLENWENN(C6:I6;Kuerzel)*Schichtstunden), die Besetzung je Tag ZÄHLENWENN, und eine bedingte Formatierung warnt, wenn zwischen zwei Schichten weniger als elf Stunden liegen.

Vorwissen: Mit Uhrzeiten rechnen, ZÄHLENWENN und gemischte Bezüge. SUMMENPRODUKT wird bei der Übung erklärt.

Probier es gleich aus

Bevor der Plan Stunden zählen kann, braucht jede Schicht ihre Dauer. Bei der Nachtschicht von 22 bis 6 Uhr liegt das Ende vor dem Beginn, darauf muss die Formel vorbereitet sein.

Aufgabe Berechne in E2 die Arbeitsstunden der Schicht als Dezimalzahl: Ende minus Beginn, abzüglich der Pause in Minuten. Die Formel wird bis E5 ausgefüllt und muss auch für die Nachtschicht über Mitternacht stimmen.

	A	B	C	D	E
1	Schicht	Beginn	Ende	Pause (Min.)	Stunden
2	Früh	6:00	14:00	30
3	Spät	14:00	22:00	30
4	Nacht	22:00	6:00	30
5	Tagdienst	8:00	16:30	30

Dienstplan-Vorlage kostenlos herunterladen

Die Vorlage enthält eine Woche für bis zu zwölf Personen, Schichtkürzel per Dropdown in Farbe, geplante Stunden und Differenz zum Soll, die Besetzung je Schicht mit Mindestbesetzung, eine Warnung bei zu kurzer Ruhezeit, ein Blatt mit den Schichtzeiten und eine Anleitung.

So ist der Dienstplan aufgebaut

Zelle oder Spalte	Inhalt	Eingabe oder Formel
B2	Ein Datum der Woche	Eingabe, etwa 04.01.2027
C5 bis I5	Montag bis Sonntag	=B2-WOCHENTAG(B2;2)+1, daneben jeweils +1
E2	Kalenderwoche	=ISOKALENDERWOCHE(C5)
A, B	Name, Soll-Stunden pro Woche	Eingaben
C bis I	Schicht je Tag	Kürzel aus dem Dropdown, leer = frei
J	Geplante Stunden	=SUMMENPRODUKT(ZÄHLENWENN(C6:I6;Kuerzel)*Schichtstunden)
K	Differenz zum Soll	=J6-B6

Das Blatt Schichten enthält je Kürzel Beginn, Ende und Pause in Minuten, daraus rechnet Spalte F die Stunden mit =REST(D5-C5;1)*24-E5/60. Kuerzel und Schichtstunden sind Namen für die Spalten A und F dieses Blatts. So stehen die Schichtzeiten an einer einzigen Stelle: Beginnt die Frühschicht künftig um 5:30, änderst du eine Zelle, und alle Wochenstunden passen sich an. Das Dropdown in C6:I17 liest die Kürzel aus derselben Liste, wie in der Lektion Dropdown-Liste gezeigt.

Weil B2 jedes beliebige Datum der Woche annimmt, rechnet WOCHENTAG(B2;2) zurück auf den Montag: Typ 2 zählt Montag als 1 und Sonntag als 7. Die Kalenderwoche folgt der ISO-Norm, wie in Deutschland üblich.

Wochenstunden je Person mit SUMMENPRODUKT

Jede Person hat in der Woche verschiedene Schichten. Man könnte für jedes Kürzel einzeln zählen und mit seinen Stunden malnehmen. Kürzer geht es, wenn ZÄHLENWENN alle Kürzel auf einmal zählt: Mit einem Bereich als Kriterium liefert es eine Anzahl je Kürzel, und SUMMENPRODUKT multipliziert sie mit den Stunden und addiert.

Aufgabe Berechne in I2 die geplanten Stunden der Woche: Für jedes Kürzel in K2:K5 zählt, wie oft es in der Zeile steht, mal die Stunden der Schicht in L2:L5. Die Formel wird bis I5 ausgefüllt.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Mo	Di	Mi	Do	Fr	Sa	So	Stunden		Kürzel	Std.
2	Anna	F	F	F	F	F					F	7,5
3	Ben	S	S	S			S	S			S	7,5
4	Clara	N	N	N	N						N	7,5
5	David	T	T	F							T	8

Diese Übung gehört zu Premium.

Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.

Premium ansehen Schon Premium? Anmelden

Die erste Übung dieser Lektion ist kostenlos.

Urlaub (U) und krank (K) stehen ohne Uhrzeiten in der Liste und zählen deshalb 0 Stunden. Rechnet dein Betrieb Urlaubstage mit dem Soll an, trägst du bei U im Blatt Schichten die Stunden direkt in Spalte F ein. Für die tatsächlich gearbeiteten Stunden gibt es die Arbeitszeiterfassung.

Schichtplan: Besetzung je Schicht zählen

Unter dem Plan steht für jede Schicht und jeden Tag, wie viele Personen eingeteilt sind. Eine einzige Formel füllt die ganze Tabelle, wenn die Bezüge richtig gemischt sind: Die Zeilen der Mitarbeitenden bleiben fest, die Spalte des Tages wandert mit, und das Kürzel steht immer in Spalte A. Warum das funktioniert, erklärt die Lektion absolute Bezüge.

Aufgabe Zähle in B10, wie viele Personen am Montag die Schicht in A10 haben. Die Formel wird nach rechts bis Sonntag und nach unten für alle drei Schichten ausgefüllt (B10:H12).

	A	B	C	D	E	F	G	H
1	Name	Mo	Di	Mi	Do	Fr	Sa	So
2	Anna	F	F	F	F	F
3	Ben	S	S	S	S	S
4	Clara	N	N	N	N
5	David			S	F	F	F	F
6	Emma	T	T				N	N
7	Felix		N			N	S	S
8
9	Schicht	Mo	Di	Mi	Do	Fr	Sa	So
10	F
11	S
12	N

Diese Übung gehört zu Premium.

Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.

Premium ansehen Schon Premium? Anmelden

Die erste Übung dieser Lektion ist kostenlos.

In Spalte B trägst du die Mindestbesetzung ein. Eine bedingte Formatierung mit der Regel =C20<$B20 färbt jeden Tag rot, an dem zu wenige eingeteilt sind. Die Schichtkürzel selbst sind ebenfalls farbig: eine Regel je Kürzel, etwa =C6="N" für Blau.

Arbeitsplan ohne Schichten

Nicht jeder Betrieb arbeitet in Schichten. Für ein Büro, eine Praxis oder einen Laden reicht oft ein Arbeitsplan mit Tagdienst, Spätdienst und freien Tagen. Dafür änderst du nur das Blatt Schichten: Kürzel T für 8:00 bis 16:30, vielleicht H für einen halben Tag, und die übrigen Zeilen löschst du. Die Formeln im Plan bleiben gleich. Feste Wochenmuster kopierst du einfach: Zeile markieren, kopieren und in die nächste Woche einfügen. Für die Urlaubsplanung des Teams über das ganze Jahr ist der Urlaubsplaner besser geeignet.

Was das Arbeitszeitgesetz vorgibt

Für Arbeitnehmerinnen und Arbeitnehmer in Deutschland gilt das Arbeitszeitgesetz (ArbZG). Drei Regeln betreffen jeden Dienstplan:

• § 3 Arbeitszeit: höchstens acht Stunden je Werktag. Bis zu zehn Stunden sind erlaubt, wenn im Durchschnitt von sechs Kalendermonaten oder 24 Wochen acht Stunden je Werktag nicht überschritten werden.
• § 4 Ruhepausen: mindestens 30 Minuten bei mehr als sechs bis neun Stunden Arbeit, 45 Minuten bei mehr als neun Stunden. Länger als sechs Stunden hintereinander darf niemand ohne Pause arbeiten.
• § 5 Ruhezeit: nach dem Ende der täglichen Arbeitszeit mindestens elf Stunden ohne Unterbrechung. In Krankenhäusern, Pflege, Gastronomie und einigen weiteren Bereichen erlaubt das Gesetz Ausnahmen.

Die Vorlage prüft die Ruhezeit zwischen zwei Tagen: Für jede Zelle ab Dienstag vergleicht eine Regel das Ende der Schicht vom Vortag mit dem Beginn der heutigen. Liegen weniger als elf Stunden dazwischen, wird das Kürzel rot und fett. Das klassische Beispiel ist Spätschicht bis 22 Uhr und am nächsten Morgen Frühschicht ab 6 Uhr: nur acht Stunden. Im Blatt Schichten werden außerdem Schichten rot, die länger als zehn Stunden dauern oder eine zu kurze Pause haben. Tarifverträge, Ausnahmen und Sonderregeln prüft die Tabelle nicht; sie ersetzt keine Rechtsberatung.

Typische Fehler

Nachtschicht mit negativen Stunden

6:00 minus 22:00 ergibt ohne REST eine negative Zeit, die Excel als #### anzeigt. Mit REST(Ende-Beginn;1) stimmt jede Schicht über Mitternacht.

Kürzel mit Leerzeichen

„F “ mit Leerzeichen ist für ZÄHLENWENN etwas anderes als „F“, die Stunden fehlen dann. Das Dropdown verhindert solche Tippfehler.

Uhrzeiten mit Komma

6,30 ist für Excel die Zahl sechs Komma drei, keine Uhrzeit. Uhrzeiten immer mit Doppelpunkt eintippen. Mehr dazu in der Lektion Arbeitszeit berechnen.

Die Formeln auf Englisch

Deutsch	Englisch
SUMMENPRODUKT, ZÄHLENWENN	SUMPRODUCT, COUNTIF
REST, WOCHENTAG	MOD, WEEKDAY
ISOKALENDERWOCHE, SVERWEIS	ISOWEEKNUM, VLOOKUP

In welchen Excel-Versionen funktioniert das?

SUMMENPRODUKT, ZÄHLENWENN, REST, WOCHENTAG, Dropdowns und bedingte Formatierung gibt es in allen aktuellen Versionen. ISOKALENDERWOCHE führt Microsoft für Excel 2016 und neuer, Microsoft 365 und Excel für Mac auf.

Häufige Fragen

Alle Dateien findest du bei den Excel-Vorlagen.