Tutorials

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

Melden

Hier ist ein detailliertes Tutorial, wie du eine automatisierte, zweiwöchentliche (bi-weekly) Zeiterfassung in Excel erstellst, die perfekt für die Lohnabrechnung vorbereitet ist.

Diese Vorlage ist so konzipiert, dass sie flexibel für jeden Zeitraum funktioniert. Du musst lediglich das Startdatum eingeben, und die gesamte Tabelle aktualisiert sich von selbst.

Schritt 1: Grundstruktur anlegen

Erstelle zuerst die Kopfzeilen für deine Tabelle. Da es sich um einen zweiwöchentlichen Rhythmus handelt, benötigen wir Platz für 14 Tage.

Kopfbereich (Eingabefelder):

  • Zelle A1: Mitarbeitername
  • Zelle A2: Abrechnungszeitraum Beginn (Hier gibst du das Startdatum ein)

Tabellenüberschriften (ab Zeile 4):

  • Spalte A: Datum
  • Spalte B: Wochentag
  • Spalte C: Arbeitsbeginn
  • Spalte D: Arbeitsende
  • Spalte E: Pause (in Stunden oder Minuten)
  • Spalte F: Netto-Arbeitszeit (Gesamt)
  • Spalte G: Art (Arbeit, Krankheit, Urlaub)

Schritt 2: Dynamische Datumsberechnung

Damit du die Daten nicht jedes Mal manuell tippen musst, automatisieren wir die Datumsspalte.

  1. Klicke in Zelle A5 (erster Tag) und schreibe: =A2.
  2. Klicke in Zelle A6 und schreibe: =A5+1.
  3. Ziehe die Formel aus A6 nach unten bis zur Zelle A18, sodass du insgesamt 14 Tage hast.

Wochentage automatisch anzeigen:

  1. Schreibe in Zelle B5: =A5.
  2. Mache einen Rechtsklick auf B5 -> Zellen formatieren -> Zahlen -> Benutzerdefiniert.
  3. Gib im Feld "Typ" drei kleine ttt ein. Nun wird dort automatisch „Mo“, „Di“ etc. angezeigt.
  4. Ziehe diese Zelle ebenfalls bis B18 nach unten.

Schritt 3: Die Zeitberechnung (Formeln)

Jetzt berechnen wir, wie viele Stunden pro Tag gearbeitet wurden. Excel rechnet intern in Tagen (1 = 24 Stunden), daher müssen wir die Pausen richtig abziehen.

Formel für Netto-Arbeitszeit (Zelle F5): Wenn du Start- und Endzeit als Uhrzeit (z.B. 08:00) eingibst: =(D5-C5)-E5

Hinweis: Wenn du die Pause als einfache Zahl (z.B. 0,5 für 30 Min) eingibst, muss die Formel lauten: =(D5-C5)-(E5/24).

Formatierung: Markiere die Spalte F, mache einen Rechtsklick -> Zellen formatieren -> Uhrzeit -> Wähle das Format 13:30 (Stunden:Minuten).

Schritt 4: Wochenenden optisch hervorheben (Bedingte Formatierung)

Um Fehler bei der Eingabe zu vermeiden, sollten Samstage und Sonntage farblich markiert werden.

  1. Markiere den Bereich A5 bis G18.
  2. Gehe zu Start -> Bedingte Formatierung -> Neue Regel.
  3. Wähle: Formel zur Ermittlung der zu formatierenden Zellen verwenden.
  4. Gib folgende Formel ein: =WOCHENTAG($A5;2)>5
  5. Klicke auf Formatieren -> Ausfüllen und wähle ein helles Grau.
  6. Bestätige mit OK. Nun sind alle Wochenenden automatisch markiert.

Schritt 5: Zusammenfassung für die Lohnabrechnung

Am Ende der 14 Tage benötigen wir die Summen für die Buchhaltung. Erstelle unterhalb der Tabelle (z. B. ab Zeile 20) eine Zusammenfassung.

Gesamtstunden berechnen: =SUMME(F5:F18)

Wichtig für die Lohnabrechnung (Industrie-Stunden): Die Lohnabrechnung benötigt oft Dezimalzahlen (z. B. 7,5 Stunden statt 07:30). Um die Zeit-Summe in eine Dezimalzahl umzuwandeln, nutzt du diese Formel: =SUMME(F5:F18)*24 Formatierung hierfür: Stelle die Zelle auf "Zahl" mit zwei Dezimalstellen um.

Schritt 6: Dropdown für "Art der Zeit" (Optional)

Damit du schnell erfassen kannst, ob jemand krank war oder Urlaub hatte:

  1. Markiere die Zellen G5 bis G18.
  2. Gehe zu Daten -> Datenüberprüfung.
  3. Wähle bei "Zulassen": Liste.
  4. Gib bei "Quelle" ein: Arbeit;Krank;Urlaub;Feiertag.
  5. Jetzt kannst du pro Tag einfach den Status auswählen.

Bonus: Schutz der Formeln

Damit keine Formeln versehentlich gelöscht werden:

  1. Markiere nur die Zellen, in die Daten eingetragen werden (A2, C5:E18, G5:G18).
  2. Rechtsklick -> Zellen formatieren -> Schutz -> Haken bei "Gesperrt" entfernen.
  3. Gehe zu Überprüfen -> Blatt schützen.
  4. Vergib ein Passwort (optional). Jetzt können nur noch die Arbeitszeiten ausgefüllt werden, aber die Automatisierung bleibt sicher.

Fertig! Du hast nun eine professionelle, bi-weekly Zeiterfassung, die sich bei Eingabe eines neuen Startdatums komplett selbst aktualisiert.

0