Es ist zwar schon etwas spät im neuen Jahr um einen Kalender zu machen, aber es ist trotzdem ein interessantes Beispiel was sich machen lässt mit ganz einfachen Excel Formeln.

Wir wollen also den Kalender erstellen aber nur eine einzige Formel dafür benutzen, so dass wir nicht immer wieder alles neu eingeben müssen.

Die Logik der Kalender Formel

Wer es sich gleich selber in Excel anschauen möchte kann den ExcelKalender runter laden:

Die Formel ist aus 7 anderen Formeln zusammengesetzt: WENN, MONAT, JAHR, DATUM, HEUTE, ZEILE, und SPALTE. Hier die Kalender Formel aus der ersten Zelle welche in B4 steht und den 1. Januar berechnet:

=WENN(MONAT(DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))>ZEILE()-ZEILE(A$3);””;DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))

Kernformel

Ich habe den Kern der Formel unten in Rot angezeigt.

=WENN(MONAT(DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))>ZEILE()-ZEILE(A$3);””;DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))

Fangen wir also nur mit diesem Kernteil an um zu sehen wie das funktioniert:

DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3))

Die Syntax von DATUM lautet DATUM(Jahr; Monat; Tag). Das Ziel ist diese Argumente der DATUM Formel so zu schreiben dass sie sich neu berechnen wenn die Formel in eine andere Zelle kopieren. So müssen wir also das Jahr wie auch den Monat und den Tag immer wieder neu herstellen.

Das Jahr

DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3))

Das Jahr berechnen wir mit Hilfe der HEUTE Formel aus welche uns den jeweiligen Tag angibt. In dem wir JAHR(HEUTE()) schreiben reduzieren wir das aktuelle Datum auf das Jahr. Weil das Jahr konstant bleibt im Kalender müssen wir uns hier keine weiteren Gedanken machen.

Der Monat

DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3))

Da wir nur eine Formel benutzen wollen, das Jahr aber 12 Monate hat, muss sich der Monat berechnen je nach dem wo wir die Formel hin kopieren. Da wir die Monate in den Zeilen anzeigen möchten, benutze ich ZEILE() um die aktuelle Zeile der Zelle zu berechnen. Von dem Ergebnis subtrahieren wir ZEILE(A$3). So stellen wir sicher dass die Zelle in welcher der Kalender beginnt immer bei 1, also der erste Monat, anfängt.

Der Tag

DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3))

Beim Tag haben wir das gleiche Problem wie beim Monat. Die Tage müssen sich ändern wenn wir die Formel kopieren. Weil wir die Tage aber in den Spalten anzeigen benutzen wir die SPALTE Formel. Gleich wie beim Monat ziehen wir das Resultat von SPALTE(A$3) ab von der aktuellen Spalte damit wir immer beim 1. Tag im Monat anfangen.

Nun haben wir sichergestellt dass die Formel immer ein neues Datum produziert wenn wir sie in eine neue Zelle kopieren, weil jede Zelle eine andere Kombination von Spalte und Zeile hat.

Tage Ausblenden

Man würde annehmen dass wenn die kopierte Formel einen Tag berechnet den es nicht gibt, wie zum Beispiel den 31. September, dass Excel einen Fehler anzeigen würde. Macht Excel aber nicht! Es geht einfach zum nächsten logischen Tag welcher der 1. Oktober ist. Diese interessante Eigenart von Excel müssen wir in unserem Kalender unterbinden.

Dies machen wir in dem wir mit der WENN Formel eine Prüfung erstellen ob der Berechnete Tag noch dem aktuellen Monat angehört.

Hier nochmals die ganze Formel. Die Kernformel ist in Blau. Wie man sieht wird diese wiederholt im ersten Teil der WENN Formel. Der Rest ist in Rot angezeigt.

=WENN(MONAT(DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))>ZEILE()-ZEILE(A$3);””;DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))

Die WENN Formel kommt hier eigentlich nur zum Einsatz damit wir die Datum welche nicht mehr in den Monat gehören ausblenden können mit “”. Die Prüfung seht Ihr im ersten Teil der WENN Formel wo wir folgendes Argument auf Wahr oder Falsch prüfen:

MONAT(Kernformel)>ZEILE()-ZEILE(A$3)

Ist der aktuelle Monat der Kernformel grösser als die berechnet Zeile, welche ja auch den Monat darstellt, so wird das Argument Wahr, und das Resultat wechselt auf “”.

Somit haben wir eine einzige Formel erstellt welche alle Tage berechnet!

Formatierungen für den Kalender

Damit der Kalender auch optisch ansprechend ist habe ich noch ein paar zusätzlich Eingriffe vorgenommen.

Bedingte Formatierungen

Den Rest des Kalenders wird über bedingte Formatierungen erstellt.Damit die bereits verstrichenen Tage ausgeblendet, bez. mit Grau versehen sind, werden sie geprüft mit:

=HEUTE()>B4

Damit sich die Wochenende abheben, werden diese Hellblau hervorgehoben mit folgender Formel:

=WOCHENTAG(B4;2)>=6

Letztendlich möchten wir nur dort einen Rahmen haben wo auch ein Datum drinsteht. Also kommt noch eine letzte Prüfung hinzu:

=B4<>””

Header

Der Header mit dem Jahr ist ein einfaches Objekt welches mit der Zelle A1 verlinkt ist. Hier wurde ebenfalls wieder die Formel JAHR und HEUTE verwendet damit auch der Teil automatisiert ist.