Transcription
Zeiterfassung in Excel: Wie Sie einenStundenzettel erstellenImmer mehr Unternehmen führen flexible Arbeitszeiten in ihrem Betrieb ein. Dies hateindeutige Vorteile für die Produktivität und Mitarbeiterzufriedenheit, muss aber auch aufeiner gewissen Vertrauensbasis fußen. Zu diesem Zweck sollte man auf ein System zurArbeitszeiterfassung setzen. Dieses dient dem Angestellten als Arbeitsnachweis gegenüberdem Chef, der es wiederum zur Qualitätskontrolle und wöchentlichen bzw. monatlichenAbrechnung nutzt. Alles was Sie für einen elektronischen Stundenzettel brauchen, ist dieTabellenkalkulations-Software Excel – und unsere Schritt-für-Schritt-Anleitung.Excel mit Microsoft 365 und IONOS!Organisieren Sie Ihre Daten in Excel und erstellen Sie im Handumdrehen Tabellen. In allenOffice-Paketen enthalten!Office OnlineOneDrive mit 1TB24/7 SupportInhaltsverzeichnis1. Anforderungen an einen professionellen Stundenzettel2. Schritt-für-Schritt-Anleitung: Stundenzettel in Excel erstellen3. Die Excel-Zeiterfassung im EinsatzAnforderungen an einen professionellen StundenzettelWas macht eine professionelle Arbeitszeiterfassung in Excel aus? Diesbezüglich habenArbeitnehmer, Arbeitgeber und auch der Gesetzgeber recht unterschiedliche Ansprüche.Anforderungen aus ArbeitnehmersichtAus Sicht des Mitarbeiters muss ein Zeiterfassungssystem zuerst einmal leicht zu handhabensein. Schließlich bedeutet das regelmäßige Einpflegen von Arbeits- und Pausenzeiten einenzusätzlichen Aufwand und zugleich eine Unterbrechung des Workflow. Allein anhand desAufbaus und der Gestaltung eines Excel-Stundenzettels muss also sofort ersichtlich sein,welche Informationen von Belang sind und wo und wie die entsprechenden Daten eingetragenwerden müssen. Eine klare Struktur, unterstützende Farbgebung und Beschränkung aufdas Wesentliche können hierfür hilfreich sein. Grundsätzlich gilt: Je einfacher Sie es IhrenAngestellten machen, desto wahrscheinlicher ist es, dass die Arbeitszeiterfassung regelmäßig,pünktlich und korrekt erfolgt.Anforderungen aus ArbeitgebersichtDa die Erfassung der Arbeitszeit für die Überprüfung der Mitarbeiterperformance als auch fürdie Gehaltsabrechnung relevant ist, gehen mit ihr einige komplexe Berechnungen einher.Diese sollte eine smarte Excel-Tabelle im Idealfall selbst durchführen, um Angestellten undArbeitgebern Zeit und Arbeit zu ersparen. Entsprechende Funktionen können weitestgehend
unsichtbar eingebunden werden und verringern künftig die Fehlerquote bei der regelmäßigenZeiterfassung.Anforderungen aus rechtlicher SichtDas deutsche Arbeitszeitgesetz (ArbZG) legt zum Schutz der Gesundheit und Sicherheit vonArbeitnehmern bestimmte Höchstgrenzen für die Arbeitszeit fest – in der Regel sind dies 8Stunden (§ 3 ArbZG). Geht die tatsächliche Arbeitszeit ausnahmsweise über diese Grenzehinaus, gilt laut § 16 ArbZG eine Aufzeichnungspflicht für den Arbeitgeber, der diese anden Arbeitnehmer delegieren kann. Für die Richtigkeit der gemachten Angaben ist jedochallein der Arbeitgeber verantwortlich. Sämtliche Aufzeichnungen müssen zwei Jahre langaufbewahrt werden. Welcher Form die Zeiterfassung sein soll, schreibt das Gesetz jedochnicht vor.Schritt-für-Schritt-Anleitung: Stundenzettel in ExcelerstellenDas folgende Beispiel zeigt einen Stundenzettel für die Zeiterfassung in Excel. Er dient einemeinzelnen Mitarbeiter als Nachweis seiner geleisteten Arbeitszeit gegenüber dem Chef.Dafür kann der Mitarbeiter die Uhrzeiten eintragen, zu denen er die Schicht jeweils angetretenbzw. Feierabend gemacht hat. Ebenso trägt er den Beginn und das Ende seiner Pause ein. DieExcel-Zeiterfassung rechnet automatisch aus, wie viele Stunden gearbeitet wurden, undermittelt im selben Zuge eventuell angehäufte Plus- und Minusstunden. So spart sich derArbeitgeber bei der Gehaltsabrechnung einiges an Aufwand. Zudem hat der Mitarbeiter dieMöglichkeit, Urlaubs- und Krankentage geltend zu machen, die automatisch miterfasstwerden. Eine übersichtliche Formatierung und Farbgestaltung hebt die wichtigstenInformationen (IST-Arbeitsstunden und Plus-/Minusstunden) deutlich hervor.
Die Stundenzettel-Excel-Vorlage von IONOS können Sie unter folgendem Link kostenlosherunterladen und sofort verwenden:Arbeitszeiterfassung in ExcelIn der folgenden Schritt-für-Schritt-Anleitung erklären wir Ihnen die einzelnen Handgriffe,mit denen Sie eine Stundenzettel-Vorlage erstellen können.Schritt 1: Kopfzeile und Spalten einrichtenSo statten Sie die Kopfzeile der Excel-Tabelle mit allen notwendigen Informationen aus,richten die Spalten ein und formatieren Ihren Stundenzettel übersichtlich:1. Geben Sie in Zelle A1 den Namen des jeweiligen Mitarbeiters ein.2. Geben Sie in Zelle D1 die tägliche SOLL-Arbeitszeit Ihres Mitarbeiters im Formathh:mm (h Stunden, m Minuten) ein, z. B. 08:00 (8 Uhr). Dieser Wert dient alsReferenz für die Berechnungsformeln, die wir später in die Tabelle einbinden wollen.3. Ein kleines Hilfsmittel für Ihre Mitarbeiter: eine aktuelle Datumsanzeige, anhandderer sie im Stundenzettel leichter die richtige Zeile finden. Geben Sie hierfür in ZelleH1 die Formel HEUTE() ein und bestätigen Sie diese mit der Eingabetaste.4. Eine weitere Hilfestellung: In Zeile 2 können Sie erläutern, an welche E-Mail-Adresse(z. B. der Personalabteilung) der Stundenzettel geschickt werden und welchen Betreffdie E-Mail enthalten soll.5. Geben Sie in Zeile 3 nacheinander die Spaltenbezeichnungen „Datum“,„Arbeitsbeginn“, „Pausenbeginn“, „Pausenende“, „Arbeitsende“, „Abwesenheit“,„IST-Arbeitsstunden“ und „Plus-/Minusstunden“ ein.6. Formatieren Sie Ihre Tabelle nach Belieben. Nutzen Sie hierfür Fettungen sowieunterschiedliche Schriftarten, -größen und -farben in angemessener Weise.Einheitliche Spaltenbreiten schaffen Übersicht und sind optisch ansprechender.Verbundene Zellen und verschiedene Füllfarben können dabei helfen, einzelneBereiche Ihrer tabellarischen Zeiterfassung voneinander abzugrenzen. Der Button„Format übertragen“ in der linken oberen Ecke des Menübands hilft Ihnen, denGestaltungsprozess zu beschleunigen.7. Zuletzt fixieren Sie die oberen drei Zeilen Ihrer Tabelle, damit diese für denMitarbeiter auch beim Herunterscrollen ständig sichtbar bleiben. Markieren Sie hierfürdie Zelle A4. Klicken Sie nun unter dem Reiter „Ansicht“ auf die Schaltfläche„Fenster fixieren“ und wählen Sie „Fenster fixieren“. Nun werden alle Zeilen fixiert,die sich oberhalb von Zelle A4 befinden.Tipp
Sie können sämtliche Formeln in diesem Artikel auch direkt in Ihre Excel-Tabellehineinkopieren. Vergessen Sie aber nicht, nach jeder Einfügung bzw. Eingabe dieEingabetaste zu betätigen, damit die Formel korrekt eingebunden wird.Schritt 2: Fortlaufendes Datum einfügenSo fügen Sie ein fortlaufendes Datum zu Ihrem Excel-Stundenzettel hinzu und markieren dieWochenenden farblich:1. Geben Sie in Zelle A4 das erste Datum ein, für das die Arbeitszeit 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 inLangform samt Wochentag angezeigt.3. Klicken Sie auf das kleine Quadrat in der rechten unteren Ecke der markierten ZelleA4 und halten Sie die Maustaste gedrückt. Ziehen Sie nun einen Bereich nach untenauf, der eine volle Sieben-Tage-Woche abdeckt.4. Markieren Sie sämtliche Spalten. Klicken Sie dafür auf Spalte A, halten Sie dieMaustaste gedrückt und ziehen Sie einen Bereich bis Spalte H auf.5. Klicken Sie unter „Start“ „Formatvorlagen“ auf die Schaltfläche „BedingteFormatierung“.6. Klicken Sie im folgenden Kontextmenü auf „Neue Regel “. Ein separates Fensteröffnet sich.7. Klicken Sie unter „Regeltyp auswählen:“ auf „Formel zur Ermittlung der zuformatierenden Zellen verwenden“.8. Kopieren Sie unter „Werte formatieren, für die diese Formel wahr ist“ folgendeFormel in das Dialogfeld: WOCHENTAG( A1;2) 6 (diese Formel bedeutet imKlartext: „Folgendes gilt für alle Samstage und Sonntage in Spalte A“).9. Klicken Sie auf den Button „Formatieren “. Ein weiteres Fenster öffnet sich.10. Wählen Sie im Reiter „Ausfüllen“ eine beliebige Farbe für die Markierung derWochenenden und klicken Sie auf „OK“. Bestätigen Sie die Eingabe erneut mit „OK“.Sämtliche Samstage und Sonntage werden nun farblich hervorgehoben.Keine Sorge: Solch komplexe Formatierungen, Regeln und Formeln müssen Sie jeweils nureinmal einrichten. Um weitere Zellen mit denselben Eigenschaften zu versehen, brauchen Siedann nur auf das kleine Quadrat in der rechten unteren Ecke Ihrer Zelle zu klicken und esnach unten zu ziehen.Schritt 3: Abwesenheits-Optionen konfigurierenSo fügen Sie ein Drop-down-Menü zur Abwesenheits-Spalte Ihrer Stundenzettel-Tabellehinzu:
1. Markieren Sie in Spalte F („Abwesenheit“) die erste Zelle, die zu einem Wochentaggehört. In unserem Fall ist dies F5.2. Wählen Sie im Menüband den Reiter „Daten“.3. Klicken Sie bei „Datentools“ auf die Schaltfläche „Datenüberprüfung“. Ein neuesFenster öffnet sich.4. Wählen Sie im Reiter „Einstellungen“ unter „Gültigkeitskriterien“ „Zulassen“ dieOption „Liste“.5. Entfernen Sie das Häkchen bei „Leere Zellen ignorieren“.6. Im Dialogfeld „Quelle“ tippen Sie nun die Optionen ein, die im Drop-down-Menüverfügbar sein sollen. Denken Sie daran, die Optionen mittels Semikolon voneinanderzu trennen. In unserem Beispiel sieht das wie folgt aus: Urlaub;Krankheit;MobileOffice.7. Bestätigen Sie Ihre Eingabe mit „OK“.8. Der Zelle in Spalte F wurde nun ein Drop-down-Menü hinzugefügt. Um auch dieanderen Wochentage damit zu versehen, kopieren Sie es einfach mittels [Strg] [C] /[Strg] [V] hinein. Alternativ können Sie das kleine Quadrat in der rechten unterenEcke der Zelle benutzen, das geht schneller.HinweisWill ein Mitarbeiter seine Auswahl im Drop-down-Menü rückgängig machen, muss erlediglich die [Entf]-Taste drücken.Schritt 4: IST-Arbeitsstunden berechnen lassenSo veranlassen Sie Excel dazu, die IST-Arbeitsstunden Ihres Mitarbeiters vollautomatischauszurechnen:1. Markieren Sie die Spalte G („IST-Arbeitsstunden“).2. Unter „Start“ „Zahl“ klicken Sie auf den kleinen Pfeil neben „Standard“ undwählen im Kontextmenü die Option „Mehr “. Ein neues Fenster öffnet sich.3. Unter „Kategorie“ wählen Sie „Benutzerdefiniert“ und geben im Dialogfeld unter„Typ“ folgende Formatierung ein: [hh]:mm (damit befehlen Sie Excel, die absoluteStundenzahl bei der Summierung zu berücksichtigen, anstatt nach 12:00 immer wiederbei null anzufangen).4. Bestätigen Sie die Eingabe mit „OK“.5. Kopieren Sie nun die folgende Formel in die erste Zelle in Spalte G, die zu einemWochentag gehört (in unserem Fall ist dies G5): WENN(ODER(F5 "Urlaub";F5 "Krankheit"); D 1;E5-(D5-C5)-B5).
Diese recht kompliziert aussehende Wenn-Dann-Formel teilt Excel Folgendes mit: „Wenn fürein Datum in Spalte F („Abwesenheit“) „Urlaub“ oder „Krankheit“ eingetragen wird, sollendie 8 SOLL-Tagesstunden aus Zelle D1 als IST-Arbeitsstunden verzeichnet werden (beimEintrag „Mobile Office“ soll nichts passieren). Bleibt die Spalte F dagegen leer, sollen dieArbeitsstunden regulär berechnet werden, und zwar nach folgender Formel: E5 (Arbeitsende)- (D5 (Pausenende) - C5 (Pausenbeginn)) - B5 (Arbeitsbeginn).“ Wie gehabt können Sie dieseFormel mittels des kleinen Quadrats auf alle anderen Wochentage übertragen. Die Formel D 1 stellt dabei sicher, dass stets dieselben SOLL-Tagesstunden als Referenz herangezogenwerden.Nun müssen die IST-Arbeitsstunden noch zur Anzahl der Wochenstunden 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 Schaltfläche „AutoSumme“klicken und die Option „Summe“ wählen. Excel ermittelt dann automatisch dierichtige Formel.Schritt 5: Plus-/Minusstunden berechnen lassenSo bringen Sie Excel mit einem Trick dazu, sowohl Plus- als auch Minusstunden für einenMitarbeiter auszurechnen:1. Formatieren Sie die Spalte H („Plus-/Minusstunden“) als [hh]:mm – nutzen Siehierfür dieselbe Vorgehensweise wie bei Spalte G („IST-Arbeitsstunden“).2. Da Excel standardmäßig keine negativen Zeiten darstellen kann, müssen Sie nun einenkleinen Umweg in die „Optionen“ machen. Klicken Sie darin auf „Erweitert“ undsuchen Sie nach dem Bereich „Beim Berechnen dieser Arbeitsmappe:“. Dort solltesich die Angabe „1904-Datumswerte verwenden“ befinden, vor der Sie nun einHäkchen setzen. Bestätigen Sie die Eingabe mit „OK“.3. In unserem Fall geben wir in ZelleH5 nun folgende Formel ein: G5- D 1 (dieseweist Excel an, die IST-Arbeitsstunden mit den SOLL-Arbeitsstunden zu verrechnen).4. Übertragen Sie die Formel wie gehabt auf alle anderen Wochentage.5. Für die Summierung der Plus-/Minusstunden können Sie erneut die Funktion„AutoSumme“ verwenden oder die Formel SUMME(H5:H9) händisch eingeben.Übersicht über alle notwendigen FormelnIm Folgenden haben wir noch einmal alle praktischen Formeln zusammengefasst, die Sie fürsExcel-Zeiterfassung-Erstellen benötigen. Ahmen Sie unser Beispiel direkt nach, können Siediese Formeln änderungslos übernehmen. Wollen Sie dagegen ein eigenes StundenzettelDesign bauen, müssen Sie die Zellenangaben entsprechend anpassen. Orientieren Sie sichdafür auch am Screenshot weiter oben sowie an unserer Download-Vorlage.ZweckFormel (in Bezug auf unser Beispiel)
Aktuelles Datum HEUTE()Wochenenden farblich markieren WOCHENTAG( A1;2) 6Tägliche IST-Arbeitsstunden WENN(ODER(F5 "Urlaub";F5 "Krankheit"); D 1;E5(D5-C5)-B5)Summe IST-Arbeitsstunden SUMME(G5:G9)Tägliche Plus-/Minusstunden G5- D 1Summe Plus-/Minusstunden SUMME(H5:H9)Die Excel-Zeiterfassung im EinsatzZuletzt erklären wir Ihnen, wie Sie Ihre fertige Excel-Zeiterfassungs-Vorlagewiederverwenden, mit einem partiellen Schreibschutz versehen und an Ihre Mitarbeiterübermitteln.Arbeitszeit-Excel-Vorlage wiederverwendenDas Ergebnis Ihrer Arbeit ist eine professionelle Stundenzettel-Excel-Vorlage, die imMoment aber nur eine einzelne Woche abdeckt. Um diese nun auf einen beliebigenZeitraum zu erweitern, nutzen Sie wieder unseren gewohnten Trick: Markieren Sie diegesamte Woche samt aller dazugehörigen Spalten mit der Maus, klicken und halten Sie daskleine Quadrat in der rechten unteren Ecke des markierten Bereichs und ziehen Sie es nachunten. Wie Sie sehen, erkennt Excel automatisch ein wiederkehrendes Muster und repliziertsämtliche Formatierungen, Drop-down-Menüs, Formeln und Regeln für alle fortlaufendenDaten. Das erlaubt es Ihnen, Ihre Vorlage immer wiederzuverwenden.Stundenzettel-Excel-Vorlage mit partiellem Schreibschutz versehenIhr Mitarbeiter trägt die Uhrzeiten seines Arbeits- und Pausenbeginns sowie Arbeits- undPausenendes in den dafür vorgesehenen Spalten B, C, D, E im Format hh:mm (z. B. 08:30)ein bzw. deklariert seine Urlaubs- und Krankentage in Spalte F. Davon abgesehen soll er –weder unbeabsichtigt noch beabsichtigt – Änderungen am Inhalt und Aufbau desStundenzettels vornehmen können. Zu diesem Zweck statten Sie Ihre Excel-Tabelle miteinem partiellen Schreibschutz aus.Das funktioniert wie folgt:1. Wenn das aktive Excel-Arbeitsblatt geschützt ist, verfügen sämtliche Zellenstandardmäßig über den Status „Gesperrt“. Um nur einen partiellen Schreibschutz zuimplementieren, müssen Sie diese Einstellung zuerst ändern.2. Markieren Sie dafür die gesamte Tabelle, indem Sie auf den Schnittpunkt zwischenden Spalten und Zeilen (links oben) klicken.3. Klicken Sie nun unter dem Reiter „Start“ auf den kleinen Pfeil in der rechten unterenEcke des Bereichs „Ausrichtung“, um das Dialogfenster „Zellen formatieren“ zuöffnen. Sie können auch die Tastenkombination [Strg] [1] verwenden.4. Wählen Sie den Reiter „Schutz“ und entfernen Sie dort das Häkchen bei „Gesperrt“.
5. Bestätigen Sie die Eingabe mit „OK“.6. Halten Sie die [Strg]-Taste gedrückt und markieren Sie mit der Maus sämtlicheZellen, die Sie schützen möchten. Wir empfehlen die Zeilen 1, 2 und 3 sowie SpalteA.7. Kehren Sie zurück zum Fenster mit der „Gesperrt“-Option und fügen Sie dasHäkchen wieder hinzu.Leider bezieht diese Methode die Formeln in den Spalten G und H nicht mit ein. Hierfürmüssen Sie wie folgt vorgehen:1. Drücken Sie die Tastenkombination [Strg] [G], um das Dialogfenster „Gehe zu“aufzurufen.2. Klicken Sie auf den Button „Inhalte “.3. Markieren Sie den Punkt „Formeln“ und bestätigen Sie mit „OK“. Excel markiert nunalle Zellen, die Formeln enthalten.4. Gehe Sie nun wie in der vorangegangenen Anleitung vor, um sämtliche Formeln mitdem Status „Gesperrt“ zu versehen.Bevor Sie den Stundenzettel an Ihre Mitarbeiter schicken, müssen Sie noch den Schreibschutzaktivieren:1. Klicken Sie unter dem Reiter „Überprüfen“ auf die Schaltfläche „Blatt schützen“.2. Entfernen Sie unter „Alle Benutzer dieses Arbeitsblattes dürfen:“ das Häkchen bei„Gesperrte Zellen auswählen“.3. Wahlweise legen Sie ein Kennwort zum Schutz des Arbeitsblattes fest.4. Klicken Sie auf „OK“ (wenn Sie ein Kennwort eingegeben haben, müssen Sie diesesnoch einmal bestätigen).Excel-Zeiterfassung an Mitarbeiter verschickenSpeichern Sie Ihren Stundenzettel im regulären Excel-Format und verschicken Sie ihn perE-Mail an Ihre Mitarbeiter. Denken Sie daran, eine Anleitung beizufügen, die die Prozedurder Arbeitszeiterfassung verständlich erläutert. Klären Sie u. a., an welche E-Mail-Adresseund in welchen Zeitabständen der Stundenzettel an die Personalabteilung zurückgesendetwerden soll. Indem Sie ein festes Format zur Benennung der Excel-Dateien vorgeben (z. B.Vorname Name Kalenderwoche), können Sie die Stundenzettel später den jeweiligenMitarbeitern zuteilen.
Schritt 4: IST-Arbeitsstunden berechnen lassen . So veranlassen Sie Excel dazu, die IST-Arbeitsstunden Ihres Mitarbeiters vollautomatisch auszurechnen: 1. Markieren Sie die . Spalte G („IST-Arbeitsstunden“). 2. Unter „Start“ „Zahl“ klicken Sie auf den kleinen Pfeil ne
