Ewiger Jahreskalender in Excel erstellen: Nur 1 Formel
Die Frage, wie sich ein Jahreskalender in Excel dauerhaft und ohne jährlichen Neuaufbau erstellen lässt, begegnet mir in meinen Schulungen und Live-Fragestunden immer wieder, meist mit demselben Hintergrund: Der alte Kalender wird kopiert, die Monate neu beschriftet, die Wochenenden von Hand eingefärbt. Dabei genügt für einen kompletten, sich selbst aktualisierenden Jahreskalender 1 einzige Formel, ganz ohne VBA oder Makros.
In diesem Beitrag zeige ich Ihnen den kompletten Aufbau Schritt-für-Schritt erklärt.
Kurz zusammengefasst:
Mit einer Formel aus DATUM, SPALTE, ZEILE und WENN erstellen Sie in Excel einen Jahreskalender, der sich bei jeder neuen Jahreszahl automatisch aktualisiert, inklusive korrekt berechneter Schaltjahre.
Über eine zusätzliche bedingte Formatierung mit der WOCHENTAG-Funktion werden Samstage und Sonntage automatisch farblich hervorgehoben. VBA oder Makros brauchen Sie dafür nicht.
Jahreszahl und Tabellenstruktur vorbereiten
- Legen Sie ein neues Tabellenblatt an.
- In A1 tragen Sie eine Überschrift ein, direkt darunter die Jahreszahl, in diesem Beispiel in Zelle A2. Diese Jahreszahl ist Ihre Ausgangsbasis für die Berechnung aller Tage.
- Darunter richten Sie die Struktur des Kalenders ein:
Spalte A für Januar, die restlichen Monate spaltenweise daneben, das geht ja fix mit der Ausfüllfunktion. - Die Formel selbst tragen Sie erstmals in Zelle A4 ein, also 1 Zeile unter der Jahreszahl und den Monatsüberschriften.
Die Excel-Formel für den Jahreskalender
Die komplette Formel lautet:
=WENN(MONAT(DATUM($A$2;SPALTE();ZEILE()-3))=SPALTE();DATUM($A$2;SPALTE();ZEILE()-3);"")
Das sieht komplex aus, lässt sich aber in einzelne, gut verständliche Bestandteile zerlegen.
Die DATUM-Funktion
Die Funktion DATUM setzt sich aus 3 Argumenten zusammen: Jahr, Monat, Tag. Im Beispiel:
- Jahr:
$A$2, mit absolutem Bezug ($ Dollarzeichen), damit sich dieser Wert beim Kopieren der Formel nicht verschiebt - Monat:
SPALTE() - Tag:
ZEILE()-3
Der Monat über SPALTE()
Die Funktion SPALTE() ermittelt die Nummer der Spalte, in der Ihre aktive Zelle steht. Beginnt der Januar in Spalte A, liefert SPALTE() die Zahl 1, was genau dem Monat Januar entspricht. Rückt die Formel eine Spalte weiter nach rechts, liefert SPALTE() automatisch die 2, also Februar, und so weiter. Sie müssen den Monat also nirgends manuell eintragen.
Der Tag über ZEILE()
Die Funktion ZEILE() ermittelt die Nummer der Zeile, in der die Zelle steht. Da Ihre Formel nicht in Zeile 1, sondern in Zeile 4 beginnt, müssen Sie von der aktiven Zeilennummer 3 abziehen, damit als erster Tageswert wieder die 1 herauskommt. Ziehen Sie die Formel weiter nach unten, zählt ZEILE()-3 automatisch mit: Zeile 5 ergibt 2, Zeile 6 ergibt 3, und so weiter.
Mit diesen 3 Bestandteilen liefert DATUM($A$2;SPALTE();ZEILE()-3) bereits ein vollständiges, korrektes Datum, das sich automatisch an Spalte und Zeile anpasst.
WENN-Funktion: Nur die passenden Tage je Monat anzeigen
Kopieren Sie die reine DATUM-Formel einfach weit genug nach unten, entstehen an einem bestimmten Punkt Werte aus dem Folgemonat: In der Spalte für Januar tauchen dann plötzlich der 1., 2. und 3. Februar auf, weil Excel einfach weiterzählt. Das wollen Sie nicht, denn in der Januar-Spalte sollen ausschließlich Januar-Werte stehen, in der Februar-Spalte ausschließlich Februar-Werte.
Genau hier kommt die WENN-Funktion ins Spiel. Sie prüft, ob der Monat des berechneten Datums mit der Spaltennummer übereinstimmt:
MONAT(DATUM($A$2;SPALTE();ZEILE()-3))=SPALTE()
Stimmen Monat und Spaltennummer überein, liegt das Datum noch innerhalb des richtigen Monats, und es wird ausgegeben. Stimmen sie nicht überein, gibt die Formel stattdessen einen leeren Text "" zurück. Dadurch bricht die Anzeige am jeweiligen Monatsende sauber ab, unabhängig davon, ob ein Monat 28, 30 oder 31 Tage hat.
Formel auf alle Monate übertragen
- Markieren Sie in der Spalte für Januar alle 31 Zeilen (mehr Tage hat kein Monat)
- Ziehen Sie die Formel mit der Ausfüllfunktion bis zur letzten Monatsspalte im Dezember. Dadurch kopieren Sie die Formel.
- Durch die WENN-Prüfung erscheint in jeder Spalte automatisch nur die passende Anzahl an Tagen, kürzere Monate bleiben in den überzähligen Zeilen einfach leer.
Datumswerte richtig formatieren
Ohne Formatierung zeigt Excel das Ergebnis der Formel zunächst als Dezimalzahl an, da ein Datum intern immer als Zahl gespeichert wird.
- Markieren Sie deshalb alle Zellen mit der Formel und öffnen Sie über Start, Gruppe Zahl, das Dialogfeld Zellen formatieren.
- Da die Jahreszahl bereits oben im Kalender steht, ist sie in den einzelnen Datumswerten überflüssig.
- Wählen Sie deshalb Benutzerdefiniert und entfernen Sie im Feld Typ den Platzhalter für das Jahr. Übrig bleiben Tag und Monat.
- Möchten Sie zusätzlich den Wochentag in abgekürzter Form sehen, ergänzen Sie vorne 3-mal den Platzhalter T.
Wochenenden automatisch farblich hervorheben
Damit Samstage und Sonntage automatisch erkennbar sind, ohne dass Sie bei jedem neuen Jahr von Hand nachfärben müssen, kommt die Bedingte Formatierung zum Einsatz.
- Markieren Sie die Datumswerte, gehen Sie über Start, Gruppe Formatvorlagen, zu Bedingte Formatierung
- Legen Sie eine neue Regel mit einer Formel an:
=WOCHENTAG(A4;2)>5
- Die Funktion WOCHENTAG ermittelt anhand des Datums, um welchen Wochentag es sich handelt. Mit dem Typ 2 liefert sie für Montag die 1 und für Sonntag die 7. Ist das Ergebnis größer als 5, handelt es sich also um Samstag oder Sonntag.
- Ein wichtiger Punkt bei der Bedingten Formatierung:
Klicken Sie eine Zelle im Formelfeld an, setzt Excel automatisch einen absoluten Bezug mit Dollarzeichen. Für diese Regel brauchen Sie aber einen relativen Bezug, damit die Prüfung für jede Zelle einzeln funktioniert. Entfernen Sie die Dollarzeichen also unbedingt, bevor Sie die Regel speichern. - Legen Sie anschließend die gewünschte Formatierung fest, zum Beispiel eine Füllfarbe, und bestätigen Sie.
Ab sofort werden alle Samstage und Sonntage in Ihrem Kalender automatisch hervorgehoben, auch nach einem Jahreswechsel.
Kalender fürs nächste Jahr aktualisieren
Damit ist Ihr Kalender komplett aufgebaut.
Wollen Sie ihn für ein anderes Jahr nutzen, ändern Sie ausschließlich die Jahreszahl in Zelle A2. Sämtliche Datumswerte, die Zuordnung zu den richtigen Monaten und die Hervorhebung der Wochenenden passen sich automatisch an, auch bei einem Schaltjahr wie 2028 mit einem 29-tägigen Februar.
Häufige Fragen zum ewigen Jahreskalender in Excel
Nein. Die komplette Lösung basiert ausschließlich auf einer Formel aus DATUM, SPALTE, ZEILE und WENN sowie einer bedingten Formatierung. Ein Makro brauchen Sie an keiner Stelle.
Ja. Da die Formel auf der DATUM-Funktion basiert, berechnet Excel Schaltjahre automatisch korrekt, der Februar zeigt in einem Schaltjahr wie 2028 automatisch 29 statt 28 Tage.
Die verwendeten Funktionen sind seit vielen Jahren Standardbestandteil von Excel, die Lösung funktioniert daher auch in älteren Excel-Versionen zuverlässig.
Die in diesem Beitrag gezeigte Formel deckt Wochenenden ab. Für eine automatische Feiertagsberechnung inklusive Formatierung finden Sie die fertige, kostenlose Vorlage samt Anleitung hier.
Passen Sie die Zahl in ZEILE()-3 an Ihre tatsächliche Startzeile an. Beginnt Ihr Kalender zum Beispiel in Zeile 5, ändern Sie den Wert auf ZEILE()-4, damit als erster Tag wieder die 1 herauskommt.
Der Aufwand steckt einmalig im Aufbau der Formel und der Formatierung. Danach reduziert sich Ihre jährliche Kalendererstellung auf eine einzige Zahl.
Möchten Sie sich den kompletten Ablauf noch einmal Schritt für Schritt am Bildschirm ansehen?
Das passende Video dazu finden Sie auf meinem YouTube-Kanal.
Bauen Sie sich das lieber nicht selbst und wünschen sich zusätzlich automatisch eingefärbte Feiertage?
Dann laden Sie sich hier die fertige Vorlage kostenlos herunter: Ewiger Jahreskalender mit Feiertagen
Brauchen Sie außerdem noch die passende Kalenderwoche zu einem Datum?
Wie Sie diese in Excel automatisch berechnen, zeige ich Ihnen in einem separaten Video: Kalenderwoche in Excel berechnen
Ich wünsche Ihnen viel Erfolg beim Umsetzen!!!
Melden Sie sich gerne bei Fragen zum Thema bei mir, entweder per E-Mail oder als Kommentar hier unter den Beitrag.
Ihre
Anuschka Schwed
MS-Office-Expertin
