Tutorials

Tutorial: Automatisierung einer wöchentlichen Zeiterfassung in Excel für die Lohnabrechnung

Melden

Hier ist ein umfassendes Tutorial, wie du eine automatisierte wöchentliche Zeiterfassung in Excel erstellst, die bereit für die Lohnabrechnung ist.

Dieses Tutorial zeigt dir, wie du eine Vorlage erstellst, die Arbeitsstunden automatisch berechnet, Pausen abzieht und den Bruttolohn kalkuliert. Da wir Jahreszahlen ignorieren, ist diese Vorlage universell für jede Woche einsetzbar.

Schritt 1: Die Grundstruktur anlegen

Zuerst erstellen wir das Gerüst der Tabelle. Lege folgende Spaltenüberschriften in der ersten Zeile an (A1 bis G1):

  • A1: Wochentag
  • B1: Datum
  • C1: Arbeitsbeginn
  • D1: Arbeitsende
  • E1: Pause (in Minuten)
  • F1: Netto-Arbeitszeit (Stunden)
  • G1: Kommentar/Projekt

Schritt 2: Dynamisches Datum einrichten

Damit du die Vorlage jede Woche nutzen kannst, ohne jedes Datum einzeln einzutippen:

  1. Schreibe in Zelle B2 das Datum des Montags der gewünschten Woche (z. B. 01.05.).
  2. Schreibe in Zelle B3 die Formel: =B2+1.
  3. Ziehe diese Formel bis Zelle B8 (Sonntag) nach unten.
  4. Wochentage automatisieren: Schreibe in Zelle A2 die Formel: =TEXT(B2;"TTTT"). Ziehe diese ebenfalls bis A8 nach unten. Excel zeigt nun automatisch „Montag“, „Dienstag“ etc. an.

Schritt 3: Zeitformate festlegen

Excel muss wissen, dass es mit Uhrzeiten rechnet:

  1. Markiere die Spalten C (Beginn) und D (Ende).
  2. Rechtsklick -> Zellen formatieren -> Uhrzeit -> Wähle das Format 13:30.
  3. Markiere Spalte F (Netto-Arbeitszeit).
  4. Rechtsklick -> Zellen formatieren -> Benutzerdefiniert -> Gib hier [h]:mm ein. (Die eckigen Klammern sorgen dafür, dass auch Zeiten über 24 Stunden korrekt angezeigt werden).

Schritt 4: Die Formel für die Arbeitszeit

Jetzt berechnen wir die tägliche Arbeitszeit abzüglich der Pause. In Zelle F2 gibst du folgende Formel ein:

=(D2-C2)-(E2/1440)

Erklärung:

  • (D2-C2) berechnet die Zeitdifferenz.
  • (E2/1440) wandelt die Pausenminuten in den Excel-Zeitwert um (da ein Tag 1440 Minuten hat).
  • Ziehe die Formel bis F8 nach unten.

Schritt 5: Gesamtstunden und Lohnberechnung

Unterhalb deiner Tabelle (z. B. in Zeile 10) erstellen wir die Zusammenfassung für die Lohnabrechnung:

  1. Gesamtstunden (Zelle F10): =SUMME(F2:F8)
  2. Stundensatz (Zelle F11): Gib hier deinen Stundenlohn ein (z. B. 15,00). Formatiere die Zelle als Währung (€).
  3. Gesamtlohn (Zelle F12): Hier kommt der wichtigste Trick. Da Excel intern mit Tagen rechnet, musst du die Stunden mit 24 multiplizieren:
    • Formel: =F10*F11*24
    • Formatiere diese Zelle als Währung.

Schritt 6: Automatisierung durch Datenüberprüfung (Optional)

Um Fehler bei der Eingabe zu vermeiden:

  1. Markiere die Spalte E (Pause).
  2. Gehe zum Reiter Daten -> Datenüberprüfung.
  3. Wähle unter "Zulassen": Liste.
  4. Gib als Quelle vor: 0;15;30;45;60. So kann man die Pause einfach per Dropdown auswählen.

Schritt 7: Optische Aufbereitung (Bedingte Formatierung)

Damit du sofort siehst, ob jemand Überstunden gemacht hat:

  1. Markiere die Netto-Arbeitsstunden (F2:F8).
  2. Gehe zu Bedingte Formatierung -> Regeln zum Hervorheben von Zellen -> Größer als....
  3. Gib z. B. 08:00/24 ein und wähle eine rote Füllung für alle Tage, an denen mehr als 8 Stunden gearbeitet wurde.

Zusammenfassung der Vorteile:

  • Kein händisches Rechnen: Du gibst nur Start, Ende und Pause ein.
  • Flexibel: Ändere einfach das Datum in B2, und die ganze Woche passt sich an.
  • Payroll-Ready: Der Bruttolohn wird sofort korrekt berechnet, bereit für die Buchhaltung.

Tipp für die Zukunft: Speichere diese Datei als Excel-Vorlage (.xltx), damit du jede Woche mit einem sauberen Blatt startest!

0