Tutorials

Tutorial: Automatisierung eines halbmonatlichen Stundenzettels in Excel für die Lohnabrechnung

Melden

Hier ist ein detailliertes Tutorial, wie du einen halbmonatlichen Stundenzettel (Semi-Monthly Timesheet) in Excel automatisierst, um die Lohnabrechnung zu vereinfachen.

In vielen Unternehmen erfolgt die Abrechnung halbmonatlich: Der erste Zeitraum geht vom 1. bis zum 15. und der zweite vom 16. bis zum Monatsende. Dieses Tutorial zeigt dir, wie du eine Vorlage erstellst, die sich automatisch an den gewählten Monat und Zeitraum anpasst.

Schritt 1: Grundstruktur der Tabelle

Erstelle zuerst die Kopfzeilen für deine Daten.

  1. Eingabefelder (oben):
    • Zelle B2: "Monat" (z. B. Januar, Februar...)
    • Zelle B3: "Zeitraum" (Dropdown-Menü: "1-15" und "16-Ende")
  2. Spaltenüberschriften (ab Zeile 5):
    • A: Datum
    • B: Wochentag
    • C: Arbeitsbeginn
    • D: Arbeitsende
    • E: Pause (in Minuten oder Stunden)
    • F: Netto-Stunden

Schritt 2: Dynamische Datumsberechnung

Damit sich das Datum automatisch ändert, wenn du den Zeitraum wechselst, nutzen wir Formeln.

  1. Startdatum festlegen (Zelle A6): Nutze eine Formel, die prüft, ob der erste oder zweite Zeitraum gewählt wurde: =WENN(B3="1-15"; DATUM(2024; MONAT(1&B2); 1); DATUM(2024; MONAT(1&B2); 16)) (Hinweis: Die Jahreszahl dient hier nur als Platzhalter für die Excel-Logik; wir konzentrieren uns auf Monat und Tag.)

  2. Fortlaufende Tage (Zelle A7 bis A21): =A6+1 (Ziehe diese Formel bis Zeile 20 oder 21 herunter).

  3. Monatsende-Logik (Für den Zeitraum ab dem 16.): Da Monate 28, 30 oder 31 Tage haben, muss in den letzten Zeilen (A21 bis A23) eine Prüfung eingebaut werden: =WENN(A20=""; ""; WENN(MONAT(A20+1)<>MONAT(A20); ""; A20+1)) Dies verhindert, dass Tage aus dem Folgemonat angezeigt werden.

  4. Wochentag anzeigen (Spalte B): Gib in B6 ein: =A6 und formatiere die Zelle über Zellen formatieren -> Benutzerdefiniert als TTTT. So wird aus dem Datum automatisch "Montag", "Dienstag" etc.

Schritt 3: Arbeitszeitberechnung

Damit Excel korrekt rechnet, müssen die Zellen für Beginn und Ende als "Zeit" (hh:mm) formatiert sein.

  1. Formel für Netto-Stunden (Zelle F6): =(D6-C6)-(E6/24) (Erklärung: Da Excel Zeit als Bruchteile eines Tages speichert, müssen Pausenminuten durch 24 geteilt werden, wenn sie als Dezimalzahl eingegeben werden).

  2. Fehlermeldungen vermeiden: Nutze WENNFEHLER, falls Zellen leer sind: =WENN(ODER(C6=""; D6=""); 0; (D6-C6)-(E6/24))

Schritt 4: Automatisierte Zusammenfassung für die Lohnabrechnung

Am Ende der Tabelle (z. B. Zeile 25) erstellst du eine Zusammenfassung für die Buchhaltung.

  1. Gesamtstunden: =SUMME(F6:F22) Wichtig: Formatiere diese Zelle mit dem benutzerdefinierten Format [hh]:mm, damit Stunden über 24 korrekt addiert werden.

  2. Überstunden-Berechnung (Optional): Wenn die Sollarbeitszeit z. B. 40 Stunden pro Zeitraum beträgt: =F25 - (40/24)

Schritt 5: Optische Automatisierung (Bedingte Formatierung)

Um Wochenenden hervorzuheben:

  1. Markiere den Bereich A6 bis F22.
  2. Gehe zu Bedingte Formatierung -> Neue Regel -> Formel zur Ermittlung...
  3. Gib ein: =WOCHENTAG($A6;2)>5
  4. Wähle eine graue Füllfarbe. Jetzt werden Samstage und Sonntage automatisch markiert.

Tipps für die Praxis:

  • Datenüberprüfung: Erstelle für das Feld "Monat" (B2) eine Liste über Daten -> Datenüberprüfung, damit nur gültige Monatsnamen eingegeben werden können.
  • Blattschutz: Sperre die Zellen mit den Formeln (Spalte A, B und F), sodass Mitarbeiter nur ihre Arbeitszeiten und Pausen eintragen können.
  • Formatierung: Nutze für die Spalte "Netto-Stunden" immer das Zahlenformat [hh]:mm oder rechne alles in Dezimalzahlen um (Stunden * 24), falls dein Abrechnungssystem Dezimalwerte (z.B. 7,5 statt 7:30) benötigt.
0