Zeiterfassung mit Microsoft Office
Stundennachweis mit Excel: So erfassen Sie Arbeitszeiten
Um Arbeitszeiten zu erfassen, gibt es viele Lösungen. Doch warum nicht einfach den Stundennachweis in Excel erledigen? Wir geben eine Anleitung.
Mit eigener Zeiterfassung behalten Sie Ihre Arbeitszeiten im Griff - ob in Büro oder Home-Office. Diese Excel-Tabelle hilft Ihnen dabei, für jeden Monat die aktuellen Arbeitsstunden zu berechnen. Sie können Ihre Arbeitszeiten bequem erfassen und auswerten. Der Arbeitsstundennachweis ist nicht nur für eine Selbstkontrolle praktisch, sondern auch, wenn man entsprechende Nachweise für ein Gespräch mit dem Arbeitgeber benötigt und keine anderen Nachweise vorliegen.
1. Aufbau des Grundgerüsts für das Eingabeformular des Stundennachweises
Zunächst erstellen Sie in einer neuen Tabelle ein Eingabeformular. Füllen Sie die Zellen folgendermaßen: Zelle „A4“: „Name:“ – hier soll manuell der Name des Mitarbeiters eingegeben werden.
Zelle „D4“: „Monat:“ – dient zur Auswahl des aktuellen Monats.
Zelle „D5“: „Stunden:“ – die Stunden sollen mit einer Formel berechnet werden.
Zelle „A7“: „Datum:“ – In der Spalte darunter werden die Tage aufgelistet.
Zelle „B7“: „Start:“ – Beginn der Arbeitszeit.
Zelle „C7“: „Ende:“ – Ende der Arbeitszeit.
Zelle „D7“: „Pause:“ – Pause
Zelle „E7“: „Arbeitszeit/Tag:“ – Berechnung der Arbeitszeit pro Tag
Unterhalb der Zelle Datum sollen die Tage des Monats erscheinen. Tippen Sie in die Zelle A8 den Wert „1.“ ein. Ziehen Sie den Kopieranfasser dieser Zelle bis in die Zelle A38. Die Zellen werden jetzt fortlaufend automatisch von 1. bis 31. ausgefüllt.
2. Die Formeln für die Stundenberechnungen in das Formular eingeben
Jetzt sollen die Arbeitszeiten pro Tag mit einer Formel berechnet werden. Datum- und Zeitfunktionen konvertieren jedes Datum und jede Uhrzeit in einen seriellen Wert, die addiert und subtrahiert werden und dann wieder in ein Datum oder die Uhrzeit umgewandelt werden können. Klicken Sie in die Zelle E8. Zur Berechnung der Arbeitszeit wird die Endzeit von der Startzeit abgezogen. Abschließend wird noch die Pausenzeit abgezogen, sodass Sie die Formel =C8-B8-D8 eingeben. Als Ergebnis wird eine 0 angezeigt.
Ziehen Sie die Formel jetzt mit dem Kopieranfasser bis in die Zelle E38. Die Formeln werden relativ kopiert und in jeder Zelle steht jetzt eine 0.
Zum Berechnen der Gesamtstundenzahl klicken Sie in die Zelle E5 und tippen dort die Formel =SUMME(E8:E38) ein. Dadurch wird die Summe aus den einzelnen Arbeitstagen berechnet. Damit das Ergebnis als Industriezeit angezeigt wird, multiplizieren Sie diese mit 24. Somit lautet die komplette Formel in der Zelle E5 =SUMME(E8:E38)*24. Formatieren Sie die Zelle als normale Zahl mit zwei Nachkommastellen.
3. Formatvorlagen für eine bessere Tabellenübersicht nutzen
Damit im Formular sofort erkannt wird, in welchen Zellen Daten eingegeben werden sollen und wo nicht, nutzen Sie farbliche Kennzeichnungen. Da ein Großteil des Bereichs nicht zur Dateneingabe genutzt wird, hinterlegen Sie zunächst den kompletten Bereich mit einer Farbe. Benutzen Sie dazu die Zellenformatvorlagen. In diesen Formatvorlagen sind Vordergrund- und Hintergrundfarben kombiniert.
Markieren Sie zunächst den kompletten Bereich von der Zelle A1 bis zur Zelle E38. Klicken Sie dann im Register Start in der Gruppe Formatvorlagen auf das Symbol Zellenformatvorlagen. Alle Zellenformatvorlagen werden eingeblendet. Bereits beim Berühren eines dieser Vorlagen wird die Auswirkung auf den markierten Bereich angezeigt. Wählen Sie zum Beispiel 40% Akzent1 aus. Mit einem Klick auf die gewünschte Formatvorlage wird sie auf den ausgewählten Bereich übertragen.
Jetzt sollen die Zellen, die zur Dateneingabe für den Anwender vorgesehen sind, in der Standardformatierung angezeigt werden. Dazu bietet sich der Formatpinsel an. Kicken Sie zunächst auf eine Zelle außerhalb des formatierten Bereichs. Führen Sie dann einen Doppelklick auf das Symbol Format übertragen in der Gruppe Zwischenablage aus. Durch den Doppelklick bleibt der Pinsel bis zu dessen Ausschaltung aktiv.
Klicken Sie jetzt in die Zelle B4, denn hier soll der Name eingegeben werden. Klicken Sie dann in die Zelle E4, hier wird der Monat eingegeben. Markieren Sie jetzt den Bereich von B8 bis D38. Hier werden die Arbeitszeiten und die Pausen eingetragen. Zum Beenden des Formatpinsels betätigen Sie die [Esc]-Taste. Jetzt ist optisch schon sehr gut zu erkennen, an welchen Stellen Daten eingegeben werden sollen.
Die Arbeitszeiten sollen im Zeitformat angezeigt werden. Markieren Sie den Bereich E8:E38. Klicken Sie im Register Start in der Gruppe Zahl auf das Auswahlfeld Zahlenformat, und wählen Sie den Eintrag Weitere Zahlenformate aus. Das Dialogfenster Zellen formatieren wird eingeblendet. Klicken Sie in der Kategorie auf den Eintrag Benutzerdefiniert. Tippen Sie in die Zeile Typ das Format hh:mm ein. Mit einem Klick auf die Schaltfläche OK werden die berechneten Werte automatisch von Excel in das Zeitformat konvertiert.
4. Im Feld Monat nur die Eingabe aus einem Listenfeld zulassen
Bei der Eingabe von Texten hat man sich schnell mal verschrieben. Wenn es um Inhalte geht, die vorher bekannt sind, wählen Sie diese aus einem Listenfeld aus. Das bietet sich bei der Eingabe des Monats an. Legen Sie zunächst in einem Bereich außerhalb des Eingabebereichs die Werte für die Liste fest. Klicken Sie in die Zelle H1. Tippen Sie dort Januar ein. Ziehen Sie den Eintrag mit dem Kopieranfasser bis in die Zelle H12. Die Monate werden dann automatisch in die Zellen eingetragen. Klicken Sie jetzt in die Zelle E4. Im Register Daten klicken Sie auf das Symbol Datenüberprüfung. Das gleichnamige Dialogfenster wird eingeblendet. Im Register Einstellungen wählen Sie im Auswahlfeld Zulassen den Eintrag Liste aus.
In der Zeile Quelle geben Sie den Bereich mit den Monatsnamen ein: =$H$1:$H$12. Legen Sie jetzt die gewünschten Einträge in den Registerblättern Eingabemeldung und Fehlermeldung fest, und schließen Sie die Eingaben mit einem Klick auf die Schaltfläche OK ab. Schon steht Ihnen ein Auswahlfeld mit den vorgegebenen Monaten zur Verfügung. Damit die Monatsnamen für die Liste nicht aus Versehen geändert werden, blenden Sie die Spalte mit den Monaten aus der Tabelle aus. Markieren Sie dazu die Spalte H, und klicken Sie diese mit der rechten Maustaste an. Wählen Sie im dann eingeblendeten Kontextmenü den Befehl Ausblenden aus.
5. Die Fehlermeldungen bei der Datenüberprüfung festlegen
In der Datenprüfung können Sie zusätzlich festlegen, wie eine fehlerhafte Eingabe behandelt wird. Klicken Sie dazu wieder in die Zelle E4. Klicken Sie dann auf das Symbol Datenüberprüfung, und aktivieren Sie dort das Register Fehlermeldung. Lassen Sie den Haken im Kontrollkästchen Fehlermeldung anzeigen, wenn ungültige Daten eingegeben wurden stehen. Tippen Sie in das Feld Titel den Text Falsche Eingabe ein. In das Feld Fehlermeldung schreiben Sie Wählen Sie einen Wert aus. Mit dem Auswahlfeld Typ wählen Sie aus, wie streng die Prüfung erfolgen soll. Stopp: Mit dieser Auswahl wird nur eine Eingabe im festgelegten Gültigkeitsbereich erlaubt.
Eingaben außerhalb des Gültigkeitsbereichs werden mit einer Fehlermeldung abgewiesen. Warnung: Diese Auswahl gibt eine Warnmeldung aus, wenn sich Ihre Daten nicht im Gültigkeitsbereich befinden. Sie können die Daten aber mit einem Klick auf die dann eingeblendete Schaltfläche Fortfahren: Ja trotzdem eingeben. Information: Mit dieser Auswahl bekommen Sie nur den Hinweis, dass sich Ihre Daten außerhalb des Gültigkeitsbereichs befinden, die Daten werden aber dennoch in die Zelle eingetragen. Um sicherzustellen, dass nur Einträge aus der Liste zugelassen werden, wählen Sie den Typ Stopp aus.
6. Die Arbeitszeiten mit einem Gültigkeitskriterium begrenzen
Damit der Anwender im Bereich der Zeiten nur Uhrzeiten in einer bestimmten Zeitspanne eingeben kann, belegen Sie diese Eingabefelder ebenfalls mit einer Gültigkeitsprüfung. Für den Arbeitsbeginn markieren Sie die Zellen B8 bis B38, und starten Sie die Datenüberprüfung. Wählen Sie als Gültigkeitskriterium Zeit aus. In dem Auswahlfeld Daten wählen Sie den Eintrag Zwischen aus. Legen Sie die Anfangszeit auf 6:00 fest. Setzen Sie die Endzeit auf 9:00. Schließen Sie den Vorgang mit einem Klick auf die Schaltfläche OK ab. Für das Arbeitsende markieren Sie den Bereich C8 bis C38, und starten Sie die Datenüberprüfung.
Wählen Sie als Gültigkeitskriterium Zeit aus. Legen Sie die Anfangszeit auf 12:00 fest. Setzen Sie die Endzeit auf 20:00. Die Eingabe für die Pause soll auf zwei Stunden begrenzt werden. Markieren Sie die Zellen D8 bis D38, und starten Sie die Datenüberprüfung. Wählen Sie als Gültigkeitskriterium Zeit aus. In dem Auswahlfeld Daten wählen Sie den Eintrag kleiner oder gleich aus. Setzen Sie die Endzeit auf 2:00. Jetzt können nur die Uhrzeiten in dem ausgewählten Gültigkeitsbereich erfasst werden.
7. Zur Sicherheit nur bestimmte Bereiche für eine Bearbeitung zulassen
Damit der Anwender die Formeln nicht ändern kann oder andere Dinge in Ihrem Formular verändert, schützen Sie Ihr Arbeitsblatt. Standardmäßig sind alle Zellen einer Excel-Tabelle geschützt, Sie merken das aber erst nach dem Einschalten des Blattschutzes. Damit der Anwender seinen Namen, den Monat und die relevanten Daten eingeben kann, müssen Sie diese Zellen vorher zur Dateneingabe freigeben. Da die Eingabefelder nicht alle unmittelbar zusammenstehen, wenden Sie beim Markieren der Zellen einen kleinen Trick an: Halten Sie beim Markieren die [Strg]-Taste gedrückt. Markieren Sie dann alle Eingabefelder: B4, E4, B8:D38.
Betätigen Sie anschließend die rechte Maustaste und wählen Sie im Kontextmenü den Befehl Zellen formatieren aus. Das gleichnamige Dialogfenster wird eingeblendet. Aktivieren Sie hier das Registerblatt Schutz, und entfernen Sie den Haken aus dem Kontrollkästchen Gesperrt. Klicken Sie dann auf die Schaltfläche OK. Aktivieren Sie jetzt das Register Überprüfen. Klicken Sie in der Gruppe Änderungen auf das Symbol Blatt schützen. Das gleichnamige Dialogfenster wird eingeblendet. Tippen Sie ein Kennwort zum Aufheben des Blattschutzes ein. Entfernen Sie den Haken aus dem Kontrollkästchen Gesperrte Zellen auswählen. Klicken Sie auf die Schaltfläche OK. Jetzt kann der Anwender nur in den freigegebenen Feldern Daten eingeben.
8. Aus dem Stundennachweis eine Vorlage für weitere Tabellen erstellen
Durch das Erstellen einer Vorlage stellen Sie das Formular für andere Anwender oder für weitere Stundenberechnungen zur Verfügung.
Lesetipp: Excel: Alle Inhalte einer Tabelle auf eine Seite drucken
Aktivieren Sie dazu das Register Datei, und wählen Sie dort den Befehl Exportieren aus. Klicken Sie auf Dateityp ändern, und wählen Sie dann das Symbol Vorlage. Klicken Sie im Anschluss auf das Symbol Speichern unter und vergeben einen passenden Dateinamen. Der Anwender kann dann über die Befehlsfolge Datei, Neu, Persönlich jederzeit ein neues Formular erstellen und bearbeiten ohne in die Gefahr zu kommen, eine bereits gepflegte Stundenabrechnung zu überschreiben. Unsere Vorlage lässt sich natürlich ganz nach Belieben erweitern. Sie soll Ihnen ein einfaches Grundgerüst für weitere Auswertungen liefern.
9. Weitere kostenlose Beispiele und Vorlagen aus dem Internet laden
Sucht man im Internet über Google Excel-Vorlagen für einen Arbeitszeitnachweis, bekommt man viele Vorschläge von einschlägigen Office-Portalen oder privat erstellten Arbeitszeittabellen von Excel-Tüftlern.
Schauen Sie zum Beispiel einmal auf die Internet-Seite office-lernen.com Hier werden Ihnen verschiedene Varianten von Arbeitszeittabellen kostenlos angeboten. Auf unterschiedlichen Excel-Tabellen zum Ausdrucken oder als Vorlage lassen sich beispielsweise neben der Arbeitszeit der fällige Arbeitslohn oder die Überstunden ermitteln. Auf der Webseite von Timo Mutter finden Sie neben anderen Vorlagen eine kostenlose Excel-Arbeitszeitnachweisvorlage. Diese Tabelle läuft ab Excel 2007, aber auch unter den Kalkulationsprogrammen von OpenOffice und LibreOffice.
