Monatskalender, verschiedene Wege zum Ziel „Monatsende”
Für verschiedene Zwecke werden Monatskalender gebraucht. Dabei wird auf 1 Arbeitsblatt genau 1 Kalendermonat erstellt. Für die ersten 28 Tage ist das immer der gleiche Weg, der individuell gewählt werden kann. Ab dem 29. ist es vom Monat abhängig, ob der entsprechende Tag in dem Monat existiert oder nicht.
Beispiel (erst einmal) für die ersten 28 Tage
Zugegeben, auch hier gibt es verschiedene Wege, die vom persönlichen Geschmack geprägt sind. Aber da diese nicht das eigentliche Thema sind, an dieser Stelle nur ein Weg stellvertretend für viele. Aber dafür in ganz kleinen Schritten gut nachvollziehbar, auch für Einsteiger in Sachen Excel.
Kopf-Bereich
In A1 kommt erst einmal ein einfacher Text: Monat:. In B1 schreiben Sie irgend einen Tag des gewünschten Monats. Das kann ein „normales” Datum sein wie 24.2.2016, aber auch Feb 16 oder 2⁄16 sind denkbar. Sie erkennen, dass nicht unbedingt der Monatserste verwendet werden muss.
Nun ist es mehr als hilfreich, wenn Sie B1 mit einem bestimmten Zahlenformat versehen. Es soll nur der Monat und das Jahr angezeigt werden:
Nur Monat und Jahr
Dazu wählen Sie im Menü Start, Gruppe Zahl das Benutzerdefinierte Zahlenformat aus und geben MMMM JJJJ als Typ: ein. Im obigen Beispiel ist ein hellblauer Hintergrund gewählt und ein dünner Rahmen unten gesetzt worden.
Die Tage des Monats in Zeilen
In der Zelle A3 soll automatisch der Monatserste des Monats eingegeben bzw. ausgefüllt werden, der in B1 steht. Dazu geben Sie dort diese Formel ein:
=DATUM(JAHR($B$1);MONAT($B$1);1)
Zugegeben, die Dollar-Zeichen sind in diesem Fall nicht unbedingt erforderlich, aber sie schaden auch nicht. 😉 Hiermit ist nun gewährleistet, dass auch dann der Erste des entsprechenden Monats in A3 steht, wenn in B1 eine Änderung vorgenommen wird. Probieren Sie es doch einfach einmal aus, es klappt.
Der nächste Schritt ist recht unkompliziert. Sie schreiben in A4 die Formel =A3+1. Damit wird der Wert aus der Zelle darüber genau um 1 (Tag) erhöht. Nun ziehen Sie die Zelle A4 mit dem Ausfüllkästchen bis zur Zeile 30 nach unten. Der letzte Wert ist nun der 28. des jeweiligen Monats. Dieses ist der letzte Tag, den es in jedem Monat jeden Jahres gibt. Denn den 29.2. gibt es nur alle 4 Jahre. Bei dieser Gelegenheit sollten Sie erforderlichenfalls B1 auf den Februar 2016 setzen.
Auch wenn es zum falschen Ergebnis führt, sollten Sie nun die Formel bis zur Zeile 33 nach unten ausfüllen. Und da ja dank der Formel immer genau 1 Tag erhöht wird, sind auch Tage aus dem Folgemonat sichtbar:
Der „Überhang” sollte nicht erscheinen
Die Darstellung im Bild hierüber sollte Sie nicht irritieren. Der besseren Übersicht wegen habe ich die ersten drei Zeilen des Fensters fixiert. – Das Ziel aber ist klar: Es sollen nur Tage des Monats angezeigt werden, der in B1 ausgewiesen ist. Nicht mehr. Und da gibt es diverse Möglichkeiten. Gute aber auch weniger gute; wobei in erster Linie die Gesamt-Sicht der Auswirkung wichtig ist, nicht die Einfachheit der Anwendung.
Die letzten möglichen drei Tage
Sie haben eben erkannt, dass mit der bisherigen Formel konsequent 31 Tage seit dem Monatsersten angezeigt werden. Dabei wird nicht geprüft, ob der entsprechende Tag noch zum oben ausgewiesenen Monat gehört oder nicht. Auf die scheinbar einfachste Möglichkeit, Überhänge einfach per Hand zu löschen, will ich hier nicht weiter eingehen. Das ist wirklich zu unprofessionell … 😥
Unsichtbar (1)
Wenn es ausschließlich darum geht, Werte des Folgemonats nicht anzuzeigen, dann bietet sich eine irgendwie faszinierende Möglichkeit an: Bedingte Formatierung. Um alle Werte, die außerhalb des gewünschten Bereichs liegen unsichtbar zu machen, markieren Sie den Bereich A3:A33 und geben bei Bedingte Formatierung die folgende (neue) Formel ein:
=MONAT(A3)<>MONAT($B$1)
und bei Formatieren… wählen Sie die Registerkarte Zahlen, die Kategorie: ist dann Benutzerdefiniert und als Typ: dann ;;;, also nur 3 Semikola.
Damit ist der Zweck erfüllt. Aber beachten Sie, dass keine andere Funktion in irgend einer Form abfragen sollte, ob in der entsprechenden Zelle (beispielsweise A32) eine Zahl oder ein Datum steht. Das kann zu Fehlergebnissen führen, denn es steht ja ein entsprechender Wert in der Zelle, wenn auch nicht sichtbar.
Unsichtbar (2)
Wenn Ihnen die erste Version zu „unheimlich” ist, weil Sie dann nichts mehr per Hand formatieren können, dann gibt es eine weitere Möglichkeit. Markieren Sie die Zellen, in denen der Folgemonat aufgeführt ist und verwenden Sie einfach die Schriftfarbe weiß. Auf dem (normalerweise) weißem Hintergrund wird dann auch nichts dargestellt.
Das muss dann zwar für jeden Monat neu „eingefärbt” werden, bei Monaten mit 31 Tagen entfällt das sogar komplett aber Sie können auch beispielsweise einen Grauton oder eine beliebige andere Farbe oder Formatierung für die entsprechenden Tage verwenden. – Ansonsten gelten die gleichen Einschränkungen wie hierüber bereits genannt.
Inhaltslos (leer)
Prinzipiell ist es die sinnvollste Lösung, einfach „nichts” in die entsprechende Zelle der Spalte A zu schreiben, wenn das berechnete Datum nicht im Monat der Zelle B1 liegt. Dazu allerdings muss die Formel zur Berechnung des Folgetages in Spalte A geändert werden. Zumindest teilweise, denn bis zum 28. Tag des Monats stimmt die Formel ja auf jeden Fall. Beginnend in A31 schreiben Sie diese Formel:
=WENN(MONAT(A28+3)=MONAT($B$1);A28+3;"")
und ziehen Sie diese Formel bis A33 nach unten. Prüfen Sie gerne mit verschiedenen Monaten, ob das Ergebnis stimmt. Es wird hinhauen. Falls Sie überlegen, warum ich in der ersten geänderten Formel nicht MONAT(A30+1) als Referenz verwendet habe, dann probieren Sie es gerne einmal aus … 😕
Hinweis: Diese Form wird sehr oft verwendet. Es ist in vielen Fällen die „sauberste” Lösung. Aber bedenken Sie, dass diese "" – Zellen nicht wirklich leer sind. Sie enthalten eine Zeichenkette (einen String) der Länge null.
Unsichtbar (3)
Es ist durchaus denkbar, dass in einer Spalte der entsprechenden Zeile eine Formel bzw. Funktion überprüft, ob in Spalte A ein (gültiges) Datum steht. Ein markantes Beispiel wäre, dass bei einem Datumswert abgefragt wird, ob es ein Wochenende ist oder nicht. Falls „nein”, dann wird automatisch eine Soll-Arbeitszeit in die Zelle eingetragen.
Die beiden eben genannten „Unsichtbar”-Methoden hätten dann ja den Nachteil, dass durchaus in der entsprechenden Zelle ein gültiges Datum steht. Es ist nur nicht sichtbar. Und die ""-Lösung kann bei einer Prüfung durchaus zu einer sehr aufwendigen Formel führen, wenn Fehler ausgeschlossen werden sollen. Da bietet sich eine Methode an, die viel Ähnlichkeit mit der ersten vorgestellten Lösung hat und etwas „tricky” ist: Wenn das berechnete Datum nicht im Referenzmonat liegt, dann wird ein Null in die Zelle geschrieben: =WENN(MONAT(A28+3)=MONAT($B$1);A28+3;0)
Prinzipiell ist es die gleiche Formel wie in dem vorherigen Lösungsansatz, nur wird kein LeerString eingefügt sondern die Zahl, nein der Wert 0. Und wahrscheinlich wird bei Ihnen ein wirklich eigenartiges Datum in den entsprechenden Zellen stehen, wenn sie nicht zum Monat gehören. Das wird dann der 00.01.1900 sein. Der Excel-Logik folgend ist (zumindest in der Windows-Version) der 1.1.1900 mit dem Wert 1 belegt. Die Null ist ja eins weniger und Excel drückt das in dieser Form als Datumswert aus. Mehr soll an dieser Stelle nicht dazu gesagt werden, es würde zu weit führen.
In Sachen Formatierung, genauer gesagt Zahlenformat können Sie nun A3:A33 markieren und beispielsweise dieses Benutzerdefinierte Format verwenden: TT.MM.JJJJ;; wobei der Teil vor dem ersten Semikolon von Ihnen nach Wunsch angepasst werden kann. Die beiden folgenden Strichpunkte (Semikola) sind Pflicht.
Diese Methode ist mein persönlicher Favorit geworden. Nicht nur, weil es eine Mischung der verschiedenen vorher gezeigten Wege ist, nein mir ist es ausgesprochen wichtig, dass in der Spalte A eine Zahl steht, die auf einfache Weise als Datum identifizierbar ist. Und das ist nun wirklich einfach machbar, denn wenn die Zahl größer ist als Null, dann ist es ein Datum des entsprechenden Monats, sonst eben nicht.
Hier ist bewusst auf Funktionen wie MONATSENDE() verzichtet worden, so können sie die aufgezeigten Wege in praktisch jeder Excel-Version anwenden. – Es gibt noch viele andere Wege, um zum gewünschten Ziel zu gelangen. Wichtig ist im Endeffekt nur, dass das gewünschte Ziel so weitgehend wie möglich erreicht wird und Sie nach Möglichkeit auch verstehen, wie das Ganze funktioniert.