Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 5 – Finale

kalender5Ziel 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 5. Teil des Work­shops ler­nen Sie wie Sie Tabel­len­blät­ter ver­ste­cken und den Kalen­der für Excel 97–2003 auf­be­rei­ten.

Sie könn­ten die ers­ten vier Tei­le der Work­shop-Rei­he:

nach­ar­bei­ten und Ihre eige­ne Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der Datei­en her­un­ter und star­ten mit dem Work­shop Ihrer Wahl:

Die Datei­en zum Work­shop fin­den Sie wie immer am Ende die­ses Blog Post. Für die ver­grö­ßer­te Dar­stel­lung eines Bil­des kli­cken Sie bit­te auf das Bild.

Tabelle Berechnung verstecken

Ich ver­se­he Excel Arbeits­map­pen, die ich für ein Pro­blem erar­bei­tet habe, häu­fig mit einer Kom­men­tar­sei­te, für den Fall, dass ich die Tabel­le ggf. noch­mals anpas­sen muss. Meist wer­den die Men­schen, für die ich die Excel Tabel­le erar­bei­tet habe, durch die Kom­men­ta­re und Berech­nun­gen nur ver­wirrt. Also muss ich die­se ver­ste­cken.

Öff­nen Sie Ihr Excel Tabel­le und rufen Sie mit <ALT><F11> den Visu­al Basic Edi­tor auf, funk­tio­niert mit jeder Excel Ver­si­on. Sicher kön­nen Sie mit die­sem Edi­tor noch mehr anstel­len, wir wol­len aber nur eine Tabel­le ver­ber­gen.

Soll­te Ihr Visu­al Basic Edi­tor nur ein lee­res grau­es Fens­ter zei­gen und nicht wie abge­bil­det aus­se­hen, so akti­vie­ren Sie mit Menü – Ansicht – Pro­jekt-Explo­rer oder >STRG>R das Fens­ter “Pro­jekt – VBA­Pro­jekt”. Das Eigen­schaf­ten Fens­ter rufen Sie mit <F4> oder Menü – Ansicht – Eigen­schaf­ten­fens­ter auf.

Wäh­len Sie im Pro­jekt­fens­ter Ihre Tabel­le 2 (Berech­nung) aus, ändern Sie dann im Eigen­schaf­ten Fens­ter die Eigen­schaft “Visi­ble” (Sicht­bar­keit) von “-1 –xlS­he­et­Vi­si­ble” auf “0 – xlS­he­etHid­den”. Das war’s schon. Ach­ten Sie dar­auf, dass Sie nicht alle Blät­ter einer Arbeits­map­pe ver­ste­cken kön­nen.

Schlie­ßen Sie den VB Edi­tor durch Klick auf das rote Kreuz in der rech­ten obe­ren Ecke, Sie müs­sen nichts spei­chern, das erfolgt auto­ma­tisch. Die Tabel­le “Berech­nung” soll­te ver­schwun­den sein.

Visual Basic Editor mit Projekt- und Eigenschaftenfenster Tabelle "Berechnung" ist verschwunden

Kalender für Excel 97 bis 2003 abspeichern

Nun spei­chern Sie die Kalen­der Datei kom­pa­ti­bel für Excel 97 bis 2003, dazu kli­cken Sie auf Datei und “Spei­chern unter”. Wäh­len Sie “Excel-97–2003-Arbeitsmappe” und geben Sie als Datei­na­men bspw. “kalender2003.xls” ein. Kli­cken Sie dann auf “Spei­chern”.

Lei­der fin­det die Kom­pa­ti­bi­li­täts­prü­fung nicht alle Feh­ler. Gefun­den wer­den:

  1. Beding­te For­ma­tie­rung für den Kalen­der, hier wird erheb­li­cher Funk­ti­ons­ver­lust pro­gnos­ti­ziert, der so jedoch nicht ein­tre­ten wird.
  2. For­ma­tie­run­gen ein­zel­ner Zel­len sol­len einen gering­fü­gi­gen Genau­ig­keits­ver­lust haben, ver­mut­lich meint Excel hier die Umset­zung der Design­far­ben, die es in den alten Ver­sio­nen so nicht gibt.

Nicht gefun­den wird die Funk­ti­on NET­TO­AR­BEITS­TA­GE, die­se Funk­ti­on gibt es nicht in Excel 97–2003. Sie erset­zen die Funk­ti­on mit einer VBA-Funk­ti­on, die das Laden eines Add-Ins erfor­dert.

Wäh­len Sie im Fens­ter für die Kom­pa­ti­bi­li­täts­prü­fung “Kor­ri­gie­ren”.

Datei kompatibel für Excel 97 bis 2003 abspeichern Wählen Sie im Fenster für die Kompatibilitätsprüfung “Korrigieren”.

Öff­nen Sie die kon­ver­tier­te Excel Datei in der älte­ren Excel Ver­si­on, hier vor­ge­führt mit Excel 2003.

Solan­ge Sie eine der Zel­len mit der inkom­pa­ti­blen For­mel für die NET­TO­AR­BEITS­TA­GE nicht anfas­sen, sieht alles per­fekt aus. Sobald Sie eine For­mel ankli­cken und mit <ENTER> bestä­ti­gen wer­den Feh­ler ange­zeigt. Das älte­re Excel kennt die Funk­ti­on NET­TO­AR­BEITS­TA­GE nicht.

Das ältere Excel kennt die Funktion NETTOARBEITSTAGE nicht.

Akti­vie­ren Sie über Menü “Extras” – “Add-Ins…” die “Ana­ly­se-Funk­tio­nen-VBA”. Jetzt erset­zen Sie die Funk­ti­on “NET­TO­AR­BEITS­TA­GE ” durch “Net­Work­Days” und bestä­ti­gen die Ein­ga­be mit der <ENTER>-Taste. In Zel­le A34 soll­te fol­gen­de For­mel ste­hen:

=NetWorkDays(MIN(A3:A33);MAX(A3:A33);Feiertage)

Aktivieren Sie über Menü “Extras” – “Add-Ins…” die “Analyse-Funktionen-VBA”.  Funktion “NETTOARBEITSTAGE ” durch “NetWorkDays” ersetzen

Mar­kie­ren Sie Zei­le 34 und wäh­len Sie Menü “Bear­bei­ten” – “Erset­zen”. Geben Sie für “Suche nach” NET­TO­AR­BEITS­TA­GE und für “Erset­zen durch” Net­Work­Days ein. Nach Klick auf “Alle erset­zen” erscheint ein Fens­ter, dass 11 Erset­zun­gen bestä­tigt. Jetzt berech­net die älte­re Excel Ver­si­on auch die Arbeits­ta­ge.

Alle  “NETTOARBEITSTAGE ” durch “NetWorkDays” ersetzen  Jetzt berechnet die ältere Excel Version auch die Arbeitstage.

Wir sind fer­tig. Der Kalen­der funk­tio­niert auch mit der älte­ren Excel Ver­si­on. Aber Ihnen gefällt, wie mir übri­gens auch, die “kon­ver­tier­te” Design­far­be abso­lut nicht. Das Cyan ver­ur­sacht doch Augen­krebs.

Konvertierte Design-Farbe gefällt nicht – was tun?

Ich neh­me für die schnel­le Lösung bewusst in Kauf, dass die For­meln noch­mals ein­ge­ge­ben wer­den müs­sen. Sie kön­nen die beding­te For­ma­tie­rung für Tage, Wochen­ta­ge und Feiertage/KW auch für jeden Monat ein­zeln vor­neh­mem, das ver­zwölf­facht jedoch den Arbeits­auf­wand.

Wir geben Voll­gas und ändern:

  1. die beding­te For­ma­tie­rung für die Tage,
  2. die beding­te For­ma­tie­rung für die Wochen­ta­ge,
  3. die beding­te For­ma­tie­rung für die Feiertage/KW,
  4. die Tabel­len­li­ni­en waa­ge­recht und senk­recht und
  5. die Far­ben für das Kalen­der­jahr, die Mona­te und die Net­to­ar­beits­ta­ge.
1. Bedingte Formatierung der Tage im gesamten Kalender

Dazu geben Sie im Namen­feld fol­gen­de Berei­che ein und bestä­ti­gen die Ein­ga­be mit <ENTER>, danach hal­ten Sie die <STRG>-Taste gedrückt und kli­cken auf Zel­le A3!

A3:A33;D3:D31;G3:G33;J3:J32;M3:M33;P3:P32;S3:S33;V3:V33;Y3:Y32;AB3:AB33;AE3:AE32;AH3:AH33

Die For­mel für die beding­te For­ma­tie­rung lau­tet wie folgt, wäh­len Sie eine For­ma­tie­rung.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)
Bedingte Formatierung der Tage im gesamten Kalender ersetzen
2. Bedingte Formatierung der Wochentage im gesamten Kalender

Fol­gen­de Berei­che aus­wäh­len. Dann bei gedrück­ter <STRG>-Taste auf die Zel­le B3 kli­cken.

B3:B33;E3:E31;H3:H33;K3:K32;N3:N33;Q3:Q32;T3:T33;W3:W33;Z3:Z32;AC3:AC33;AF3:AF32;AI3:AI33

Die For­mel für die beding­te For­ma­tie­rung lau­tet wie folgt, wäh­len Sie eine For­ma­tie­rung wie bei den Tagen.

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

Bedingte Formatierung der Wochentage im gesamten Kalender ersetzen

3. Bedingte Formatierung der Feiertage/KW im gesamten Kalender

Den Bereich wie folgt wäh­len und dann bei bei gedrück­ter <STRG>-Taste auf die Zel­le C3 kli­cken.

C3:C33;F3:F31;I3:I33;L3:L32;O3:O33;R3:R32;U3:U33;X3:X33;AA3:AA32;AD3:AD33;AG3:AG32;AJ3:AJ33

Für die beding­te For­ma­tie­rung sind zwei For­meln ein­zu­ge­ben.

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

Die ers­te For­mel gilt für die Kalen­der­wo­che, ändern Sie hier nur die Schrift­far­be. For­mel 2 gilt für die Fei­er­ta­ge, wäh­len Sie eine For­ma­tie­rung. Hal­ten Sie die Rei­hen­fol­ge der ein­zel­nen For­meln für die beding­te For­ma­tie­rung ein. 

Bedingte Formatierung der Feiertage/KW im gesamten Kalender ersetzen

4. Tabellenlinien waagerecht und senkrecht

Nun müs­sen Sie die hori­zon­ta­len Lini­en for­ma­tie­ren, wäh­len Sie dazu fol­gen­den Bereich.

A2:AH34

Wäh­len Sie im Menü “For­mat” – “Zel­len” und dann “Rah­men”. ACH­TUNG! For­ma­tie­ren Sie die mitt­le­re senk­rech­te Linie nicht. For­ma­tie­ren Sie nur die äuße­ren senk­rech­ten Lini­en und alle waa­ge­rech­ten Lini­en.

Tabellenlinien waagerecht formatieren

Für das For­ma­tie­ren der senk­rech­ten Lini­en dür­fen wir nur jeden zwei­ten Monat mar­kie­ren. Wäh­len Sie fol­gen­den Bereich. For­ma­tie­ren Sie nur die äuße­re lin­ke und äußers­te rech­te senk­rech­te Linie.

D2:F34;J2:L34;P2:R34;V2:X34;AB2:AD34;AH2:AJ34

Tabellenlinien senkrecht formatieren

Jetzt müs­sen Sie nur noch die Zel­len mit dem Jahr, die Mona­te und die Arbeits­ta­ge mar­kie­ren. Wäh­len Sie eine For­ma­tie­rung für die Schrift­far­be.

 Jahr, Monate und Arbeitstage formatieren

Das war’s. Der Kalen­der für Excel 97 bis 2003 sieht nun auch ange­nehm aus.

Der Kalender für Excel 97 bis 2003 sieht nun auch angenehm aus.

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

Down­loads

  1. Kalen­der Teil 1 für Excel 2010,
  2. Kalen­der Teil 2 für Excel 2010,
  3. Kalen­der Teil 3 für Excel 2010,
  4. Kalen­der Teil 4 für Excel 2010,
  5. fina­ler Kalen­der für Excel 2010,
  6. kon­ver­tier­ter Kalen­der für Excel 97–2003 und
  7. fina­ler Kalen­der für Excel 97–2003.

Kommentare sind geschlossen.