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-Fea­tures der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 3. Teil des Work­shops meis­tern Sie die “Beding­te For­ma­tie­rung”.

Sie könn­ten die bei­den ers­ten Tei­le der Work­shops “Work­shop: Kalen­der mit Fei­er­ta­gen und Kalen­der­wo­che selbst erstel­len Teil 1 — Grund­la­gen” und “Work­shop: Kalen­der mit Fei­er­ta­gen und Kalen­der­wo­che selbst erstel­len Teil 2 – Pro­gram­mie­rung” nach­ar­bei­ten und Ihre eige­ne Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der bei­den Datei­en her­un­ter und star­ten mit dem Work­shop Ihrer Wahl:

Die Datei­en des Work­shop Teil 3 fin­den Sie wie immer am Ende die­ses Blog Post. Kli­cken Sie auf ein Bild für eine ver­grö­ßer­te Dar­stel­lung.

Was noch fehlte

Im Teil 2 die­ser Work­shop Rei­he fehl­te noch das For­ma­tie­ren der Zel­len in denen ein Fei­er­tag oder mög­li­cher­wei­se die Kalen­der­wo­che steht. die Berech­nung hat­ten Sie schon pro­gram­miert. Mar­kie­ren Sie bit­te die Zel­len A4:F4 und fül­len Sie sie bit­te bis zur Zei­le 9 nach unten aus. Wenn Sie alles rich­tig gemacht haben, dann soll­ten Sie beim 1. Janu­ar links­bün­dig “Neu­jahr” ste­hen haben und beim 3. Janu­ar und 7. Febru­ar eine 1 bzw. 6, das sind die Kalen­der­wo­chen. Wir möch­ten im Feld aber 1. KW bzw. 6. KW ste­hen haben, also bemü­hen Sie die benut­zer­de­fi­nier­te Zell­for­ma­tie­rung. Mar­kie­ren Sie bit­te die Zel­len C3:C9 und F3:F9 (<STRG> Tas­te), kli­cken mit der rech­ten Maus und wäh­len “Zel­len for­ma­tie­ren”. Geben Sie als Typ [0”. KW”] ohne die ecki­gen Klam­mern ein. Also Null Anfüh­rungs­zei­chen, Punkt, Leer­zei­chen, KW und dann Anfüh­rungs­zei­chen.

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 Zel­len eine Zah­len­for­ma­tie­rung defi­niert, die­se gilt aber nur für die Zah­len, hier die Kalen­der­wo­chen. Wenn Sie ein­mal die Zel­len mar­kiert haben, ord­nen Sie den Inhalt bit­te links­bün­dig an.

Nur Zellen mit Kalenderwochen sind von der Formatierung betroffen

Die Bedingte Formatierung

Fol­gen­de For­ma­tie­run­gen für Fei­er­ta­ge, Wochen­en­den und Mon­ta­ge wol­len wir vor­neh­men:

  • der Tag soll eine mitt­le­re Füll­far­be und wei­ße Schrift bekom­men,
  • der Wochen­tag bekommt eine mitt­le­re Füll­far­be und
  • das Feld Feiertag/KW bekommt eben­falls eine mitt­le­re Füll­far­be.
  • Das Feld Feiertag/KW bekommt eine mitt­le­re Schrift­far­be, sofern es ein Mon­tag ist, der kein Fei­er­tag ist.
Wochenende: WOCHENTAG(Datum;2)>5

Ein Datum fällt auf ein Wochen­en­de, wenn der Wochen­tag grö­ßer als 5 ist, näm­lich Sams­tag oder Sonn­tag 

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

Ein Fei­er­tag ist, wenn beim Suchen in der Fei­er­tags­lis­te kein Feh­ler auf­tritt. Datum ist ein Fei­er­tag, dann lie­fern die Funk­ti­on FIN­DEN kei­nen Feh­ler, damit lie­fert die Funk­ti­on IST­FEH­LER den Wert FALSCH, die Funk­ti­on NICHT kehrt den Wert zu WAHR um. Damit hier die Feh­ler der Nicht­fei­er­ta­ge abge­fan­gen wer­den kön­nen, wird die­se Kon­struk­ti­on benö­tigt.

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

Die Kalen­der­wo­che wird nur ein­ge­tra­gen, wenn der Mon­tag kein Fei­er­tag ist. Also nur wenn Wochentag=1 (Mon­tag) UND beim Suchen in der Fei­er­tags­lis­te ein Feh­ler auf­tritt, das Datum also kein Fei­er­tag ist.

Mit den fol­gen­den zwei For­meln lösen Sie die Beding­te For­ma­tie­rung für die oben auf­ge­stell­ten Regeln. ACH­TUNG! Bezugs­zel­le ist hier A3.

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

Mar­kie­ren Sie bit­te die Zel­len A3:A9, begin­nen Sie unbe­dingt mit Zel­le A3, da Sie als Bezugs­zel­le für die For­ma­tie­rung der ande­ren Zel­len benutzt wird. Wür­den Sie von unten her mar­kie­ren, dann wür­de oben im Namen­feld nicht A3 son­dern A9 ste­hen und die For­mel müss­te sich anstel­le von A3 auf A9 bezie­hen. Also machen Sie es bit­te wie vor­ge­schla­gen.

Kli­cken Sie dann im Men­üband “Start”, Grup­pe “For­mat­vor­la­gen” auf “Beding­te For­ma­tie­rung” und wäh­len “Neue Regel ..”.

“Bedingte Formatierung” - “Neue Regel ..”

Es öff­net sich das Dia­log­fens­ter “Neue For­ma­tie­rungs­re­gel”, wäh­len Sie hier “For­mel zur Ermitt­lung der zu for­ma­tie­ren­den Zel­len ver­wen­den” und geben dann im Feld “Wer­te for­ma­tie­ren, für die die­se For­mel wahr ist:” (rot) die For­mel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anfüh­rungs­zei­chen ein. Kli­cken Sie hier­nach auf den Knopf For­ma­tie­ren (gelb).

Im Dia­log­fens­ter Zel­len for­ma­tie­ren wäh­len Sie für die Schrift ein Design-Weiß (grün) und für Aus­fül­len ein mitt­le­res Design-Blau (blau) aus. Nach­dem Sie alle Dia­lo­ge mit OK geschlos­sen haben könn­te Ihr Kalen­der wie abge­bil­det aus­se­hen.

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

Nun for­ma­tie­ren Sie die Wochen­ta­ge, die­se sol­len nur mit einem Füll­mus­ter ver­se­hen wer­den, die Schrift­far­be bleibt unver­än­dert. Mar­kie­ren Sie bit­te die Zel­len B3:B9, begin­nen Sie unbe­dingt mit Zel­le B3!

Kli­cken Sie dann im Men­üband “Start”, Grup­pe “For­mat­vor­la­gen” auf “Beding­te For­ma­tie­rung” und wäh­len “Neue Regel ..”.

Wäh­len Sie “For­mel zur Ermitt­lung der zu for­ma­tie­ren­den Zel­len ver­wen­den” und geben dann im Feld “Wer­te for­ma­tie­ren, für die die­se For­mel wahr ist:” (rot) die For­mel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anfüh­rungs­zei­chen ein. Als Füll­mus­ter wäh­len Sie das glei­che mitt­le­re Design-Blau wie bei den Tagen. Wenn alles funk­tio­niert hat, dann sieht Ihr Kalen­der nun so aus.

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

Nun For­ma­tie­ren Sie die Feiertage/KW. Die For­ma­tie­rungs­re­gel für die Fei­er­ta­ge ken­nen Sie schon, es ist die schon für die Tage und Wochen­ta­ge ver­wen­de­te. Mar­kie­ren Sie die Zel­len C3:C9. Erzeu­gen Sie eine neue for­mel­ba­sier­te beding­te For­ma­tie­rung und ver­wen­den Sie die fol­gen­de For­mel und wäh­len ein mitt­le­res Design-Blau als Füll­mus­ter:

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

Jetzt müs­sen Sie die For­ma­tie­rung für die Kalen­der­wo­che fest­le­gen, hier kein Füll­mus­ter aber Schrift­far­be in mitt­le­ren Design-Blau.

Nach­dem Sie die ers­te For­ma­tie­rungs­re­gel defi­niert haben, sind noch immer die Zel­len C3:C9 mar­kiert. Kli­cken Sie auf “Beding­te For­ma­tie­rung” und wäh­len Sie nun “Regeln ver­wal­ten ..”. Im Dia­log­fens­ter “Mana­ger für Regeln zur beding­ten For­ma­tie­rung” wäh­len Sie bit­te “Neue Regel ..”.

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

Der wei­te­re Ablauf ist ana­log zu den schon durch­ge­führ­ten beding­ten For­ma­tie­run­gen. Wäh­len Sie eine for­mel­ba­sier­te beding­te For­ma­tie­rung, wäh­len Sie als Schrift­far­be ein mitt­le­res Design-Blau. Geben Sie die fol­gen­de For­mel ein:

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

Nach dem Bestä­ti­gen (meh­re­re Male), soll­ten Sie die hell­blaue 1. KW sehen kön­nen.

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

Nun haben Sie es fast geschafft, Sie müs­sen nur noch die beding­ten For­ma­tie­run­gen für Tage, Wochen­ta­ge und Feiertag/KW vom Janu­ar auf den Febru­ar über­tra­gen. Das Erle­di­gen Sie kin­der­leicht mit For­mat-Über­tra­gen (-Pin­sel).

Mar­kie­ren Sie hier­zu die Zel­len A3:A9, also vom 01. bis 07. Janu­ar, und kli­cken dann auf im Men­üband “Start”, Grup­pe “Zwi­schen­ab­la­ge” auf  “For­mat-Über­tra­gen”. Danach mar­kie­ren Sie den Bereich D3:D9 in einem Zug und die For­ma­tie­rung wird über­tra­gen. Das wie­der­ho­len Sie bit­te für die Wochen­ta­ge und die Feiertage/KW. Wenn alles funk­tio­niert hat, dann soll­te Ihr Kalen­der wie im fol­gen­den Bild aus­se­hen.

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

Das war der 3. Teil des Work­shops. Im 4. Teil wer­den Sie die rest­li­chen Mona­te anle­gen und die Zei­len nach unten aus­fül­len. Dann kommt noch das Ermit­teln der Arbeits­ta­ge und das Ein­stel­len des Druck­be­rei­ches und Sie sind fer­tig mit Ihrem Kalen­der.

Viel Erfolg und Spaß beim Nach­ma­chen.

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 fina­len Ver­si­on für Excel 2003,
  3. Kalen­der Teil 1 für Excel 2010,
  4. Kalen­der Teil 2 für Excel 2010 und
  5. Kalen­der Teil 3 für Excel 2010.

Kommentare sind geschlossen.