Ratgeber-Blog
Zweiundzwanzig Ratgeber für den Alltag mit Excel-Dienstplänen: mit XVERWEIS Pausenstaffeln ohne Sortierung, den jüngsten Eintrag einer Liste, Name, Bereich und Stunden in einer Formel, die Soll-Besetzung aus einer Matrix und Treffer nach Datum und Schicht zugleich nachschlagen, Schichtkürzel per WENN, verschachtelter WENN-Formel und WENNS in Stunden übersetzen und Wochenenden, Überstunden, Pausen und Nachtarbeit kennzeichnen, Stunden je Mitarbeiter, Schichtart und Monat sowie Nacht- und Feiertagsstunden mit SUMMEWENN und SUMMEWENNS summieren, Frühdienste, Wochenenddienste, Tagesbesetzung und Urlaubstage mit ZÄHLENWENN und ZÄHLENWENNS zählen, Schichtkürzel, Wochenenden, Feiertage und Grenzwerte per bedingter Formatierung automatisch einfärben, Schichtkürzel per SVERWEIS oder XVERWEIS in Uhrzeiten und Stunden übersetzen, Dropdown-Listen für Schichtkürzel per Datenüberprüfung erstellen, den Wochentag aus einem Datum per Zahlenformat, TEXT oder WOCHENTAG anzeigen, eine Anwesenheitsliste mit Monatsraster, Kürzeln und ZÄHLENWENN-Auswertung anlegen, eine tägliche Arbeitszeiterfassung mit Pausen- und Ruhezeitprüfung aufbauen, einen Einarbeitungsplan anlegen, der sich aus dem Startdatum selbst terminiert, einen Jahreskalender 2027 mit Feiertagen und Kalenderwochen per Formel aufbauen, einen Dienstplan von Grund auf bauen, ihn im Team ohne Versionschaos teilen, Feiertage und Wochenenden automatisch markieren, Arbeitsstunden inklusive Nachtschichten korrekt summieren, Nacht-, Sonntags- und Feiertagszuschläge berechnen, Urlaub und Abwesenheiten über das Jahr führen, ein Arbeitszeitkonto mit Soll, Ist und Übertrag führen, die Krankenquote nach Arbeitstagen oder Stunden auswerten, die häufigsten Fehler vermeiden und einschätzen, wann sich der Umstieg von der Vorlage auf eine App lohnt.
Der Blog behandelt die Praxisfragen hinter dem Excel-Dienstplan: welche Formeln Stunden, Nachtschichten und Zuschläge korrekt rechnen, welche Fehler in geteilten Dateien immer wieder auftreten und ab wann eine zentrale App wie Aplano belastbarer ist als eine verschickte Vorlage. Alle Artikel nennen ihre Quellen und sind auf dem Stand vom 12. November 2026.
Excel kann Dienstpläne. Es kann sie nur nicht bewachen. Eine Tabelle rechnet zuverlässig, solange die Zellen stimmen – sie merkt aber nicht, wenn eine Uhrzeit als Text eingetragen wurde, wenn eine Nachtschicht über Mitternacht plötzlich negative Stunden ergibt oder wenn drei Fassungen derselben Datei im Umlauf sind. Genau an diesen Stellen setzen die Artikel an.
Die Beiträge sind Werkstattbeschreibungen, keine Produktwerbung: konkrete Formeln, konkrete Zellbezüge, konkrete Fehlerbilder samt Ursache. Wo Excel die richtige Wahl bleibt, steht das ausdrücklich – und wo die Tabelle strukturell nichts mehr leisten kann, ebenfalls.
Alle Texte stammen aus der Redaktion, nennen ihre Quellen und tragen einen festen Prüfstand. Wie die Inhalte entstehen, nach welchen Kriterien geprüft wird und was ausdrücklich nicht geprüft wurde, steht auf der Methodikseite.
Themenschwerpunkte
Neu · Formeln
Die sechs Argumente mit wenn_nicht_gefunden, Vergleichsmodus -1 und 1 für Pausen- und Besetzungsstaffeln ohne sortierte Liste, Platzhalter, Suchmodus -1 für den jüngsten Eintrag, Name, Bereich und Stunden in einer überlaufenden Formel, Soll-Besetzung aus einer Matrix, mehrere Kriterien, XVERGLEICH – und #WERT!, #ÜBERLAUF! und #NAME? in Excel 2016 und 2019.
Formeln
Syntax mit Prüfung, Dann_Wert und Sonst_Wert, Vergleichsoperatoren und Anführungszeichen, verschachtelte WENN mit der richtigen Reihenfolge, UND und ODER, WENNS und ERSTERWERT ab Excel 2019, WENNFEHLER – und sechs Formeln für den Plan: Kürzel in Stunden, leere Zeilen, Wochenende, Überstunden, Pausenprüfung und Nachtarbeit.
Formeln
Syntax und die unterschiedliche Argumentreihenfolge von SUMMEWENN und SUMMEWENNS, Datumskriterien mit & und EDATUM, Platzhalter für Kürzelvarianten – und fünf Auswertungen aus einer Schichtliste: Stunden je Person, Kreuztabelle nach Schichtart, Monatsstunden, Nacht- und Feiertagsstunden, dazu #WERT!, Text-Zahlen und die Abgrenzung zu SUMMENPRODUKT und PivotTable.
Formeln
Syntax von ZÄHLENWENN und ZÄHLENWENNS, Kriterien mit Platzhaltern * und ?, Vergleichsoperatoren und Zellbezüge mit &, Groß- und Kleinschreibung, Leerzeichen und die 255-Zeichen-Grenze – und vier Formeln für den Plan: Frühdienste je Person, Wochenenddienste per Wochentag-Hilfszeile, Besetzung je Tag und Urlaubstage an Arbeitstagen.
Formatierung
Regeltypen von „Gleich…“ bis Datenbalken, Formelregeln mit relativen, absoluten und gemischten Bezügen, Regel-Manager, Rangfolge und Anhalten – und sechs Regeln für den Plan: Schichtkürzel F/S/N/U/K, Wochenenden per WOCHENTAG, Feiertage per ZÄHLENWENN, Ruhezeitkonflikte, mehr als 10 Stunden am Tag und Unterbesetzung.
Formeln
Syntax und die vier Argumente, warum FALSCH fast immer ins vierte Argument gehört, Schichtkürzel in Uhrzeiten und Nettostunden übersetzen, Namen per Personalnummer nachschlagen, eine Pausenstaffel nach § 4 ArbZG mit ungefährer Suche, #NV und verrutschte Bezüge, WENNNV statt WENNFEHLER – und wann XVERWEIS die bessere Wahl ist.
Dateneingabe
Auswahlliste per Datenüberprüfung in vier Schritten, Quelle als Bereich, Excel-Tabelle oder benannter Bereich, mitwachsende Listen mit EINDEUTIG und dem #-Operator, abhängige Dropdowns mit INDIREKT, Stopp statt Warnung – und Schichtfarben für F, S, N, U und K per bedingter Formatierung.
Datumsformeln
Wochentag per Zahlenformat TTT/TTTT, mit TEXT oder als Zahl mit WOCHENTAG und allen Rückgabetypen von 1 bis 17, Wochenenden per bedingter Formatierung einfärben, die Kopfzeile des Monatsplans aus einem Datum – und die Sprachfalle mit TTTT, dddd und [$-407] beim Teilen der Datei.
Anwesenheit
Ein Monatsraster, das sich aus einem Datum selbst aufbaut, Kürzel per Datenüberprüfung, Tage je Person mit ZÄHLENWENN, eine Musterspalte für Teilzeit, Wochenenden und Feiertage per bedingter Formatierung, ein Aushangblatt ohne Krankheitskürzel, eine Teilnehmerliste für Unterweisungen – und warum die Liste keine Arbeitszeiterfassung nach BAG 1 ABR 22/21 ersetzt.
Kennzahlen
Die Formel in vier Varianten – Arbeitstage, Stunden, Kalendertage, Kopfzahl –, die Abgrenzung zur Fehlzeitenquote, Krankheitstage über Monatsgrenzen mit NETTOARBEITSTAGE.INTL, Monats- und Jahresquote per SUMMEWENNS, Vergleichswerte 2025 mit Zählweise und die Regeln aus Art. 9 DSGVO und § 87 BetrVG.
Zeiterfassung
Eine Zeile pro Tag mit Beginn, Ende und Pause, die Netto-Arbeitszeit per REST auch über Mitternacht, Prüfspalten für die Pausen nach § 4 ArbZG und die elfstündige Ruhezeit, Blattschutz für die Formeln – und was die Aufzeichnungspflicht nach dem BAG-Beschluss von 2022 tatsächlich verlangt.
Einarbeitung
Phasen von der Vorbereitung bis zum Probezeitende, zehn Spalten mit Pate, Termin und Erledigungsdatum, Termine per ARBEITSTAG ab Startdatum, Fortschritt mit ZÄHLENWENNS, eine Ampel für Überfälliges – und Einarbeitungsschichten, die im Dienstplan nicht als volle Besetzung zählen.
Jahreskalender
Datumsreihe aus einer Jahreszahl, ISO-Kalenderwochen, alle Feiertage 2027 nach Bundesland als Liste, die Osterformel für Karfreitag bis Fronleichnam, Wochenenden und Feiertage per bedingter Formatierung — und 256 Arbeitstage bundesweit, gezählt mit NETTOARBEITSTAGE.INTL.
Stundenkonto
Monats-Soll mit NETTOARBEITSTAGE.INTL, Ist-Stunden mit REST, der Saldo in Dezimalstunden statt [h]:mm gegen das Rautenproblem — und ein Übertrag, der beim Kopieren des Monatsblatts nicht verrutscht.
Abwesenheiten
Eine Jahresmatrix statt zwölf Monatsblätter, Kürzel per Datenüberprüfung, Resturlaub mit SUMMENPRODUKT ohne Wochenenden und Feiertage – und eine Auswertungszeile, die Besetzungslücken zeigt, bevor der Antrag genehmigt ist.
Zusammenarbeit
Gemeinsame Dokumenterstellung statt Mail-Anhänge: die drei Voraussetzungen, was bei Offline-Konflikten passiert, wie der Versionsverlauf rettet, was der Blattschutz sichert – und wo die geteilte Datei strukturell endet.
Kalender
Feiertagsliste je Bundesland als benannter Bereich, zwei Regeln mit ZÄHLENWENN und WOCHENTAG, die richtige Regelreihenfolge mit „Anhalten falls wahr“ – und Sollstunden aus derselben Liste per NETTOARBEITSTAGE.INTL.
Aufbau
Von der Arbeitsmappe über Schichtlegende, Dropdown per Datenüberprüfung und SVERWEIS bis zu bedingter Formatierung, Summen und Druckbereich – der komplette Aufbau in sechs Schritten.
Zuschläge
Die steuerfreien Sätze nach § 3b EStG, die Grundlohngrenzen von 50 und 25 Euro und die Überschneidungsformel, die Nachtstunden über Mitternacht sauber zählt.
Formeln
Wie Sie mit REST, dem Format [h]:mm und einem Trick für Nachtschichten über Mitternacht die Wochenstunden zuverlässig berechnen.
Qualität
Von der Uhrzeit als Text über negative Stunden bis zu mehreren Dateiversionen: die typischen Stolperfallen und ihre saubere Lösung.
Entscheidung
Sechs klare Signale, an denen Excel an seine Grenze kommt — und was eine Dienstplan-App wie Aplano dann konkret übernimmt.
Die Beiträge folgen dem typischen Weg einer Excel-Planung: erst rechnen, dann die Zuschläge korrekt zuordnen, dann Fehler ausräumen, dann die Datei im Team verteilen – und zuletzt prüfen, ob das Werkzeug noch trägt.
| Wenn Sie sich das fragen… | …lesen Sie | Worauf der Artikel hinausläuft |
|---|---|---|
| „Wie suche ich mit XVERWEIS nach Staffeln, dem letzten Eintrag oder zwei Bedingungen – und warum zeigt die Formel #ÜBERLAUF!?“ | XVERWEIS in Excel | Genaue Suche als Standard mit 0 als Ersatzwert, Vergleichsmodus -1 für die Pausenstaffel nach § 4 ArbZG, Suchmodus -1 für das letzte Dienstende, verschachtelte Suche in der Besetzungsmatrix, multiplizierte Bedingungen – und freie Zielzellen außerhalb von Excel-Tabellen. |
| „Wie übersetze ich Schichtkürzel in Stunden und kennzeichne Wochenenden, Überstunden oder Nachtarbeit – und warum zeigt die Formel FALSCH?“ | WENN-Funktion in Excel | WENN mit Sonst_Wert und Zahlen ohne Anführungszeichen, UND/ODER für Pausen nach § 4 ArbZG, die strengste Bedingung zuerst, WENNS mit WAHR als Auffangpaar – und ab drei Ebenen lieber eine Legende mit SVERWEIS. |
| „Wie summiere ich Stunden je Mitarbeiter, Schichtart oder Monat – und warum zeigt SUMMEWENNS #WERT!?“ | SUMMEWENN in Excel | Eine Schichtliste mit Dezimalstunden als Datenbasis, SUMMEWENNS mit dem Summenbereich vorn und zwei Datumsgrenzen für den Monat, eigene Spalten für Nacht- und Feiertagsstunden – und gleich große Bereiche gegen #WERT! und verschobene Summen. |
| „Wie zähle ich Frühdienste, Wochenenddienste und Urlaubstage – und warum ist die Zahl zu klein?“ | ZÄHLENWENN in Excel | ZÄHLENWENN mit Kürzel-Kopfzeile und $-Bezügen, ZÄHLENWENNS mit Wochentag- und Feiertags-Hilfszeile, Besetzungszeilen unter dem Plan – und Leerzeichen als häufigste Ursache zu niedriger Zählungen. |
| „Wie färbt Excel Schichten, Wochenenden und zu lange Tage automatisch ein – und warum stimmen die Farben nicht?“ | Bedingte Formatierung in Excel | Formelregeln mit C$4 für Spalten und $F5 für Zeilen, die Regelreihenfolge im Manager mit Anhalten, Warnfarben für Ruhezeitkonflikte, mehr als 10 Stunden und Unterbesetzung – und Datenbalken für Stundenkonten. |
| „Wie holt Excel zu einem Schichtkürzel die Uhrzeiten und Stunden – und warum zeigt die Formel #NV?“ | SVERWEIS in Excel | SVERWEIS mit FALSCH und $-Bezügen auf eine Schichtlegende, WENNNV für leere Tage, INDEX/VERGLEICH für die Suche nach links – und XVERWEIS, wo alle mit Microsoft 365, Excel 2021 oder 2024 arbeiten. |
| „Wie baue ich ein Dropdown für Schichtkürzel – und warum stehen trotzdem falsche Werte im Plan?“ | Excel Dropdown erstellen | Datenüberprüfung mit Liste aus einer Excel-Tabelle, Fehlermeldung Stopp, abhängige Listen per INDIREKT, Farben per bedingter Formatierung – und „Ungültige Daten einkreisen“ gegen eingefügte Fremdwerte. |
| „Wie zeigt Excel den Wochentag zu einem Datum an – und warum färbt meine Regel den Freitag?“ | Excel: Wochentag aus Datum anzeigen | Zahlenformat TTT/TTTT für die Kopfzeile, TEXT für Beschriftungen, WOCHENTAG mit Typ 2 für Regeln wie =WOCHENTAG(C$3;2)>5 – und sprachneutrale Varianten für Dateien, die zwischen Excel-Versionen und Google Tabellen wandern. |
| „Wie baue ich eine Anwesenheitsliste in Excel – und was darf im Aushang stehen?“ | Anwesenheitsliste in Excel | Monatsraster aus einem Datum, feste Kürzel per Dropdown, ZÄHLENWENN je Person, Aushangblatt mit „abw.“ statt Krankheitskürzel – und die Abgrenzung zur Zeiterfassungspflicht. |
| „Wie berechne ich die Krankenquote – und womit kann ich sie vergleichen?“ | Krankenquote berechnen | Ausfall- gegen Solltage oder -stunden in derselben Einheit, Krankmeldungen per NETTOARBEITSTAGE.INTL auf Monate verteilt, Vergleichswerte von Destatis, BMG und DAK mit ihrer Zählweise – und die Datenschutzgrenzen einer personenbezogenen Auswertung. |
| „Wie erfasse ich Arbeitszeiten täglich in Excel – und prüfe dabei Pausen und Ruhezeiten?“ | Arbeitszeiterfassung in Excel | Beginn, Ende und tatsächliche Pause als Eingaben, Netto-Zeit per REST, Prüfspalten in ganzen Minuten für § 4 und § 5 ArbZG, gesperrte Formelspalten – und die Rechtslage nach BAG 1 ABR 22/21, § 16 ArbZG und § 17 MiLoG. |
| „Wie baue ich einen Einarbeitungsplan, der sich selbst terminiert?“ | Einarbeitungsplan-Vorlage in Excel | Startdatum und Probezeitende als benannte Zellen, Termine per ARBEITSTAG und EDATUM, Status aus dem Erledigungsdatum, die gesetzlichen Fristen aus ArbSchG, NachwG und BGB als feste Zeilen. |
| „Wie baue ich den Jahreskalender 2027 mit Feiertagen und Kalenderwochen?“ | Kalender 2027 in Excel erstellen | Eine Jahreszahl, eine Datumsreihe, eine benannte Feiertagsliste; Ostern per Formel, Arbeitstage per NETTOARBEITSTAGE.INTL. |
| „Wie fange ich mit einem Dienstplan in Excel überhaupt an?“ | Dienstplan in Excel erstellen | Drei Blätter, eine Schichtlegende, Dropdown per Datenüberprüfung, Stunden per SVERWEIS, Farben per bedingter Formatierung. |
| „Warum färbt Excel meinen Feiertag nicht ein?“ | Feiertage in Excel markieren | Excel kennt keine Feiertage: benannte Liste, zwei Regeln der bedingten Formatierung, richtige Reihenfolge im Regel-Manager. |
| „Warum zeigt mein Stundenkonto Rauten statt eines Minus?“ | Arbeitszeitkonto in Excel führen | Saldo in Dezimalstunden statt [h]:mm, Sollstunden per NETTOARBEITSTAGE.INTL, Übertrag über eine feste Startsaldo-Zelle. |
| „Warum steht bei meiner Nachtschicht ein negativer Wert?“ | Arbeitsstunden in Excel korrekt summieren | Zeitdifferenzen über Mitternacht brauchen REST, Summen über 24 Stunden das Zahlenformat [h]:mm. Der Artikel zeigt beides mit konkreten Formeln. |
| „Wie viele Stunden sind zuschlagspflichtig – und was davon steuerfrei?“ | Zuschläge in Excel berechnen | Die Sätze des § 3b EStG mit ihren Zeitfenstern, der gesetzliche Nachtzuschlag nach § 6 Abs. 5 ArbZG und eine Formel, die Nachtstunden über Mitternacht ohne Fallunterscheidung ermittelt. |
| „Warum stimmt die Wochensumme nicht?“ | Die häufigsten Fehler in Excel-Dienstplänen | Von der Uhrzeit als Text über pauschale Pausenabzüge bis zu konkurrierenden Dateiversionen – die typischen Stolperfallen mit jeweils einer sauberen Lösung. |
| „Wie viele Urlaubstage sind noch offen – und wer fehlt am selben Tag?“ | Urlaubsplaner in Excel bauen | Eine Jahresmatrix mit Kürzeln aus der Datenüberprüfung, SUMMENPRODUKT für die urlaubswirksamen Tage und eine Auswertungszeile, die Unterbesetzung markiert – plus die Punkte, die das BUrlG außerhalb der Tabelle regelt. |
| „Wie arbeiten mehrere Personen an derselben Datei?“ | Excel-Dienstplan im Team teilen | Gemeinsame Dokumenterstellung in OneDrive, Bearbeitungskonflikte bei Offline-Arbeit, Versionsverlauf als Rettungsanker und Blattschutz für die Formelspalten. |
| „Lohnt der Umstieg – und woran erkenne ich ihn?“ | Wann sich der Umstieg auf eine App lohnt | Sechs überprüfbare Signale statt eines Bauchgefühls, und was eine Dienstplan-App an dieser Stelle konkret übernimmt. |
Die Artikel beschreiben drei Ebenen desselben Problems. Auf der ersten Ebene geht es um Rechnen: Zeitwerte sind in Excel Bruchteile eines Tages, und fast alle Stundenfehler lassen sich darauf zurückführen, dass diese Eigenschaft übersehen wird. Wer REST und das Format [h]:mm beherrscht, hat die Mehrzahl der Fehlerbilder bereits ausgeschlossen – und mit der Überschneidungsformel aus dem Zuschlagsartikel auch die Zeitfenster für Nacht-, Sonntags- und Feiertagsstunden im Griff.
Die zweite Ebene betrifft die Datenqualität. Eine Formel ist nur so gut wie die Zelle, auf die sie zeigt. Uhrzeiten als Text, pauschal abgezogene Pausen, überschriebene Formelspalten und leere Pflichtfelder erzeugen Ergebnisse, die plausibel aussehen und trotzdem falsch sind – deshalb gehören Datenprüfung und Sichtkontrolle in jede Arbeitsmappe.
Die dritte Ebene lässt sich mit Excel nicht lösen: die Verteilung. Eine Datei kann nicht benachrichtigen, keine Rechte vergeben und keinen Tauschwunsch entgegennehmen. Ab hier ist die Frage nicht mehr, wie man besser rechnet, sondern ob das Werkzeug noch zur Aufgabe passt.
Wer diese drei Ebenen auseinanderhält, trifft die Werkzeugfrage nüchterner. Ein Rechenfehler ist kein Grund, die Software zu wechseln – er ist ein Grund, die Formel zu korrigieren. Umgekehrt hilft die beste Formel nicht gegen drei Dateiversionen im Umlauf. Die Artikel benennen deshalb jeweils, welcher Ebene ein Problem angehört und mit welchem Mittel es sich tatsächlich lösen lässt.
Passend dazu: Die Vorlagenübersicht liefert fertige Arbeitsmappen mit Formeln und Hinweisseite, der Dienstplan-Generator erzeugt ein Grundraster als XLSX oder CSV, und die Seite Rechtslage ordnet ein, wo eine Datei arbeitsrechtlich an ihre Grenze stößt.
Sie suchen die passende Datei? Die Vorlagenübersicht bündelt alle Downloads, der Dienstplan-Generator erstellt eine fertige Woche im Browser, und die Seite Aplano sachlich eingeordnet zeigt, wann sich der Wechsel auf eine App lohnt.
Fragen aus der Praxis
Der Blog wird anlassbezogen ergänzt und aktualisiert, nicht nach einem festen Redaktionsplan. Jeder Artikel trägt sein Aktualisierungsdatum, damit der Stand der Angaben erkennbar bleibt.
Die gezeigten Funktionen wie REST, SUMMEWENN und ZEITWERT sind Standardfunktionen und funktionieren in aktuellen Excel-Versionen sowie weitgehend in LibreOffice Calc. In Google Sheets heißen einzelne Funktionen anders, die Logik bleibt identisch.
Weil eine Endzeit von 06:30 rechnerisch kleiner ist als eine Startzeit von 22:00 und die Differenz negativ wird. Die Funktion REST fängt den Tageswechsel ab; Summen über 24 Stunden brauchen zusätzlich das Zahlenformat [h]:mm.
Nein. Die Beiträge fassen allgemeine gesetzliche Regeln zusammen und verlinken die amtlichen Fassungen. Tarifverträge, Betriebsvereinbarungen und branchenspezifische Ausnahmen können abweichen und sind im Einzelfall gesondert zu prüfen.
Ja. Unter Vorlagen stehen Wochen-, Monats-, 3-Schicht- und Urlaubspläne als XLSX mit Netto-Stundenformeln und eigener Hinweisseite bereit, dazu CSV-Fassungen für den Import und neun branchenspezifische Pakete.
Weil die Beiträge beschreiben, wo eine Tabelle strukturell an ihre Grenze kommt, und Aplano dabei als Beispiel für die digitale Alternative dient. Diese Einordnung ist redaktionell gesetzt; Tarifgrenzen und Kritikpunkte werden mitgenannt und auf der Methodikseite offengelegt.