Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 3 – Die Bedingte Formatierung meistern

kalender

Ziel die­ses Work­shops ist es einen Kalen­der zu erstel­len, der nur durch Ein­gabe der Jah­res­zahl auto­ma­tisch ein Kalen­der­blatt erzeugt, in dem die Wochen­en­den und die gesetz­li­chen Fei­er­tage mar­kiert und die Anzahl der Arbeits­tage ange­zeigt wer­den. Der Kalen­der soll für die Nut­zung der Design-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 3. Teil des Work­shops meistern Sie die “Bedingte Formatierung”.

Sie könnten die beiden ersten Teile der Workshops “Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 1 — Grundlagen” und “Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 2 – Programmierung” nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der beiden Dateien herunter und starten mit dem Workshop Ihrer Wahl:

Die Dateien des Workshop Teil 3 finden Sie wie immer am Ende dieses Blog Post. Klicken Sie auf ein Bild für eine vergrößerte Darstellung.

Was noch fehlte

Im Teil 2 dieser Workshop Reihe fehlte noch das Formatieren der Zellen in denen ein Feiertag oder möglicherweise die Kalenderwoche steht. die Berechnung hatten Sie schon programmiert. Markieren Sie bitte die Zellen A4:F4 und füllen Sie sie bitte bis zur Zeile 9 nach unten aus. Wenn Sie alles richtig gemacht haben, dann sollten Sie beim 1. Januar linksbündig “Neujahr” stehen haben und beim 3. Januar und 7. Februar eine 1 bzw. 6, das sind die Kalenderwochen. Wir möchten im Feld aber 1. KW bzw. 6. KW stehen haben, also bemühen Sie die benutzerdefinierte Zellformatierung. Markieren Sie bitte die Zellen C3:C9 und F3:F9 (<STRG> Taste), klicken mit der rechten Maus und wählen “Zellen formatieren”. Geben Sie als Typ [0”. KW”] ohne die eckigen Klammern ein. Also Null Anführungszeichen, Punkt, Leerzeichen, KW und dann Anführungszeichen.

Markieren Sie bitte die Zellen A4:F4 und füllen Sie sie bitte bis zur Zeile 9 nach unten aus.  Geben Sie als Typ [0”. KW”] ohne die eckigen Klammern ein.

Schön erkenn Sie, wir haben zwar für alle Zellen eine Zahlenformatierung definiert, diese gilt aber nur für die Zahlen, hier die Kalenderwochen. Wenn Sie einmal die Zellen markiert haben, ordnen Sie den Inhalt bitte linksbündig an.

Nur Zellen mit Kalenderwochen sind von der Formatierung betroffen

Die Bedingte Formatierung

Folgende Formatierungen für Feiertage, Wochenenden und Montage wollen wir vornehmen:

  • der Tag soll eine mittlere Füllfarbe und weiße Schrift bekommen,
  • der Wochentag bekommt eine mittlere Füllfarbe und
  • das Feld Feiertag/KW bekommt ebenfalls eine mittlere Füllfarbe.
  • Das Feld Feiertag/KW bekommt eine mittlere Schriftfarbe, sofern es ein Montag ist, der kein Feiertag ist.
Wochenende: WOCHENTAG(Datum;2)>5

Ein Datum fällt auf ein Wochenende, wenn der Wochentag größer als 5 ist, nämlich Samstag oder Sonntag 

NICHT( ISTFEHLER( FINDEN(Datum;Feiertagsliste) ) )

Ein Feiertag ist, wenn beim Suchen in der Feiertagsliste kein Fehler auftritt. Datum ist ein Feiertag, dann liefern die Funktion FINDEN keinen Fehler, damit liefert die Funktion ISTFEHLER den Wert FALSCH, die Funktion NICHT kehrt den Wert zu WAHR um. Damit hier die Fehler der Nichtfeiertage abgefangen werden können, wird diese Konstruktion benötigt.

UND( ISTFEHLER( FINDEN(Datum;Feiertagsliste));
WOCHENTAG(A3;2)=1)

Die Kalenderwoche wird nur eingetragen, wenn der Montag kein Feiertag ist. Also nur wenn Wochentag=1 (Montag) UND beim Suchen in der Feiertagsliste ein Fehler auftritt, das Datum also kein Feiertag ist.

Mit den folgenden zwei Formeln lösen Sie die Bedingte Formatierung für die oben aufgestellten Regeln. ACHTUNG! Bezugszelle ist hier A3.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)
=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)

Markieren Sie bitte die Zellen A3:A9, beginnen Sie unbedingt mit Zelle A3, da Sie als Bezugszelle für die Formatierung der anderen Zellen benutzt wird. Würden Sie von unten her markieren, dann würde oben im Namenfeld nicht A3 sondern A9 stehen und die Formel müsste sich anstelle von A3 auf A9 beziehen. Also machen Sie es bitte wie vorgeschlagen.

Klicken Sie dann im Menüband “Start”, Gruppe “Formatvorlagen” auf “Bedingte Formatierung” und wählen “Neue Regel ..”.

“Bedingte Formatierung” - “Neue Regel ..”

Es öffnet sich das Dialogfenster “Neue Formatierungsregel”, wählen Sie hier “Formel zur Ermittlung der zu formatierenden Zellen verwenden” und geben dann im Feld “Werte formatieren, für die diese Formel wahr ist:” (rot) die Formel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anführungszeichen ein. Klicken Sie hiernach auf den Knopf Formatieren (gelb).

Im Dialogfenster Zellen formatieren wählen Sie für die Schrift ein Design-Weiß (grün) und für Ausfüllen ein mittleres Design-Blau (blau) aus. Nachdem Sie alle Dialoge mit OK geschlossen haben könnte Ihr Kalender wie abgebildet aussehen.

Formelbasierte bedingte Formatierung für die Tage. So könnte Ihre formelbasierte bedingte Formatierung aussehen.

Nun formatieren Sie die Wochentage, diese sollen nur mit einem Füllmuster versehen werden, die Schriftfarbe bleibt unverändert. Markieren Sie bitte die Zellen B3:B9, beginnen Sie unbedingt mit Zelle B3!

Klicken Sie dann im Menüband “Start”, Gruppe “Formatvorlagen” auf “Bedingte Formatierung” und wählen “Neue Regel ..”.

Wählen Sie “Formel zur Ermittlung der zu formatierenden Zellen verwenden” und geben dann im Feld “Werte formatieren, für die diese Formel wahr ist:” (rot) die Formel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anführungszeichen ein. Als Füllmuster wählen Sie das gleiche mittlere Design-Blau wie bei den Tagen. Wenn alles funktioniert hat, dann sieht Ihr Kalender nun so aus.

Formelbasierte bedingte Formatierung für die Wochentage. So könnte Ihre formelbasierte bedingte Formatierung aussehen.

Nun Formatieren Sie die Feiertage/KW. Die Formatierungsregel für die Feiertage kennen Sie schon, es ist die schon für die Tage und Wochentage verwendete. Markieren Sie die Zellen C3:C9. Erzeugen Sie eine neue formelbasierte bedingte Formatierung und verwenden Sie die folgende Formel und wählen ein mittleres Design-Blau als Füllmuster:

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)

Jetzt müssen Sie die Formatierung für die Kalenderwoche festlegen, hier kein Füllmuster aber Schriftfarbe in mittleren Design-Blau.

Nachdem Sie die erste Formatierungsregel definiert haben, sind noch immer die Zellen C3:C9 markiert. Klicken Sie auf “Bedingte Formatierung” und wählen Sie nun “Regeln verwalten ..”. Im Dialogfenster “Manager für Regeln zur bedingten Formatierung” wählen Sie bitte “Neue Regel ..”.

Erste Regel für formelbasierte bedingte Formatierung wurde hinzugefügt. Neue Regel für die Formatierung der KW hinzufügen.

Der weitere Ablauf ist analog zu den schon durchgeführten bedingten Formatierungen. Wählen Sie eine formelbasierte bedingte Formatierung, wählen Sie als Schriftfarbe ein mittleres Design-Blau. Geben Sie die folgende Formel ein:

=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)

Nach dem Bestätigen (mehrere Male), sollten Sie die hellblaue 1. KW sehen können.

 Formelbasierte bedingte Formatierung für die Kalenderwoche. So sieht die Kalenderwoche aus.

Nun haben Sie es fast geschafft, Sie müssen nur noch die bedingten Formatierungen für Tage, Wochentage und Feiertag/KW vom Januar auf den Februar übertragen. Das Erledigen Sie kinderleicht mit Format-Übertragen (-Pinsel).

Markieren Sie hierzu die Zellen A3:A9, also vom 01. bis 07. Januar, und klicken dann auf im Menüband “Start”, Gruppe “Zwischenablage” auf  “Format-Übertragen”. Danach markieren Sie den Bereich D3:D9 in einem Zug und die Formatierung wird übertragen. Das wiederholen Sie bitte für die Wochentage und die Feiertage/KW. Wenn alles funktioniert hat, dann sollte Ihr Kalender wie im folgenden Bild aussehen.

Format-Übertragen (-Pinsel) Teil 3 - Fertsch!

Das war der 3. Teil des Workshops. Im 4. Teil werden Sie die restlichen Monate anlegen und die Zeilen nach unten ausfüllen. Dann kommt noch das Ermitteln der Arbeitstage und das Einstellen des Druckbereiches und Sie sind fertig mit Ihrem Kalender.

Viel Erfolg und Spaß beim Nachmachen.

Down­loads

  1. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2010,
  2. Kalen­der in einer mög­li­chen finalen Version für Excel 2003,
  3. Kalender Teil 1 für Excel 2010,
  4. Kalender Teil 2 für Excel 2010 und
  5. Kalender Teil 3 für Excel 2010.

Kommentare sind geschlossen.