Zeiterfassung in Microsoft Excel: Stundenzettel erstellen

Erstellen Sie Ihre eigene Excel-Vorlage, um Ihre Arbeitszeiten zu dokumentieren und Ihr Zeitkonto zu berechnen.

In diesem Kurs erstellen wir gemeinsam einen Stundenzettel, über den Sie Ihre Ist-, Soll- und Pausenzeiten erfassen, Ihr Zeitkonto, welches Minus- oder Überstunden aufzeigt, managen, Ihre Urlaubstage eintragen und das alles kumuliert über ein dynamisches, filterbares Dashboard abrufen können. Setzen Sie Datums- und Uhrzeitenformeln ein und kombinieren Sie wichtige Excel-Funktionen, z. B. Wenn, Wennfehler, Sverweis, Indirekt, Abs, Datwert und weitere. Lernen Sie mit negativen Uhrzeiten umzugehen und trotzdem Berechnungen damit durchzuführen. Dazu erhalten Sie jede Menge Excel-Material zum Nachlesen und Ausprobieren.

Ergänzungstutorial:

Wöchentliche Gesamt-Summen ergänzen

Um die Summen pro Woche zusammenzufassen, kann anhand des Datums der Wochentag hilfreich sein. Diesen ermitteln wir mit der Formel „Wochentag“: =WOCHENTAG(A17;2).

Die 7 in der Funktion steht für den Sonntag. Kombinieren wir die Wochentagsfunktion nun mit einer Wenn-Funktion, kann damit das Wochenende ermittelt und die gewünschte Berechnung, nämlich die Kumulierung der wöchentlichen Werte immer Sonntags, durchgeführt werden. Die vollständige Formeln lauten in diesem Fall:

=WENN(WOCHENTAG(A17;2)=7;ZÄHLENWENN(G11:G17;“ja“);““)  -> Wenn also der Datumswert einem Sonntag entspricht, dann soll Excel die Summe der Urlaubstage berechnen, ansonsten nichts anzeigen.

=WENN(WOCHENTAG(A17;2)=7;SUMME(H11:H17);““)  -> Wenn also der Datumswert einem Sonntag entspricht, dann soll Excel die Summe der Tageslöhne berechnen, ansonsten nichts anzeigen.

Nun gehen wir davon aus, dass wir immer 7 Zeilen kumulieren müssen, dem ist aber nicht so, wenn der Sonntag ein 01., 02., 03., 04., 05. oder 06. ist.

In den Zeilen 8 bis 13 muss also der Bezug abgeändert werden:

• Zeile 8: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G8;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H8);““)
• Zeile 9: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G9;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H9);““)
• Zeile 10: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G10;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H10);““)
• Zeile 11: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G11;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H11);““)
• Zeile 12: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G12;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H12);““)
• Zeile 13: =WENN(WOCHENTAG(A8;2)=7;ZÄHLENWENN(G8:G13;“ja“);““) sowie =WENN(WOCHENTAG(A8;2)=7;SUMME(H8:H13);““)

Und abschließend ist es sinnvoll eine WENNFEHLER-Funktion bei den unteren Formeln zu ergänzen, damit bei Monaten, die nicht über 31 Tage verfügen, kein #WERT angezeigt wird.

Jetzt müssen diese Berechnungen nur noch auf die anderen Monatsblätter und in die Übersicht übertragen werden. Dazu markieren wir die Zellen I7 bis J38 und kopieren sie, z. B. mit der Tastenkombination STRG+C.

Nun klicken wir den Reiter „Februar“ an, halten die Großschreibtaste (Shift) gedrückt und klicken den Reiter „Dezember“ an. Damit werden die Reiter Februar bis Dezember alle markiert.

Im Reiter „Februar“ klicken wir nun die Zelle I7 an und betätigen im Startregister den Button „Einfügen“ (oder die Tastenkombination STRG+V). Somit werden die kopierten Formeln aus dem Reiter „Januar“ automatisch an alle andern Tabellenblätter übertragen.

Zuletzt passen wir nur noch die Übersicht an und ergänzen die beiden neuen Spalten. Hierzu können wir die Formel aus der Spalte H kopieren und müssen lediglich jeweils den Index im SVerweis anpassen, damit die korrekte Spalte abgefragt wird.

Nicht vergessen: Auch unsere vergebenen Namen für die einzelnen Bereiche der Monate müssen wir um die 2 neuen Spalten im Namensmanager erweitern. Dazu passen wir die Bezüge jeweils von A8 bis J38 an.