Zeiterfassung im Homeof ce mit Excel
Coronabedingt arbeiten derzeit mehr Mitarbeiter im Homeof ce als je zuvor. Viele Chefs haben erkannt, dass die Leistung trotzdem passt. Behalten Sie Ihre Überstunden mit unserer Arbeitszeiterfassung im Griff.
So behalten Sie Ihre Arbeitszeit im Blick
Wer exible Arbeitszeiten hat oder viele Überstunden macht, der muss im Homeof ce den Überblick über die geleisteten Stunden behalten. Unterstützung bieten Ihnen geeignete Arbeitszeittabellen in Microsoft Excel, Calc (LibreOf ce) oder die Tabellenapplikation von Google Docs. Wenn Sie sich einmal eine geeignete Arbeitsmappe mit Formeln anlegen, wissen Sie mit nur zwei bis drei täglichen Eingaben, ob das Überstundenkonto prall gefüllt ist oder ob Fehlstunden nachzuholen sind.
Am besten legen Sie für jeden Monat eine Arbeitszeittabelle an. Damit diese auf allgemeine Informationen zugreifen kann, emp ehlt sich eine zusätzliche Tabelle für persönliche Daten wie Name, Personal-Nr., tägliche Arbeitszeit, das vergangene Überstundensaldo und allgemeine Angaben, etwa das Jahr oder die Feiertage (siehe Bild auf den nächsten Seite oben links). Damit Sie die nachfolgenden Schritte 1:1 nachzuvollziehen können, achten Sie darauf, dass Sie exakt die gleichen Zellen für die Angaben nutzen wie in der Abbildung.
Auf die Infos können die einzelnen Monatsübersichten später zugreifen. Das ist zwar nicht zwingend notwendig; je mehr Vorarbeit Sie jedoch leisten, umso komfortabler wird das Tool. Weisen Sie dem Arbeitsblatt den Namen Informationen zu. Dazu klicken Sie einfach doppelt auf die zugehörige Registerlasche und überschreiben den vorgegebenen Namen. All denjenigen, denen es zu aufwendig ist, das Tabellenmodell selbst zu erstellen, nden eine fertige Musterlösung für das 1. Quartal 2021 auf der Heft-DVD unter Software/Extras zum Heft. Bevor Sie die Excel-Arbeitsmappe verwenden, sollten Sie auf der Informationsseite die Feiertage anpassen.
1. Grundgerüst für die Arbeitszeiterfassung anlegen
Nennen Sie die erste Monatstabelle Januar. Lassen Sie im oberen Tabellenbereich etwas Platz für persönliche Angaben (siehe Bild nächste Seite oben rechts). Auf diese Weise können Sie das Formular später bequem ausdrucken und weitergeben. Ihren Namen übernehmen Sie über die Formel
=Informationen!B3, die Personal-Nummer über =Informationen!B4. Erfassen Sie als nächstes die Datumsangaben. Tippen Sie in Zelle A6 das Startdatum mit der Formel ein: =DATUM(Informationen!B9;1;1)
In A7 kommt die Formel =A6+1. Aufbauend darauf lassen sich alle weiteren Monatsblätter schnell erstellen und für die kommenden Jahre dynamisch anpassen.
Damit Sie auf den ersten Blick erkennen, ob es sich um ein Wochenende handelt, weisen Sie den Zellen ein Datumsformat mit Wochentagen zu. Dazu markieren Sie die Datumszellen und öffnen durch einen Klick mit der rechten Maustaste das Kontextmenü. Wählen Sie nacheinander Zellen formatieren/Zahl/Datum. Unter Typ geben Sie den Code TTT TT MM JJ an und bestätigen über OK. Auf diese Weise zeigt Excel die ersten beiden Buchstaben des jeweiligen Tages an. TTT steht dabei stellvertretend für den gekürzten Wochentag (zum Beispiel Mo), TT für das Tagesdatum in Zifferform, MM für den Monat, JJ fürs Jahr. Kopieren Sie die Formel aus A7 in die nachfolgenden Zellen, bis der 31.01.2021 angezeigt wird.
Damit das Tabellenkalkulationsprogramm die Arbeitszeit ermittelt, müssen Sie angeben, an welchem Tag Sie wie lange gearbeitet haben. Legen Sie dazu weitere Spalten entsprechend für den täglichen Arbeitsbeginn, das Ende und die Pausenzeiten an.
2. Ein Auswahlfeld zur Erfassung von Fehlzeiten
In die nächste Spalte kommen Ausfallzeiten. Unter Umständen wird krankheitsoder urlaubsbedingt an bestimmten Tagen nicht gearbeitet. Eine komfortable Arbeitszeiterfassung sollte den Grund für ein eventuelles Fehlen anzeigen. Hierfür emp ehlt sich ein Zell-Drop-down. Schreiben Sie in einen Zellbereich außerhalb Ihrer Übersicht (A101:A105) untereinander die Begriffe Urlaub, krank, Kur, Fortbildung und Sonstiges.
Klicken Sie nacheinander in die Zelle E6 und im Menü Daten auf Datenüberprüfung/ Datenüberprüfung. Im folgenden Fenster öffnen Sie das Register Einstellungen. Dort wählen Sie unter Zulassen den Eintrag Liste und geben unter Quelle den Bereich A100:A105 an. Damit umfasst die Fehlzeitenliste zusätzlich eine Leerzelle, um alle Tage abzudecken, an denen keine Einträge notwendig sind. Klicken Sie auf OK. Später wählen Sie die Fehlzeit aus, indem Sie in die entsprechende Zelle klicken. Am rechten Zellenrand erscheint ein kleiner Pfeil über den Sie eine Au istung der zuvor de nierten Einträge erhalten. Einfach den zutreffenden Eintrag auswählen, fertig.
3. Formeln für Rechenvorgänge erfassen
Alle weiteren Werte der Arbeitszeiterfassung ( Istzeit, Sollzeit, Mehrstunden, Gesamtsaldo) ergeben sich rechnerisch. In Abhängigkeit vom Wochentag erscheinen die täglichen Sollstunden. Für Samstage, Sonntage und für Feiertage wird die Zeit formel
gesteuert auf Null gesetzt. Wer trotzdem arbeitet, erhält später automatisch eine Überstundengutschrift. Das gilt auch für die Feiertage in Informationen.
Achtung: Für Feiertage gelten für die verschiedenen Bundesländer unterschiedliche Regelungen. Beispielsweise ist der 6. Januar nicht in allen Bundesländern ein Feiertag.
Die folgende Formel aus der Zelle G6 zur Berechnung der Zeitvorgabe ist recht komplex und verknüpft mehrere Funktionen (WENN, ODER und WOCHENTAG) miteinander. Nachfolgend ein Ausschnitt aus der Formel, die beispielhaft die Methode zeigt: =WENN(ODER(A6=Neujahr;A6=HeiligeDreiKön ige;[…]);0;WENN(WOCHENTAG(A6;2)<6;Infor mationen!$B$7;0))
Die Zellen mit den Datumsangaben wurden für ein besseres Verständnis mit dem Namen des Feiertags versehen (Formeln/ Namen de nieren). Zwingend notwendig ist der Schritt jedoch nicht. In der Arbeitsmappe auf der Heft-DVD werden auch die Datumsangaben verglichen. Mithilfe der Formel (vollständig sichtbar im Bild unten links) wird geprüft, ob es sich um einen Arbeitstag handelt oder nicht. Bei Arbeitstagen erscheint die tägliche Arbeitszeit, an Wochenenden bzw. Feiertagen der Wert 0:00. Ob die Formel korrekt funktioniert, können Sie direkt anhand des Neujahrstages sehen. Obwohl es sich um den Wochentag Freitag handelt erscheint 0:00.
Die Ist-Zeit ergibt sich in F6 als Saldo aus Arbeitsende, -beginn und Pausenzeiten mit Hilfe der Formel
= WENN(ODER(C6="";C6=0);0;C6-B6-D6) Damit wird erreicht, dass die Ist-Zeit nur dann berechnet wird, wenn ein Arbeitsende erfasst wurde. Solange bleibt die geleistete Stundenzahl auf Null. Warum so kompliziert? Ohne diese Abfrage würde Excel automatisch alle noch fehlenden Angaben als Minusstunden ausweisen. Somit hätten Sie keinen Überblick über Ihren aktuellen Stand. Die Mehr- und Minderstunden werden in H6 berechnet:
=WENN(NICHT(ISTLEER(C6));F6-G6;0) Beachten Sie hier unbedingt folgende Besonderheit: Wird die Sollstundenzahl unterschritten, ergeben sich negative Zeitwerte. Excel tut sich hier schwer und weist lediglich Rauten (######) aus. Das korrekte Ergebnis wird angezeigt, wenn Sie unter Datei/Optionen/Erweitert in der Gruppe Beim Berechnen dieser Arbeitsmappe das Kontrollkästen 1904 Datumswerte abhaken und bestätigen.
Tipp: Wenn Sie den Zellen in der Spalte Mehr-/Minderstunden das benutzerde - nierte Format [h]:mm;[Rot]-[h]:mm zuweisen, werden alle Minusstunden in roter Schriftfarbe angezeigt.
4. Gesamtsalden mit Benutzerde niertem Format ausweisen
Für die letzte Spalte Gesamtsaldo greifen Sie noch einmal auf die Tabelle Informationen zu (I6: =H6+Informationen!B8, I7: =H7+I6). In der Summenzeile werden mithilfe einer einfachen Summenfunktion die Gesamtstunden des Monats (F37: =SUMME(F6:F36)) ausgerechnet. Hier schwächelt Excel, wenn das Ergebnis mehr als 24:00 Stunden beträgt. Dieser Macke begegnen Sie mithilfe eines weiteren Benutzer-de nierten Zellenformats im Fenster Zellen formatieren. Tippen Sie in das Feld Typ den Code [h]:mm ein. Bestätigen Sie über OK. Dieses Format sollten Sie auch für die Spalte I verwenden.
5. Rest- und Formatierungsarbeiten am Tabellenmodell durchführen
Die Einträge des Bereichs E6:H6 können Sie in einem Rutsch in die nachfolgenden Zeilen kopieren. In Spalte I kopieren Sie die Zelle I7. Für eine bessere Abgrenzung der Spalten voneinander sorgen Rahmenlinien aus der Gruppe Schriftart im Start- Menü. Wenn Sie die Eingabezellen (Bereich B6:E36) mit einem Zellhintergrund kennzeichnen, heben sie sich von den Formelzellen ab. Verwenden Sie dazu die Schalt äche Füllfarbe, die Sie unter Start/Schriftart nden.
Tipp: Wenn Sie später die Zeiten erfassen, müssen Sie bei vollen Stunden lediglich die
Zahl und einen Doppelpunkt eintippen. Aus 8: macht Excel 8:00. Für Bruchteile einer Stunde verwenden Sie das Zeitformat, also 0:30 statt 0,5.
Achtung: Hier macht Excel aus der Eingabe 0:3 allerdings 00:03, sodass Sie hier 0:30 eintragen müssen. Schicker sehen die Tabellen aus, wenn Sie unter Anzeige die Gitternetzlinien ausschalten. Für einen späteren Ausdruck de nieren Sie den Druckbereich (A1:I37) über die Befehlsfolge Seitenlayout/ Druckbereich/Druckbereich festlegen.
6. Komplexe Formen zur Sicherheit schützen
Eine Formel ist schnell überschrieben, sodass Sie die Zellen schützen sollten. Markieren Sie alle Formelzellen. Mehrfachmarkierungen sind mithilfe der [Strg]- Taste möglich. Im Dialog Zellen formatieren aktivieren Sie das Register Schutz, entfernen dort das Häkchen im Feld Gesperrt und bestätigen die Änderung. Der Zellschutz greift nur in Kombination mit dem Blattschutz, den Sie im Register Überprüfen durch einen Klick auf die Schalt äche Blatt schützen aktivieren. Auf ein Kennwort können Sie in Ihrer persönlichen Arbeitszeitermittlung verzichten. Klicken Sie auf OK.
7. Vom Blatt zum Tool: Tabellenmodell vervielfältigen
Sobald die Tabelle für den Monat Januar fertig ist, können Sie diese vervielfältigen. Das erledigen Sie durch einen Klick auf das Blattregister und den Befehl Verschieben oder kopieren. Im folgenden Fenster das Kontrollkästchen Kopieren abhaken, bestätigen, fertig. Es sind noch kleinere Anpassungsarbeiten notwendig. Ändern Sie für den Februar in Zelle A6 die Formel (=DATUM(Informationen!B9;2;1)) für Februar. Im Grunde müssen Sie nur das mittlere Argument, das für den Monat steht, austauschen. Die Folgezellen werden – wie bereits im Januar – automatisch ausgefüllt. In I6 üernehmen Sie den aktuellen Saldo vom 31.01.2021 (= H6+Januar!I36). Erstellen Sie analog ein Blatt für den März usw. In den Monaten, die keine 31 Tage umfassen, etwa im Februar oder April, können Sie die Inhalte der über üssigen Zeilen in weißer Schriftfarbe gestalten. Das hat gegenüber dem Löschen den Vorteil, dass Sie bei der Übernahme von Salden nicht auf eine unterschiedliche Anzahl von Tagen achten müssen. Jetzt müssen Sie nur noch die einzelnen Tabellen mit den Monatsnamen versehen. Wem komplexe Tabellen Spaß machen, kann das Tool jederzeit erweitern, etwa automatisch Überschriften erzeugen: ="Arbeitszeiterfassung"&TEXT(MONAT(A6); "MMMM")&" "&Informationen!B9
Oder über die Auswahlfelder der Datenüberprüfung die Anzahl der Krankheitsbzw. Urlaubstage ermitteln: =ZÄHLENWENN(E6:E36;"Urlaub")