Zunächst wird Tabelle1 in Kalender und Tabelle2 in Daten umbenannt. In das Tabellenblatt Daten kommen die Einträge für eine Liste rein, vom 01.01.2014 bis 01.12.2020. Die Einträge werden dann benutzerdefiniert in MMMM JJJJ umformatiert, so dass der ausgeschriebene Monat und das Jahr als Eintrag stehen.
Jetzt ändern wir im Tabellenblatt Kalender die Spaltenbreite auf 2,5 und verbinden die Zellen B2 bis F2 rechtsbündig. In dieser Zelle erstellen wir nun eine Liste mit den Datumswerten aus Tabellenblatt Daten und formatieren ebenfalls benutzerdefiniert im Format MMMM JJJJ. Dadurch werden aus den Zahleneinträgen wieder Datumsangaben.
Ab B7 werden waagerecht die Zahlen 1 bis 31 für die Monatstage ausgefüllt. Die 1 schreiben und mit Strg + linker Maustaste bis zur 31 ziehen (am Ende erst die Maustaste loslassen).
In der Liste wählen wir den September 2014 aus und ordnen nun die Wochentage den Zahlen von 1 bis 31 zu, egal ob der Monat 30 oder 31 Tage hat. Die Funktion hierfür heißt Wochentag.
Zahl: ist das Datum aus unserer Liste
Typ: für uns die 1
Als Formelergebnis bekommen wir die 2, was einem Montag entspricht. Die Zellen B6 bis AF6 bekommen Schriftgröße 10 (damit uns der Montag nicht die Spalten vergrößert), Fett, zentriert und TTT bei der benutzerdefinierten Formatierung.
Wie kommen wir jetzt zum Dienstag? Montag +1. Der Di kann bedenkenlos bis zur 31 gezogen werden.
In B5:AF5 werden Hilfseinträge zur bedingten Formatierung erstellt. Diese werden später in weiß geschrieben und sind so nicht mehr zu sehen.
Dafür verwenden wir in B5 wieder die Funktion Wochentag. Doch diesmal ist unsere Zahl der Montag aus B6 und der Typ wieder 1. Als Ergebnis bekommen wir die 2 und ziehen sie bis AF5.
Jetzt werden die Wochenenden farblich gekennzeichnet. Dazu nutzen wir die bedingte Formatierung.
B6:AF7 auswählen, bedingte Formatierung, neue Regel… auswählen.
Im nächsten Fenster „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ anklicken.
In die leere Zeile klicken und folgende Formel eintippen: =oder(B$5=1;B$5=7). Danach Formatieren… anklicken und bei Register Schrift und Register Ausfüllen die Farben nach eigenem Ermessen auswählen. Mit OK das Fenster schließen. Ein Klick auf die Liste und die Auswahl eines anderen Monats oder Jahres zeigen, ob die bedingte Formatierung richtig ist. Die Wochenenden müssen eingefärbt mitwandern.
Profi Tipp: Monatsende, Anzahl der Tage eines Monates = bedingte Formatierung
Jeder zweite Monat endet an einem anderen Tag. Alle vier Jahre kommt sogar noch das Schaltjahr hinzu. Folgende bedingte Formatierung erkennt das Monatsende.
Den Bereich AD5:AF6 auswählen und unten stehende Formel in der bedingten Formatierung übernehmen und von den Zellen her gegebenenfalls dem eigenen Kalender anpassen. Dadurch wird die Anzahl der Wochentage im aktuellen Monat erkannt. Die Zahlen bekommen eine weiße Formatierung, die Zellen bekommen keine Hintergrundfüllung.
Zur Erklärung der Formel:
Das Monatsende aus B2 ist der 28.02.2014. Gebraucht wird jedoch nur der Tag, also die 28. Excel vergleicht die Zahl in AD6 ob sie größer ist als der Tag des Monatsendes in B2. Ist das der Fall, bekommt die Zelle AD6 die oben beschriebene Formatierung.
