Tutorials

Tutorial: Dynamischer Lohnabrechnungskalender in Excel erstellen

Melden

Hier ist ein umfassendes Tutorial, wie du einen dynamischen Lohnabrechnungskalender (Payroll Calendar) in Excel erstellst. Das Ziel ist ein Kalender, der sich automatisch aktualisiert, wenn du den Monat oder das Jahr änderst, und wichtige Zahltage hervorhebt.

Ein dynamischer Kalender spart Zeit und minimiert Fehler, da er sich automatisch an das jeweilige Jahr und den Monat anpasst.

Schritt 1: Die Grundstruktur (Input-Bereich)

Zuerst legen wir fest, für welchen Zeitraum der Kalender gelten soll.

  1. Öffne ein leeres Excel-Blatt.
  2. Erstelle zwei Eingabefelder oben links:
    • Zelle B2: Beschriftung "Jahr" | Zelle C2: (z.B. 2024)
    • Zelle B3: Beschriftung "Monat" | Zelle C3: (Zahl von 1 bis 12)
  3. Benenne die Zelle C2 im Namensfeld (oben links neben der Bearbeitungsleiste) als K_Jahr und C3 als K_Monat. Das macht die Formeln lesbarer.

Schritt 2: Berechnung des Startdatums

Wir müssen Excel mitteilen, mit welchem Tag der gewählte Monat beginnt.

  1. Schreibe in eine Hilfszelle (z.B. B5): =DATUM(K_Jahr; K_Monat; 1)
  2. Formatiere diese Zelle als Datum. Dies ist der erste Tag deines gewählten Monats.

Schritt 3: Das Kalender-Gitter aufbauen

Ein Standardkalender hat 7 Spalten (Montag bis Sonntag).

  1. Schreibe in die Zeile 7 (von Spalte B bis H) die Wochentage: Mo, Di, Mi, Do, Fr, Sa, So.
  2. Jetzt berechnen wir den ersten Tag, der im Gitter angezeigt wird. Oft beginnt ein Monat mitten in der Woche. Damit das Gitter immer ausgefüllt ist, nutzen wir eine Formel in Zelle B8: =$B$5-WOCHENTAG($B$5;2)+1 (Diese Formel berechnet den Montag der Woche, in der der 1. des Monats liegt.)

Schritt 4: Die Datumslogik ausfüllen

  1. Zelle C8 (Dienstag): =B8+1
  2. Ziehe diese Formel rüber bis Spalte H (Sonntag).
  3. Zelle B9 (nächste Woche Montag): =H8+1
  4. Ziehe die Formel von B9 wieder nach rechts bis Spalte H.
  5. Kopiere die Zeile 9 nun nach unten, bis du insgesamt 6 Zeilen für das Datum hast.

Tipp: Markiere alle Datumszellen und wähle unter "Zellen formatieren" -> "Benutzerdefiniert" den Typ "t". So wird nur die Zahl des Tages angezeigt (z.B. "1" statt "01.01.2024"), was den Kalender übersichtlicher macht.

Schritt 5: Bedingte Formatierung (Optik & Fokus)

Damit du siehst, welche Tage zum aktuellen Monat gehören und welche zum Vor- oder Folgemonat:

  1. Markiere alle Datumszellen (B8 bis H13).
  2. Gehe zu Bedingte Formatierung -> Neue Regel -> Formel zur Ermittlung....
  3. Gib folgende Formel ein: =MONAT(B8)<>K_Monat
  4. Wähle unter "Formatieren" eine graue Schriftfarbe. Nun werden Tage außerhalb des gewählten Monats ausgegraut.

Schritt 6: Zahltage (Payroll) markieren

In einem Lohnkalender sind fixe Zahltage wichtig (z.B. der 25. des Monats oder der letzte Werktag).

Beispiel: Markierung des 25. jeden Monats:

  1. Markiere wieder den Kalenderbereich.
  2. Neue Regel für bedingte Formatierung: =UND(TAG(B8)=25; MONAT(B8)=K_Monat)
  3. Wähle eine auffällige Füllfarbe (z.B. Grün) und fette Schrift.

Beispiel: Markierung des letzten Werktags im Monat:

  1. Regel: =B8=NETTOARBEITSTAGE.INTL(DATUM(K_Jahr;K_Monat+1;0);DATUM(K_Jahr;K_Monat+1;0);1) (Dies markiert automatisch den letzten Arbeitstag des aktuellen Monats.)

Schritt 7: Wochenende hervorheben

Um die Wochenenden optisch zu trennen:

  1. Markiere den Bereich.
  2. Neue Regel: =WOCHENTAG(B8;2)>5
  3. Wähle eine leichte Hintergrundschattierung.

Bonus: Feiertage integrieren

Wenn du eine Liste mit Feiertagen in einem separaten Tabellenblatt hast (Name des Bereichs z.B. Feiertage):

  1. Markiere den Kalender.
  2. Regel: =ZÄHLENWENN(Feiertage; B8)>0
  3. Wähle eine rote Schriftfarbe oder einen besonderen Rahmen.

Zusammenfassung der Dynamik

Du hast nun ein System geschaffen, bei dem du lediglich in C2 (Jahr) und C3 (Monat) die Werte ändern musst. Der gesamte Kalender inklusive der Lohn-Zahltage, Wochenenden und Monatsgrenzen passt sich sofort automatisch an.

0