Eine Arbeitszeittabelle erstellen: So funktioniert die Arbeitszeiterfassung mit Excel
19. März 2021 in Excel
Für jede entgeltliche Arbeitstätigkeit, von Minijob bis hin Vollzeitbeschäftigung ist eine exakte Erfassung der Arbeitszeit unerlässlich. Mit Hilfe von Excel haben Sie die Möglichkeit, Ihre geleistete Arbeitszeit, anfallende Pausen- und Urlaubszeiten exakt zu dokumentieren und automatisch berechnen zu lassen. Auf einen Blick erhalten Sie Auskunft über die wichtigen Eckdaten:
- an welchen Tagen habe ich gearbeitet
- wie viele Stunden habe ich gearbeitet
- welche Pausenzeiten wurden berücksichtigt
- wieviel habe ich verdient
In diesem Ratgeber erklären wir Ihnen, wie Sie eine übersichtliche Tabelle für Ihre Arbeitszeiterfassung in Microsoft Excel erstellen.
1. Das Grundgerüst für die Arbeitszeittabelle – Erstellen eines Erfassungsblattes
Das Erfassungsblatt dient Ihnen zur übersichtlichen Erfassung Ihrer Arbeits- und Abwesenheitszeiten. Dank Ihrer individuellen Excel-Zeiterfassung haben Sie jederzeit die Kontrolle über Ihre Arbeitsstunden und Ihren Lohn bzw. Sie können auf diesem Weg unkompliziert Arbeitszeitnachweise für Ihren Arbeitgeber erbringen.
a) Tabelle anlegen
1. Öffnen Sie ein leeres Excel-Tabellenblatt.
2. Geben Sie in Zeile 1 der Datei zunächst folgende Spaltenüberschriften ein:
- Datum
- Arbeitsbeginn
- Arbeitsende
- Pause
- Stunden
3. Tippen Sie anschließend Ihr gewünschtes Start-Datum für die Arbeitszeiterfassung in Zelle A2 Ihrer Excel-Datei ein.
b) Zellen manuell formatieren
Für eine bessere Darstellung der Daten in Ihrer Arbeitszeiterfassung können Sie die Zellen Ihrer Excel Arbeitszeittabelle manuell formatieren – beispielsweise die Datums-Zelle mit Angabe des Wochentages:
1. Die Zelle A2 ist markiert – erkennbar am grünen Rahmen. Drücken Sie die Tastenkombination STRG+1 und es öffnet sich das Dialogfenster Zellen Formatieren.
2. Wählen Sie die Kategorie „Benutzerdefiniert“ aus.
3. Geben Sie in das Text-Eingabefeld bei „Typ“ das Darstellungsformat „TTT TT.MM.JJJJ“.
Klicken Sie auf „OK“, um die Änderung auf Ihre Zelle zu übernehmen.
Markieren Sie als nächstes die Zelle A2 mit dem angepassten Darstellungsformat und bewegen Sie den Mauscursor an die rechte, untere Ecke der Zelle – bis sich der Mauscursor in ein kleines, schwarzes Plus-Symbol verwandelt.
Halten Sie jetzt die linke Maustaste gedrückt und ziehen Sie die Zelle A2 nach unten über die gesamte Spalte auf, bis Ihr gewünschtes Enddatum erreicht ist. Alle Zellen, die Sie auf diese Weise markieren, werden so automatisch mit dem Folgedatum versehen.
Das Ergebnis sieht folgendermaßen aus:
c) Formatierungsregeln erstellen
Für eine bessere Wochenübersicht haben Sie anschließend noch die Möglichkeit, die Wochenenden für Ihre Arbeitszeiterfassung in Excel farbig hervorzuheben. Das funktioniert folgendermaßen:
1. Markieren Sie großzügig ihr Tabellenblatt und klicken Sie unter der Registerkarte „Start“ auf die Schaltfläche „Bedingte Formatierung“. Wählen Sie anschließend den Reiter „Neue Regel…“ an, um eine neue Regel zu erstellen.
Ein Dialogfenster namens „Neue Formatierungsregel“ öffnet sich. Entscheiden Sie sich hier für die Option „Formel zur Ermittlung der zu formatierenden Zellen verwenden“. Im unteren Bereich des Fensters unter „Regelbeschreibung bearbeiten“ geben Sie in das Text-Eingabefeld bei „Werte formatieren, für die diese Formel wahr ist“ folgende Excel-Formel ein: „=WOCHENTAG($A1;2)>=6“.
Durch das Größer-gleich-Zeichen vor der Sechs erkennt Excel automatisch, dass damit alle Samstage und Sonntage gemeint sind.
2. Anschließend klicken Sie auf den Reiter „Formatieren…“. Es erscheint erneut ein Dialogfenster namens „Zellen formatieren“. Unter der Registerkarte „Ausfüllen“ können Sie auswählen, mit welcher Farbe Sie die Wochenenden kennzeichnen möchten. Klicken Sie auf „OK“, damit die Änderungen auf alle Samstage und Sonntage übernommen werden.
Das Ergebnis aus unserem Beispiel sieht daraufhin folgendermaßen aus:
3. Machen Sie damit weiter, alle weiteren Werte in Ihre Arbeitszeit-Tabelle einzutragen und nach Ihren Wünschen zu formatieren.
Anker Werbeblock
2. Arbeitsstunden mit Excel berechnen
a) Formeln auf die Excel Arbeitszeittabelle anwenden
Haben Sie Ihre Arbeits- und Pausenzeiten in die Tabelle eingetragen, können Sie damit fortfahren, die Arbeitsstunden mit Excel zu berechnen. Sie müssen dafür die Excel-Formel „=(C2-B2-D2)“ anwenden. Wie Sie für Ihre Arbeitszeiterfassung Excel-Formeln anwenden, können Sie in unserem Ratgeber „Rechnen mit Microsoft Excel 2016 – Wichtige Excel-Formeln, die Ihnen viel Zeit bei der Arbeit sparen: Teil 1“ nachlesen.
In unserem Beispiel ergeben sich für die Stundenerfassung folgende Werte:
C2 = Arbeitsende
B2 = Arbeitsbeginn
D2 = Pause
1. Geben Sie in die Stunden-Zelle E2 ein Gleichheitszeichen und eine geöffnete Klammer ein.
2. Klicken Sie jetzt mit dem Mauscursor auf die Zelle C2 und geben Sie ein Minus-Zeichen in E2 ein. Anschließend klicken Sie auf Zelle B2 und geben wieder ein Minus-Zeichen in E2 ein. Zuletzt klicken Sie noch auf die Zelle D2 und schließen die Klammer wieder.
3. Nachdem Sie mit „ENTER“ bestätigt haben, erscheint automatisch Ihre korrekt berechnete Arbeitszeit in Uhrzeiten in Zelle E2 Ihrer Excel Arbeitszeittabelle.
Sie müssen die Formel nur ein einziges Mal eingeben, um sie auch auf die übrigen Zellen in Ihrer Tabelle anzuwenden. Klicken Sie dafür einfach in die Zelle E2 und bewegen Sie den Mauscursor wieder in die rechte, untere Ecke der Zelle, bis das Plus-Symbol erscheint. Halten Sie nun die linke Maustaste gedrückt und ziehen Sie die Zellen nach unten auf. Jetzt berechnen sich automatisch auch die Arbeitsstunden in allen markierten Zellen aus dieser Spalte.
b) Uhrzeiten in Dezimalzahlen umwandeln
Microsoft Excel rechnet Stunden nur positiv. Sollten negative Stunden – also Minusstunden – in Ihrer Zeiterfassung auftauchen, so kann Excel diese standardmäßig nicht berücksichtigen, was bei einer Arbeitszeitberechnung eher hinderlich ist. Dieses Problem können Sie aber umgehen, indem Sie in Ihrer Excel Arbeitszeittabelle mit Dezimalzahlen arbeiten:
1. Multiplizieren Sie Ihre eingegebenen Stunden mit 24, indem Sie die Excel-Formel =E2*24 in Zelle F2 eingeben.
2. Nachdem Sie mit „ENTER“ bestätigt haben, müssen Sie noch das Zahlenformat manuell anpassen. Klicken Sie dazu mit der rechten Maustaste in die Zelle F2und wählen Sie im Drop-Down-Menü „Zellen formatieren…“ an, damit sich ein neues Dialogfenster öffnet.
3. Klicken Sie links im Fenster unter „Kategorie“ auf „Zahl“ und bestätigen Sie mit „OK“.
4. Die Uhrzeit wird ab sofort in Zelle F2 als Dezimalzahl angezeigt. Ziehen Sie die Zelle wie bereits oben beschrieben über die ganze Spalte auf, um die Excel-Formel automatisch auf alle Zellen zu übernehmen.
Der besseren Übersichtlichkeit wegen haben Sie die Möglichkeit, die Werte einheitlich auf Ihre gewünschte Anzahl an Nachkommastellen zu runden, indem Sie unter der Registerkarte „Start“ im Bereich „Zahl“ auf „Dezimalstellen hinzufügen“ oder „Dezimalstellen entfernen“ klicken:
c) Plus- und Minusstunden ausrechnen
Damit Sie mit der Excel Arbeitszeittabelle Ihre Plus- und Minusstunden berechnen können, müssen Sie zunächst Ihre Standardarbeitszeit je Tag festlegen:
1. Beschriften Sie die nächste Spalte Ihrer Tabelle mit „Standardarbeitszeit in Dezimal“ und geben Sie Ihre Standardarbeitszeit pro Tag (in unserem Beispiel 08:00 Stunden) in eine beliebige Spalte ein (bei uns die Spalte L).
Anschließend verwenden Sie für die Berechnung Ihrer Standardarbeitszeit in Dezimal die folgende Excel-Formel, die Sie in die Zelle G2 eintragen: „=$L$1*24“. Formatieren Sie Ihren Wert anschließend wieder als Zahl und ziehen Sie ihn über die Spalte (ausschließlich der Wochenenden) auf.
2. Nun können Sie damit weitermachen, Ihre Plus-/Minusstunden zu berechnen, indem Sie die nächste Spalte Ihrer Excel-Arbeitszeittabelle (in unserem Beispiel Spalte H) mit der entsprechenden Überschrift versehen und folgende Formel darauf anwenden, um Ihre Standardarbeitszeit pro Tag von den gearbeiteten Stunden zu subtrahieren: „=(F2-G2)“.
Ziehen Sie auch dieses Mal wieder Ihren errechneten Wert über die Spalte auf. Das Ergebnis aus unserem Beispiel sieht nun folgendermaßen aus:
d) Excel AutoSumme
Als nächstes können Sie mit Microsoft Excel die Gesamtsumme Ihrer Arbeitsstunden sowie Ihrer Plus‑/Minusstunden ermitteln. Mit der Funktion „Excel AutoSumme“ funktioniert das einfach und unkompliziert:
1. Klicken Sie in der Spalte F unter den Werten Ihrer Arbeitsstunden in eine beliebige Zelle.
2. Klicken Sie anschließend in der Registerkarte „Formeln“ auf die Schaltfläche „AutoSumme“ und danach auf „Summe“.
3. Das Ergebnis müssen Sie auch hier wieder mit der „ENTER“-Taste bestätigen:
Sie können anschließend genauso in allen anderen Spalten verfahren, um die Summen Ihrer Stunden, Ihrer Standardarbeitszeiten sowie Ihrer Plus- und Minusstunden zu bilden:
In unserem Beispiel ist die Excel-Summe der Stunden in Spalte E noch falsch dargestellt. Um das zu ändern, öffnen Sie erneut mit STRG + 1 das Dialogfenster „Zellen formatieren“.
Unter der Kategorie „benutzerdefiniert “ wählen Sie das Darstellungsformat.
Sollten Sie die Sekunden stören, können Sie diese löschen, so dass im Eingabefeld für Typ das Format [h]:mm stehen bleibt. Damit wird Ihnen die Summe Ihrer Arbeitsstunden nur noch in Stunden und Minuten ausgegeben:
3. Mit der Excel Arbeitszeiterfassung den fälligen Verdienst berechnen
Zu guter Letzt können Sie mit Excel nun auch noch Ihren Verdienst über die Arbeitszeittabelle berechnen – das funktioniert wiederum am besten auf Basis Ihrer Arbeitszeiten in Dezimal aus Spalte F:
1. Beschriften Sie die nächste Spalte Ihrer Tabelle mit „Verdienst“ und geben Sie Ihren Stundenlohn (in unserem Beispiel 13,50 Euro) in eine beliebige Zelle ein (bei uns die Zelle L2).
Anschließend verwenden Sie für die Berechnung Ihres Lohns die folgende Excel-Formel, die Sie in die Zelle I3 eintragen: „=F2*$L$2“.
Anschließend verwenden Sie für die Berechnung Ihres Lohns die folgende Excel-Formel, die Sie in die Zelle I3 eintragen: „=F3*$L$2“.
Drücken Sie auf „ENTER“ und wenden Sie die Excel-Formel wieder -wie bereits weiter oben beschrieben – auf alle übrigen Zellen in Ihrer Tabelle an, indem Sie die Zellen nach unten aufziehen.
Zu guter Letzt können Sie Ihren Verdienst beispielsweise ebenfalls noch mit dem AutoSumme-Assistenten für den kompletten Monat addieren:
4. Monatsweise Darstellung der Arbeitszeittabelle
Für eine monatsweise Darstellung Ihrer Arbeitszeiten ist es vorteilhaft, dass Tabellenblatt zu kopieren und mit dem jeweiligen Monatsnamen zu benennen. Der schnellste Weg, ein Tabellenblatt zu kopieren besteht darin, dass Sie
- STRG-Taste gedrückt halten
- Gleichzeitiges Klicken und Ziehen der linken Maus auf dem Tabellenblattnamen und
- Bewegen die Maus nach links.
Mit einem Doppelklick auf das kopierte Tabellenblatt aktivieren Sie den Überschreibemodus und benennen das Tabellenblatt auf den entsprechenden Monat um. Bestätigen Sie Ihre Angaben mit ok.
5. Fazit
Mit MS Excel haben Sie ein Tool zur Hand, mit dem Sie Ihren Stundennachweis führen können. Mit Hilfe von Zeit- und Datumsfunktionen erfassen Sie so auch Pausen- und Urlaubszeiten exakt. Optisch entsprechend gut formatiert, können Sie die Arbeitszeittabelle monatsweise duplizieren. Somit behalten Sie jederzeit den Überblick über Ihr Arbeitszeitkonto und können auf diesem Weg unkompliziert Arbeitszeitnachweise für Ihren Arbeitgeber erbringen.
Und wenn Sie noch mehr zum Thema Zeit- und Datumsfunktionen in Excel wissen wollen, besuchen Sie unseren online Kurs ‚Excel online: Arbeitszeittabelle erstellen‚. In unserem 3-Stunden online Training mit echtem Live Trainer, den Sie fragen können, erarbeiten Sie sich Ihre eigene Arbeitszeittabelle und lernen die wichtigsten Datums- und Zeitrechnungen kennen. Alternativ haben wir natürlich auch Präsenz Seminare wie z.B. „Excel für Personalarbeit“ oder „Excel Fortgeschritten“. Sie haben Fragen? Dann rufen Sie uns gerne an. 089 450 66316.
