Excel Kalender: Wochenenden einfärben

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.

Liste Zellen formatieren

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.

Liste erstellen

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.

Funktion 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.

Di ausrechnen

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.

Kalenderkopf

Jetzt werden die Wochenenden farblich gekennzeichnet. Dazu nutzen wir die bedingte Formatierung.
B6:AF7 auswählen, bedingte Formatierung, neue Regel… auswählen.

bedingte Formatierung Bild 1

Im nächsten Fenster „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ anklicken.

bedingte Formatierung Bild 2

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.

bedingte Formatierung Bild 3

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.

Monatsende bed. Formatierung

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.

 

Viel Spaß beim Ausprobieren wünscht das Excellernen Team!

3 Comments

  • Funktioniert das auch wenn man die Tage senkrecht anordnen will?

    • Andreas

      Hallo Mo5eZ,

      ja, es funktioniert auch senkrecht. die einzige Änderung, die du vornehmen musst, ist die Formel zur bedingten Formatierung. Die $-Zeichen stehen für die senkrechte Eingabe an der falschen Stelle. Sie setzen die Zeile fest. Ändere das $ bitte auf vor die Spalte. Aus B$5 muss $B5 werden.

      Gruß Foulcrow

  • Mit der Wochentag-Funktion geht das meiner Ansicht nach viel einfacher.

    Wenn in Zelle A1 das Datum steht sind alle Informationen enthalten.
    Benutzerdefiniert wird nur der Tag angezeitgt.

    In die Bedingte Formatierung wird folgende Formel eingetragen
    =WOCHENTAG(A1;2)>5

    Die Option 2 in der Formel ändert den Zahl von Sonntag=1 auf Montag=1.

    Somit sind werden die Tage Samstag=6 und Sonntag=7 über die bedingte Formatierung markiert.

Frage oder Kommentar zu diesem Artikel?