Zeiterfassung in Excel: Wie Sie einen Stundenzettel erstellen

Immer mehr Unternehmen führen flexible Arbeitszeiten in ihrem Betrieb ein. Dies hat eindeutige Vorteile für die Produktivität und Mitarbeiterzufriedenheit, muss aber auch auf einer gewissen Vertrauensbasis fußen. Zu diesem Zweck sollte man auf ein System zur Arbeitszeiterfassung setzen. Dieses dient dem Angestellten als Arbeitsnachweis gegenüber dem Chef, der es wiederum zur Qualitätskontrolle und wöchentlichen bzw. monatlichen Abrechnung nutzt. Alles was Sie für einen elektronischen Stundenzettel brauchen, ist die Tabellenkalkulations-Software Excel – und unsere Schritt-für-Schritt-Anleitung.

Was macht eine professionelle Arbeitszeiterfassung in Excel aus? Diesbezüglich haben Arbeitnehmer, Arbeitgeber und auch der Gesetzgeber recht unterschiedliche Ansprüche.

Aus Sicht des Mitarbeiters muss ein Zeiterfassungssystem zuerst einmal leicht zu handhaben sein. Schließlich bedeutet das regelmäßige Einpflegen von Arbeits- und Pausenzeiten einen zusätzlichen Aufwand und zugleich eine Unterbrechung des Workflow. Allein anhand des Aufbaus und der Gestaltung eines Excel-Stundenzettels muss also sofort ersichtlich sein, welche Informationen von Belang sind und wo und wie die entsprechenden Daten eingetragen werden müssen. Eine klare Struktur, unterstützende Farbgebung und Beschränkung auf das Wesentliche können hierfür hilfreich sein. Grundsätzlich gilt: Je einfacher Sie es Ihren Angestellten machen, desto wahrscheinlicher ist es, dass die Arbeitszeiterfassung regelmäßig, pünktlich und korrekt erfolgt.

Da die Erfassung der Arbeitszeit für die Überprüfung der Mitarbeiterperformance als auch für die Gehaltsabrechnung relevant ist, gehen mit ihr einige komplexe Berechnungen einher. Diese sollte eine smarte Excel-Tabelle im Idealfall selbst durchführen, um Angestellten und Arbeitgebern Zeit und Arbeit zu ersparen. Entsprechende Funktionen können weitestgehend unsichtbar eingebunden werden und verringern künftig die Fehlerquote bei der regelmäßigen Zeiterfassung.

Das deutsche Arbeitszeitgesetz (ArbZG) legt zum Schutz der Gesundheit und Sicherheit von Arbeitnehmern bestimmte Höchstgrenzen für die Arbeitszeit fest – in der Regel sind dies 8 Stunden (§ 3 ArbZG). Geht die tatsächliche Arbeitszeit ausnahmsweise über diese Grenze hinaus, gilt laut § 16 ArbZG eine Aufzeichnungspflicht für den Arbeitgeber, der diese an den Arbeitnehmer delegieren kann. Für die Richtigkeit der gemachten Angaben ist jedoch allein der Arbeitgeber verantwortlich. Sämtliche Aufzeichnungen müssen zwei Jahre lang aufbewahrt werden. Welcher Form die Zeiterfassung sein soll, schreibt das Gesetz jedoch nicht vor.

Die Stundenzettel-Excel-Vorlage von IONOS können Sie unter folgendem Link kostenlos herunterladen und sofort verwenden:

In der folgenden Schritt-für-Schritt-Anleitung erklären wir Ihnen die einzelnen Handgriffe, mit denen Sie eine Stundenzettel-Vorlage erstellen können.

So statten Sie die Kopfzeile der Excel-Tabelle mit allen notwendigen Informationen aus, richten die Spalten ein und formatieren Ihren Stundenzettel übersichtlich:

1. Geben Sie in Zelle A1 den Namen des jeweiligen Mitarbeiters ein.
    
2. Geben Sie in Zelle D1 die tägliche SOLL-Arbeitszeit Ihres Mitarbeiters im Format hh:mm (h = Stunden, m = Minuten) ein, z. B. 08:00 (8 Uhr). Dieser Wert dient als Referenz für die Berechnungsformeln, die wir später in die Tabelle einbinden wollen.
    
3. Ein kleines Hilfsmittel für Ihre Mitarbeiter: eine aktuelle Datumsanzeige, anhand derer sie im Stundenzettel leichter die richtige Zeile finden. Geben Sie hierfür in Zelle H1 die Formel =HEUTE() ein und bestätigen Sie diese mit der Eingabetaste.
    
4. Eine weitere Hilfestellung: In Zeile 2 können Sie erläutern, an welche E-Mail-Adresse (z. B. der Personalabteilung) der Stundenzettel geschickt werden und welchen Betreff die E-Mail enthalten soll.
    
5. Geben Sie in Zeile 3 nacheinander die Spaltenbezeichnungen „Datum“, „Arbeitsbeginn“, „Pausenbeginn“, „Pausenende“, „Arbeitsende“, „Abwesenheit“, „IST-Arbeitsstunden“ und „Plus-/Minusstunden“ ein.
    
6. Formatieren Sie Ihre Tabelle nach Belieben. Nutzen Sie hierfür Fettungen sowie unterschiedliche Schriftarten, -größen und -farben in angemessener Weise. Einheitliche Spaltenbreiten schaffen Übersicht und sind optisch ansprechender. Verbundene Zellen und verschiedene Füllfarben können dabei helfen, einzelne Bereiche Ihrer tabellarischen Zeiterfassung voneinander abzugrenzen. Der Button „Format übertragen“ in der linken oberen Ecke des Menübands hilft Ihnen, den Gestaltungsprozess zu beschleunigen.
    
7. Zuletzt fixieren Sie die oberen drei Zeilen Ihrer Tabelle, damit diese für den Mitarbeiter auch beim Herunterscrollen ständig sichtbar bleiben. Markieren Sie hierfür die Zelle A4. Klicken Sie nun unter dem Reiter „Ansicht“ auf die Schaltfläche „Fenster fixieren“ und wählen Sie „Fenster fixieren“. Nun werden alle Zeilen fixiert, die sich oberhalb von Zelle A4 befinden.

So fügen Sie ein fortlaufendes Datum zu Ihrem Excel-Stundenzettel hinzu und markieren die Wochenenden farblich:

1. Geben Sie in Zelle A4 das erste Datum ein, für das die Arbeitszeit erfasst werden soll.
    
2. Klicken Sie unter dem Reiter „Start“ im Bereich „Zahl“ auf den kleinen Pfeil neben „Datum“ und wählen Sie das Format „Datum, lang“ aus. Das Datum wir nun in Langform samt Wochentag angezeigt.
    
3. Klicken Sie auf das kleine Quadrat in der rechten unteren Ecke der markierten Zelle A4 und halten Sie die Maustaste gedrückt. Ziehen Sie nun einen Bereich nach unten auf, der eine volle Sieben-Tage-Woche abdeckt.
    
4. Markieren Sie sämtliche Spalten. Klicken Sie dafür auf Spalte A, halten Sie die Maustaste gedrückt und ziehen Sie einen Bereich bis Spalte H auf.
    
5. Klicken Sie unter „Start“ > „Formatvorlagen“ auf die Schaltfläche „Bedingte Formatierung“.
    
6. Klicken Sie im folgenden Kontextmenü auf „Neue Regel…“. Ein separates Fenster öffnet sich.
    
7. Klicken Sie unter „Regeltyp auswählen:“ auf „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
    
8. Kopieren Sie unter „Werte formatieren, für die diese Formel wahr ist“ folgende Formel in das Dialogfeld: =WOCHENTAG($A1;2)>=6 (diese Formel bedeutet im Klartext: „Folgendes gilt für alle Samstage und Sonntage in Spalte A“).
    
9. Klicken Sie auf den Button „Formatieren…“. Ein weiteres Fenster öffnet sich.
    
10. Wählen Sie im Reiter „Ausfüllen“ eine beliebige Farbe für die Markierung der Wochenenden und klicken Sie auf „OK“. Bestätigen Sie die Eingabe erneut mit „OK“. Sämtliche Samstage und Sonntage werden nun farblich hervorgehoben.

Keine Sorge: Solch komplexe Formatierungen, Regeln und Formeln müssen Sie jeweils nur einmal einrichten. Um weitere Zellen mit denselben Eigenschaften zu versehen, brauchen Sie dann nur auf das kleine Quadrat in der rechten unteren Ecke Ihrer Zelle zu klicken und es nach unten zu ziehen.

So fügen Sie ein Drop-down-Menü zur Abwesenheits-Spalte Ihrer Stundenzettel-Tabelle hinzu:

1. Markieren Sie in Spalte F („Abwesenheit“) die erste Zelle, die zu einem Wochentag gehört. In unserem Fall ist dies F5.
    
2. Wählen Sie im Menüband den Reiter „Daten“.
    
3. Klicken Sie bei „Datentools“ auf die Schaltfläche „Datenüberprüfung“. Ein neues Fenster öffnet sich.
    
4. Wählen Sie im Reiter „Einstellungen“ unter „Gültigkeitskriterien“ > „Zulassen“ die Option „Liste“.
    
5. Entfernen Sie das Häkchen bei „Leere Zellen ignorieren“.
    
6. Im Dialogfeld „Quelle“ tippen Sie nun die Optionen ein, die im Drop-down-Menü verfügbar sein sollen. Denken Sie daran, die Optionen mittels Semikolon voneinander zu trennen. In unserem Beispiel sieht das wie folgt aus: Urlaub;Krankheit;Mobile Office.
    
7. Bestätigen Sie Ihre Eingabe mit „OK“.
    
8. Der Zelle in Spalte F wurde nun ein Drop-down-Menü hinzugefügt. Um auch die anderen Wochentage damit zu versehen, kopieren Sie es einfach mittels [Strg] + [C] / [Strg] + [V] hinein. Alternativ können Sie das kleine Quadrat in der rechten unteren Ecke der Zelle benutzen, das geht schneller.

So veranlassen Sie Excel dazu, die IST-Arbeitsstunden Ihres Mitarbeiters vollautomatisch auszurechnen:

1. Markieren Sie die Spalte G („IST-Arbeitsstunden“).
    
2. Unter „Start“ > „Zahl“ klicken Sie auf den kleinen Pfeil neben „Standard“ und wählen im Kontextmenü die Option „Mehr…“. Ein neues Fenster öffnet sich.
    
3. Unter „Kategorie“ wählen Sie „Benutzerdefiniert“ und geben im Dialogfeld unter „Typ“ folgende Formatierung ein: [hh]:mm (damit befehlen Sie Excel, die absolute Stundenzahl bei der Summierung zu berücksichtigen, anstatt nach 12:00 immer wieder bei null anzufangen).
    
4. Bestätigen Sie die Eingabe mit „OK“.
    
5. Kopieren Sie nun die folgende Formel in die erste Zelle in Spalte G, die zu einem Wochentag gehört (in unserem Fall ist dies G5): =WENN(ODER(F5="Urlaub";F5="Krankheit");$D$1;E5-(D5-C5)-B5).

Diese recht kompliziert aussehende Wenn-Dann-Formel teilt Excel Folgendes mit: „Wenn für ein Datum in Spalte F („Abwesenheit“) „Urlaub“ oder „Krankheit“ eingetragen wird, sollen die 8 SOLL-Tagesstunden aus Zelle D1 als IST-Arbeitsstunden verzeichnet werden (beim Eintrag „Mobile Office“ soll nichts passieren). Bleibt die Spalte F dagegen leer, sollen die Arbeitsstunden regulär berechnet werden, und zwar nach folgender Formel: E5 (Arbeitsende) - (D5 (Pausenende) - C5 (Pausenbeginn)) - B5 (Arbeitsbeginn).“ Wie gehabt können Sie diese Formel mittels des kleinen Quadrats auf alle anderen Wochentage übertragen. Die Formel $D$1 stellt dabei sicher, dass stets dieselben SOLL-Tagesstunden als Referenz herangezogen werden.

Nun müssen die IST-Arbeitsstunden noch zur Anzahl der Wochenstunden summiert werden:

1. In Spalte G, in der ersten Zelle, die zu einem Samstag gehört (bei uns ist das die G10), geben Sie folgende Formel ein: =SUMME(G5:G9).
    
2. Wahlweise können Sie auch im Reiter „Formeln“ auf die Schaltfläche „AutoSumme“ klicken und die Option „Summe“ wählen. Excel ermittelt dann automatisch die richtige Formel.

So bringen Sie Excel mit einem Trick dazu, sowohl Plus- als auch Minusstunden für einen Mitarbeiter auszurechnen:

1. Formatieren Sie die Spalte H („Plus-/Minusstunden“) als [hh]:mm – nutzen Sie hierfür dieselbe Vorgehensweise wie bei Spalte G („IST-Arbeitsstunden“).
    
2. Da Excel standardmäßig keine negativen Zeiten darstellen kann, müssen Sie nun einen kleinen Umweg in die „Optionen“ machen. Klicken Sie darin auf „Erweitert“ und suchen Sie nach dem Bereich „Beim Berechnen dieser Arbeitsmappe:“. Dort sollte sich die Angabe „1904-Datumswerte verwenden“ befinden, vor der Sie nun ein Häkchen setzen. Bestätigen Sie die Eingabe mit „OK“.
    
3. In unserem Fall geben wir in Zelle H5 nun folgende Formel ein: =G5-$D$1 (diese weist Excel an, die IST-Arbeitsstunden mit den SOLL-Arbeitsstunden zu verrechnen).
    
4. Übertragen Sie die Formel wie gehabt auf alle anderen Wochentage.
    
5. Für die Summierung der Plus-/Minusstunden können Sie erneut die Funktion „AutoSumme“ verwenden oder die Formel =SUMME(H5:H9) händisch eingeben.

Im Folgenden haben wir noch einmal alle praktischen Formeln zusammengefasst, die Sie fürs Excel-Zeiterfassung-Erstellen benötigen. Ahmen Sie unser Beispiel direkt nach, können Sie diese Formeln änderungslos übernehmen. Wollen Sie dagegen ein eigenes Stundenzettel-Design bauen, müssen Sie die Zellenangaben entsprechend anpassen. Orientieren Sie sich dafür auch am Screenshot weiter oben sowie an unserer Download-Vorlage.

Zuletzt erklären wir Ihnen, wie Sie Ihre fertige Excel-Zeiterfassungs-Vorlage wiederverwenden, mit einem partiellen Schreibschutz versehen und an Ihre Mitarbeiter übermitteln.

Das Ergebnis Ihrer Arbeit ist eine professionelle Stundenzettel-Excel-Vorlage, die im Moment aber nur eine einzelne Woche abdeckt. Um diese nun auf einen beliebigen Zeitraum zu erweitern, nutzen Sie wieder unseren gewohnten Trick: Markieren Sie die gesamte Woche samt aller dazugehörigen Spalten mit der Maus, klicken und halten Sie das kleine Quadrat in der rechten unteren Ecke des markierten Bereichs und ziehen Sie es nach unten. Wie Sie sehen, erkennt Excel automatisch ein wiederkehrendes Muster und repliziert sämtliche Formatierungen, Drop-down-Menüs, Formeln und Regeln für alle fortlaufenden Daten. Das erlaubt es Ihnen, Ihre Vorlage immer wiederzuverwenden.

Ihr Mitarbeiter trägt die Uhrzeiten seines Arbeits- und Pausenbeginns sowie Arbeits- und Pausenendes in den dafür vorgesehenen Spalten B, C, D, E im Format hh:mm (z. B. 08:30) ein bzw. deklariert seine Urlaubs- und Krankentage in Spalte F. Davon abgesehen soll er – weder unbeabsichtigt noch beabsichtigt – Änderungen am Inhalt und Aufbau des Stundenzettels vornehmen können. Zu diesem Zweck statten Sie Ihre Excel-Tabelle mit einem partiellen Schreibschutz aus.

Das funktioniert wie folgt:

1. Wenn das aktive Excel-Arbeitsblatt geschützt ist, verfügen sämtliche Zellen standardmäßig über den Status „Gesperrt“. Um nur einen partiellen Schreibschutz zu implementieren, müssen Sie diese Einstellung zuerst ändern.
    
2. Markieren Sie dafür die gesamte Tabelle, indem Sie auf den Schnittpunkt zwischen den Spalten und Zeilen (links oben) klicken.
    
3. Klicken Sie nun unter dem Reiter „Start“ auf den kleinen Pfeil in der rechten unteren Ecke des Bereichs „Ausrichtung“, um das Dialogfenster „Zellen formatieren“ zu öffnen. Sie können auch die Tastenkombination [Strg] + [1] verwenden.
    
4. Wählen Sie den Reiter „Schutz“ und entfernen Sie dort das Häkchen bei „Gesperrt“.
    
5. Bestätigen Sie die Eingabe mit „OK“.
    
6. Halten Sie die [Strg]-Taste gedrückt und markieren Sie mit der Maus sämtliche Zellen, die Sie schützen möchten. Wir empfehlen die Zeilen 1, 2 und 3 sowie Spalte A.
    
7. Kehren Sie zurück zum Fenster mit der „Gesperrt“-Option und fügen Sie das Häkchen wieder hinzu.

Leider bezieht diese Methode die Formeln in den Spalten G und H nicht mit ein. Hierfür müssen Sie wie folgt vorgehen:

1. Drücken Sie die Tastenkombination [Strg] + [G], um das Dialogfenster „Gehe zu“ aufzurufen.
    
2. Klicken Sie auf den Button „Inhalte…“.
    
3. Markieren Sie den Punkt „Formeln“ und bestätigen Sie mit „OK“. Excel markiert nun alle Zellen, die Formeln enthalten.
    
4. Gehe Sie nun wie in der vorangegangenen Anleitung vor, um sämtliche Formeln mit dem Status „Gesperrt“ zu versehen.

Bevor Sie den Stundenzettel an Ihre Mitarbeiter schicken, müssen Sie noch den Schreibschutz aktivieren:

1. Klicken Sie unter dem Reiter „Überprüfen“ auf die Schaltfläche „Blatt schützen“.
    
2. Entfernen Sie unter „Alle Benutzer dieses Arbeitsblattes dürfen:“ das Häkchen bei „Gesperrte Zellen auswählen“.
    
3. Wahlweise legen Sie ein Kennwort zum Schutz des Arbeitsblattes fest.
    
4. Klicken Sie auf „OK“ (wenn Sie ein Kennwort eingegeben haben, müssen Sie dieses noch einmal bestätigen).

Speichern Sie Ihren Stundenzettel im regulären Excel-Format und verschicken Sie ihn per E-Mail an Ihre Mitarbeiter. Denken Sie daran, eine Anleitung beizufügen, die die Prozedur der Arbeitszeiterfassung verständlich erläutert. Klären Sie u. a., an welche E-Mail-Adresse und in welchen Zeitabständen der Stundenzettel an die Personalabteilung zurückgesendet werden soll. Indem Sie ein festes Format zur Benennung der Excel-Dateien vorgeben (z. B. Vorname Name Kalenderwoche), können Sie die Stundenzettel später den jeweiligen Mitarbeitern zuteilen.