Zeit­er­fas­sung in Excel: Wie Sie einen Stun­den­zet­tel erstellen

Immer mehr Un­ter­neh­men führen flexible Ar­beits­zei­ten in ihrem Betrieb ein. Dies hat ein­deu­ti­ge Vorteile für die Pro­duk­ti­vi­tät und Mit­ar­bei­ter­zu­frie­den­heit, muss aber auch auf einer gewissen Ver­trau­ens­ba­sis fußen. Zu diesem Zweck sollte man auf ein System zur Ar­beits­zeit­er­fas­sung setzen. Dieses dient dem An­ge­stell­ten als Ar­beits­nach­weis gegenüber dem Chef, der es wiederum zur Qua­li­täts­kon­trol­le und wö­chent­li­chen bzw. mo­nat­li­chen Ab­rech­nung nutzt. Alles was Sie für einen elek­tro­ni­schen Stun­den­zet­tel brauchen, ist die Ta­bel­len­kal­ku­la­ti­ons-Software Excel – und unsere Schritt-für-Schritt-Anleitung.

Was macht eine pro­fes­sio­nel­le Ar­beits­zeit­er­fas­sung in Excel aus? Dies­be­züg­lich haben Ar­beit­neh­mer, Ar­beit­ge­ber und auch der Ge­setz­ge­ber recht un­ter­schied­li­che Ansprüche.

Aus Sicht des Mit­ar­bei­ters muss ein Zeit­er­fas­sungs­sys­tem zuerst einmal leicht zu handhaben sein. Schließ­lich bedeutet das re­gel­mä­ßi­ge Ein­pfle­gen von Arbeits- und Pau­sen­zei­ten einen zu­sätz­li­chen Aufwand und zugleich eine Un­ter­bre­chung des Workflow. Allein anhand des Aufbaus und der Ge­stal­tung eines Excel-Stun­den­zet­tels muss also sofort er­sicht­lich sein, welche In­for­ma­tio­nen von Belang sind und wo und wie die ent­spre­chen­den Daten ein­ge­tra­gen werden müssen. Eine klare Struktur, un­ter­stüt­zen­de Farb­ge­bung und Be­schrän­kung auf das We­sent­li­che können hierfür hilfreich sein. Grund­sätz­lich gilt: Je einfacher Sie es Ihren An­ge­stell­ten machen, desto wahr­schein­li­cher ist es, dass die Ar­beits­zeit­er­fas­sung re­gel­mä­ßig, pünktlich und korrekt erfolgt.

Da die Erfassung der Ar­beits­zeit für die Über­prü­fung der Mit­ar­bei­ter­per­for­mance als auch für die Ge­halts­ab­rech­nung relevant ist, gehen mit ihr einige komplexe Be­rech­nun­gen einher. Diese sollte eine smarte Excel-Tabelle im Idealfall selbst durch­füh­ren, um An­ge­stell­ten und Ar­beit­ge­bern Zeit und Arbeit zu ersparen. Ent­spre­chen­de Funk­tio­nen können wei­test­ge­hend un­sicht­bar ein­ge­bun­den werden und ver­rin­gern künftig die Feh­ler­quo­te bei der re­gel­mä­ßi­gen Zeit­er­fas­sung.

Das deutsche Ar­beits­zeit­ge­setz (ArbZG) legt zum Schutz der Ge­sund­heit und Si­cher­heit von Ar­beit­neh­mern bestimmte Höchst­gren­zen für die Ar­beits­zeit fest – in der Regel sind dies 8 Stunden (§ 3 ArbZG). Geht die tat­säch­li­che Ar­beits­zeit aus­nahms­wei­se über diese Grenze hinaus, gilt laut § 16 ArbZG eine Auf­zeich­nungs­pflicht für den Ar­beit­ge­ber, der diese an den Ar­beit­neh­mer de­le­gie­ren kann. Für die Rich­tig­keit der gemachten Angaben ist jedoch allein der Ar­beit­ge­ber ver­ant­wort­lich. Sämtliche Auf­zeich­nun­gen müssen zwei Jahre lang auf­be­wahrt werden. Welcher Form die Zeit­er­fas­sung sein soll, schreibt das Gesetz jedoch nicht vor.

Die Stun­den­zet­tel-Excel-Vorlage von IONOS können Sie unter folgendem Link kostenlos her­un­ter­la­den und sofort verwenden:

In der folgenden Schritt-für-Schritt-Anleitung erklären wir Ihnen die einzelnen Hand­grif­fe, mit denen Sie eine Stun­den­zet­tel-Vorlage erstellen können.

So statten Sie die Kopfzeile der Excel-Tabelle mit allen not­wen­di­gen In­for­ma­tio­nen aus, richten die Spalten ein und for­ma­tie­ren Ihren Stun­den­zet­tel über­sicht­lich:

1. Geben Sie in Zelle A1 den Namen des je­wei­li­gen Mit­ar­bei­ters ein.
    
2. Geben Sie in Zelle D1 die tägliche SOLL-Ar­beits­zeit Ihres Mit­ar­bei­ters im Format hh:mm (h = Stunden, m = Minuten) ein, z. B. 08:00 (8 Uhr). Dieser Wert dient als Referenz für die Be­rech­nungs­for­meln, die wir später in die Tabelle einbinden wollen.
    
3. Ein kleines Hilfs­mit­tel für Ihre Mit­ar­bei­ter: eine aktuelle Da­tumsan­zei­ge, anhand derer sie im Stun­den­zet­tel leichter die richtige Zeile finden. Geben Sie hierfür in Zelle H1 die Formel =HEUTE() ein und be­stä­ti­gen Sie diese mit der Ein­ga­be­tas­te.
    
4. Eine weitere Hil­fe­stel­lung: In Zeile 2 können Sie erläutern, an welche E-Mail-Adresse (z. B. der Per­so­nal­ab­tei­lung) der Stun­den­zet­tel geschickt werden und welchen Betreff die E-Mail enthalten soll.
    
5. Geben Sie in Zeile 3 nach­ein­an­der die Spal­ten­be­zeich­nun­gen „Datum“, „Ar­beits­be­ginn“, „Pau­sen­be­ginn“, „Pau­se­n­en­de“, „Ar­beits­en­de“, „Ab­we­sen­heit“, „IST-Ar­beits­stun­den“ und „Plus-/Mi­nus­stun­den“ ein.
    
6. For­ma­tie­ren Sie Ihre Tabelle nach Belieben. Nutzen Sie hierfür Fettungen sowie un­ter­schied­li­che Schrift­ar­ten, -größen und -farben in an­ge­mes­se­ner Weise. Ein­heit­li­che Spal­ten­brei­ten schaffen Übersicht und sind optisch an­spre­chen­der. Ver­bun­de­ne Zellen und ver­schie­de­ne Füll­far­ben können dabei helfen, einzelne Bereiche Ihrer ta­bel­la­ri­schen Zeit­er­fas­sung von­ein­an­der ab­zu­gren­zen. Der Button „Format über­tra­gen“ in der linken oberen Ecke des Menübands hilft Ihnen, den Ge­stal­tungs­pro­zess zu be­schleu­ni­gen.
    
7. Zuletzt fixieren Sie die oberen drei Zeilen Ihrer Tabelle, damit diese für den Mit­ar­bei­ter auch beim Her­un­ter­scrol­len ständig sichtbar bleiben. Markieren Sie hierfür die Zelle A4. Klicken Sie nun unter dem Reiter „Ansicht“ auf die Schalt­flä­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 fort­lau­fen­des Datum zu Ihrem Excel-Stun­den­zet­tel hinzu und markieren die Wo­chen­en­den farblich:

1. Geben Sie in Zelle A4 das erste Datum ein, für das die Ar­beits­zeit 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 mar­kier­ten 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“ > „For­mat­vor­la­gen“ auf die Schalt­flä­che „Bedingte For­ma­tie­rung“.
    
6. Klicken Sie im folgenden Kon­text­me­nü auf „Neue Regel…“. Ein separates Fenster öffnet sich.
    
7. Klicken Sie unter „Regeltyp auswählen:“ auf „Formel zur Er­mitt­lung der zu for­ma­tie­ren­den Zellen verwenden“.
    
8. Kopieren Sie unter „Werte for­ma­tie­ren, für die diese Formel wahr ist“ folgende Formel in das Dia­log­feld: =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 „For­ma­tie­ren…“. Ein weiteres Fenster öffnet sich.
    
10. Wählen Sie im Reiter „Ausfüllen“ eine beliebige Farbe für die Mar­kie­rung der Wo­chen­en­den und klicken Sie auf „OK“. Be­stä­ti­gen Sie die Eingabe erneut mit „OK“. Sämtliche Samstage und Sonntage werden nun farblich her­vor­ge­ho­ben.

Keine Sorge: Solch komplexe For­ma­tie­run­gen, Regeln und Formeln müssen Sie jeweils nur einmal ein­rich­ten. Um weitere Zellen mit denselben Ei­gen­schaf­ten 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 Ab­we­sen­heits-Spalte Ihrer Stun­den­zet­tel-Tabelle hinzu:

1. Markieren Sie in Spalte F („Ab­we­sen­heit“) 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 „Da­ten­tools“ auf die Schalt­flä­che „Da­ten­über­prü­fung“. Ein neues Fenster öffnet sich.
    
4. Wählen Sie im Reiter „Ein­stel­lun­gen“ unter „Gül­tig­keits­kri­te­ri­en“ > „Zulassen“ die Option „Liste“.
    
5. Entfernen Sie das Häkchen bei „Leere Zellen igno­rie­ren“.
    
6. Im Dia­log­feld „Quelle“ tippen Sie nun die Optionen ein, die im Drop-down-Menü verfügbar sein sollen. Denken Sie daran, die Optionen mittels Semikolon von­ein­an­der zu trennen. In unserem Beispiel sieht das wie folgt aus: Urlaub;Krankheit;Mobile Office.
    
7. Be­stä­ti­gen Sie Ihre Eingabe mit „OK“.
    
8. Der Zelle in Spalte F wurde nun ein Drop-down-Menü hin­zu­ge­fügt. Um auch die anderen Wo­chen­ta­ge damit zu versehen, kopieren Sie es einfach mittels [Strg] + [C] / [Strg] + [V] hinein. Al­ter­na­tiv können Sie das kleine Quadrat in der rechten unteren Ecke der Zelle benutzen, das geht schneller.

So ver­an­las­sen Sie Excel dazu, die IST-Ar­beits­stun­den Ihres Mit­ar­bei­ters voll­au­to­ma­tisch aus­zu­rech­nen:

1. Markieren Sie die Spalte G („IST-Ar­beits­stun­den“).
    
2. Unter „Start“ > „Zahl“ klicken Sie auf den kleinen Pfeil neben „Standard“ und wählen im Kon­text­me­nü die Option „Mehr…“. Ein neues Fenster öffnet sich.
    
3. Unter „Kategorie“ wählen Sie „Be­nut­zer­de­fi­niert“ und geben im Dia­log­feld unter „Typ“ folgende For­ma­tie­rung ein: [hh]:mm (damit befehlen Sie Excel, die absolute Stun­den­zahl bei der Sum­mie­rung zu be­rück­sich­ti­gen, anstatt nach 12:00 immer wieder bei null an­zu­fan­gen).
    
4. Be­stä­ti­gen 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 kom­pli­ziert aus­se­hen­de Wenn-Dann-Formel teilt Excel Folgendes mit: „Wenn für ein Datum in Spalte F („Ab­we­sen­heit“) „Urlaub“ oder „Krankheit“ ein­ge­tra­gen wird, sollen die 8 SOLL-Ta­ges­stun­den aus Zelle D1 als IST-Ar­beits­stun­den ver­zeich­net werden (beim Eintrag „Mobile Office“ soll nichts passieren). Bleibt die Spalte F dagegen leer, sollen die Ar­beits­stun­den regulär berechnet werden, und zwar nach folgender Formel: E5 (Ar­beits­en­de) - (D5 (Pau­se­n­en­de) - C5 (Pau­sen­be­ginn)) - B5 (Ar­beits­be­ginn).“ Wie gehabt können Sie diese Formel mittels des kleinen Quadrats auf alle anderen Wo­chen­ta­ge über­tra­gen. Die Formel $D$1 stellt dabei sicher, dass stets dieselben SOLL-Ta­ges­stun­den als Referenz her­an­ge­zo­gen werden.

Nun müssen die IST-Ar­beits­stun­den noch zur Anzahl der Wo­chen­stun­den 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 Schalt­flä­che „AutoSumme“ klicken und die Option „Summe“ wählen. Excel ermittelt dann au­to­ma­tisch die richtige Formel.

So bringen Sie Excel mit einem Trick dazu, sowohl Plus- als auch Mi­nus­stun­den für einen Mit­ar­bei­ter aus­zu­rech­nen:

1. For­ma­tie­ren Sie die Spalte H („Plus-/Mi­nus­stun­den“) als [hh]:mm – nutzen Sie hierfür dieselbe Vor­ge­hens­wei­se wie bei Spalte G („IST-Ar­beits­stun­den“).
    
2. Da Excel stan­dard­mä­ßig keine negativen Zeiten dar­stel­len 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 Ar­beits­map­pe:“. Dort sollte sich die Angabe „1904-Da­tums­wer­te verwenden“ befinden, vor der Sie nun ein Häkchen setzen. Be­stä­ti­gen 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-Ar­beits­stun­den mit den SOLL-Ar­beits­stun­den zu ver­rech­nen).
    
4. Über­tra­gen Sie die Formel wie gehabt auf alle anderen Wo­chen­ta­ge.
    
5. Für die Sum­mie­rung der Plus-/Mi­nus­stun­den können Sie erneut die Funktion „AutoSumme“ verwenden oder die Formel =SUMME(H5:H9) händisch eingeben.

Im Folgenden haben wir noch einmal alle prak­ti­schen Formeln zu­sam­men­ge­fasst, die Sie fürs Excel-Zeit­er­fas­sung-Erstellen benötigen. Ahmen Sie unser Beispiel direkt nach, können Sie diese Formeln än­de­rungs­los über­neh­men. Wollen Sie dagegen ein eigenes Stun­den­zet­tel-Design bauen, müssen Sie die Zel­len­an­ga­ben ent­spre­chend anpassen. Ori­en­tie­ren Sie sich dafür auch am Screen­shot weiter oben sowie an unserer Download-Vorlage.

Zuletzt erklären wir Ihnen, wie Sie Ihre fertige Excel-Zeit­er­fas­sungs-Vorlage wie­der­ver­wen­den, mit einem par­ti­el­len Schreib­schutz versehen und an Ihre Mit­ar­bei­ter über­mit­teln.

Das Ergebnis Ihrer Arbeit ist eine pro­fes­sio­nel­le Stun­den­zet­tel-Excel-Vorlage, die im Moment aber nur eine einzelne Woche abdeckt. Um diese nun auf einen be­lie­bi­gen Zeitraum zu erweitern, nutzen Sie wieder unseren gewohnten Trick: Markieren Sie die gesamte Woche samt aller da­zu­ge­hö­ri­gen Spalten mit der Maus, klicken und halten Sie das kleine Quadrat in der rechten unteren Ecke des mar­kier­ten Bereichs und ziehen Sie es nach unten. Wie Sie sehen, erkennt Excel au­to­ma­tisch ein wie­der­keh­ren­des Muster und re­pli­ziert sämtliche For­ma­tie­run­gen, Drop-down-Menüs, Formeln und Regeln für alle fort­lau­fen­den Daten. Das erlaubt es Ihnen, Ihre Vorlage immer wie­der­zu­ver­wen­den.

Ihr Mit­ar­bei­ter trägt die Uhrzeiten seines Arbeits- und Pau­sen­be­ginns sowie Arbeits- und Pau­se­n­en­des in den dafür vor­ge­se­he­nen Spalten B, C, D, E im Format hh:mm (z. B. 08:30) ein bzw. de­kla­riert seine Urlaubs- und Kran­ken­ta­ge in Spalte F. Davon abgesehen soll er – weder un­be­ab­sich­tigt noch be­ab­sich­tigt – Än­de­run­gen am Inhalt und Aufbau des Stun­den­zet­tels vornehmen können. Zu diesem Zweck statten Sie Ihre Excel-Tabelle mit einem par­ti­el­len Schreib­schutz aus.

Das funk­tio­niert wie folgt:

1. Wenn das aktive Excel-Ar­beits­blatt geschützt ist, verfügen sämtliche Zellen stan­dard­mä­ßig über den Status „Gesperrt“. Um nur einen par­ti­el­len Schreib­schutz zu im­ple­men­tie­ren, müssen Sie diese Ein­stel­lung zuerst ändern.
    
2. Markieren Sie dafür die gesamte Tabelle, indem Sie auf den Schnitt­punkt 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 „Aus­rich­tung“, um das Dia­log­fens­ter „Zellen for­ma­tie­ren“ zu öffnen. Sie können auch die Tas­ten­kom­bi­na­ti­on [Strg] + [1] verwenden.
    
4. Wählen Sie den Reiter „Schutz“ und entfernen Sie dort das Häkchen bei „Gesperrt“.
    
5. Be­stä­ti­gen 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 Tas­ten­kom­bi­na­ti­on [Strg] + [G], um das Dia­log­fens­ter „Gehe zu“ auf­zu­ru­fen.
    
2. Klicken Sie auf den Button „Inhalte…“.
    
3. Markieren Sie den Punkt „Formeln“ und be­stä­ti­gen Sie mit „OK“. Excel markiert nun alle Zellen, die Formeln enthalten.
    
4. Gehe Sie nun wie in der vor­an­ge­gan­ge­nen Anleitung vor, um sämtliche Formeln mit dem Status „Gesperrt“ zu versehen.

Bevor Sie den Stun­den­zet­tel an Ihre Mit­ar­bei­ter schicken, müssen Sie noch den Schreib­schutz ak­ti­vie­ren:

1. Klicken Sie unter dem Reiter „Über­prü­fen“ auf die Schalt­flä­che „Blatt schützen“.
    
2. Entfernen Sie unter „Alle Benutzer dieses Ar­beits­blat­tes dürfen:“ das Häkchen bei „Gesperrte Zellen auswählen“.
    
3. Wahlweise legen Sie ein Kennwort zum Schutz des Ar­beits­blat­tes fest.
    
4. Klicken Sie auf „OK“ (wenn Sie ein Kennwort ein­ge­ge­ben haben, müssen Sie dieses noch einmal be­stä­ti­gen).

Speichern Sie Ihren Stun­den­zet­tel im regulären Excel-Format und ver­schi­cken Sie ihn per E-Mail an Ihre Mit­ar­bei­ter. Denken Sie daran, eine Anleitung bei­zu­fü­gen, die die Prozedur der Ar­beits­zeit­er­fas­sung ver­ständ­lich erläutert. Klären Sie u. a., an welche E-Mail-Adresse und in welchen Zeit­ab­stän­den der Stun­den­zet­tel an die Per­so­nal­ab­tei­lung zu­rück­ge­sen­det werden soll. Indem Sie ein festes Format zur Benennung der Excel-Dateien vorgeben (z. B. Vorname Name Ka­len­der­wo­che), können Sie die Stun­den­zet­tel später den je­wei­li­gen Mit­ar­bei­tern zuteilen.