Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 4 – Aufhübschen

kalenderZiel 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 4. Teil des Work­shops legen Sie die rest­li­chen Mona­te und Tage an, ermit­teln die Arbeits­ta­ge und stel­len den Druck­be­reich ein.

Sie könn­ten die ers­ten Tei­le der Work­shops:

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 auf das Bild.

Überschriften und Monate mit Schriftfarbe versehen

Mar­kie­ren Sie die Zel­len A1, B2 und D2, also die Zel­len mit dem Jahr und den Mona­ten Janu­ar und Febru­ar. Wäh­len Sie dann eine Design­far­be für die Schrift aus, hier habe ich wie­der das mitt­le­re Design-Blau gewählt.

A1, B2 und D2 mit Schriftfarbe mittleres Design-Blau formatieren

Mar­kie­ren Sie das Feld D2, das mit dem Febru­ar, und gehen Sie auf die rech­te unte­re Ecke, kli­cken Sie mit der lin­ken Maus dar­auf und zie­hen die Maus nach rechts, so fül­len Sie die Mona­te bis Dezem­ber aus. Wahr­schein­lich wer­den Sie am Bild­rand abset­zen, den Fens­ter­in­halt nach rechts scrol­len und dann wei­ter aus­fül­len. Enden Sie mit dem Dezem­ber, es soll­te die Zel­le AJ2 sein.

Zellen nach rechts ausfüllen - mit Absetzen Zellen nach rechts ausfüllen - bis Dezember

Mar­kie­ren Sie im Monat Febru­ar die Tage vom 1. bis zum 7. Febru­ar (D3:D9). Hal­ten Sie die <STRG>-Taste gedrückt und mar­kie­ren Sie die Tage für die Mona­te März bis Dezem­ber. soll­ten Sie abrut­schen, dann müs­sen Sei weder von vorn begin­nen. Kli­cken Sie dann auf dem Men­üband “Start” die Grup­pe “Bear­bei­ten” und dann “Füll­be­reich” und “Rechts”. Alter­na­tiv hät­ten Sie auch <STRG>R benut­zen kön­nen. Ver­fah­ren Sie für die Wochen­ta­ge und die Feiertage/KW ana­log. Am Ende soll­te die ers­te Woche von Janu­ar bis Dezem­ber ein­ge­tra­gen sein.

Spalten für Tage von Januar bis Dezember nach rechts ausfüllen Spalten für Tage, Wochentage und Feiertage/KW von Januar bis Dezember nach rechts ausgefüllt

Mar­kie­ren Sie nun in Zei­le 9 den Bereich A9:AJ9, Sie könn­ten auch im Namen­feld “A9:AJ9” ohne Anfüh­rungs­zei­chen ein­ge­ben und mit der <ENTER>-Taste bestä­ti­gen. Kli­cken Sie auf die rech­te unte­re Ecke, Maus­zei­ger muss zuerst zu dün­nem schwar­zen Plus wer­den, und zie­hen Sie die Maus bis zur Zei­le 33. Sie müs­sen ggf. abset­zen. Wenn Sie alles kor­rekt aus­ge­füllt haben, dann erscheint in der rech­ten unte­ren Zel­le “Sil­ves­ter”.

Kalender nach unten ausfüllen

Sie wis­sen, dass eini­ge Mona­te 28/29, man­che 30 und ande­re 31 Tage haben. Das müs­sen Sie nun kor­ri­gie­ren. Mar­kie­ren Sie dazu die Fel­der, bei denen es mit dem 1. des Nach­fol­ge­mo­nats wei­ter geht. Das müss­ten der 2. und 3. März, der 1. Mai, der 1. Juli, der 1. Okto­ber und der 1. Dezem­ber sein.

Nach dem Mar­kie­ren drü­cken Sie bit­te die <ENTF>-Taste, die For­meln in den Zel­len wer­den gelöscht. Nun müs­sen Sie noch die beding­te For­ma­tie­rung ent­fer­nen. Wäh­len Sie auf dem Men­üband “Start” in der Grup­pe “For­mat­vor­la­gen” die “Beding­te For­ma­tie­rung”, dann “Regeln löschen” und “Regeln in aus­ge­wähl­ten Zel­len löschen”.

Markeiren Sie am unteren Rand des Kalenders den 2. und 3. März, den 1. Mai, den 1. Juli, den 1. Oktober und den 1. Dezember Löschen Sie die Regeln der bedingten Formatierung in den ausgewählten Bereichen

Mar­kie­ren Sie alle Tage-Spal­ten und alle Wochen­tag-Spal­ten, sie wer­den auf eine Brei­te von 7 Excel-Ein­hei­ten ein­ge­stellt. Kli­cken Sie mit der rech­tem Maus auf den Spal­ten­kopf und wäh­len Sie “Spal­ten­brei­te…”. Geben Sie hier den Wert 7 ein. Ver­fah­ren Sie ana­log für die Fei­er­ta­g/KW-Spal­ten und stel­len Sie die Brei­te auf 25 ein.

Tage-Spalten und Wochentag-Spalten 7 Excel-Einheiten breit Feiertag/KW-Spalten sind 25 Excel-Einheiten breit

Nun müs­sen Sie sich um den 29. Febru­ar küm­mern. Dazu ver­wen­den wir einen Trick, denn Excel kennt Schalt­jah­re und wen­det Sie auch an. Wenn Sie in einem Schalt­jahr zum 28. Febru­ar einen Tag hin­zu­ad­die­ren, dann erhal­ten Sie den 29. Febru­ar. Voll­zie­hen Sie das in einem Nicht­schalt­jahr, dann erhal­ten Sie den 1. März. Wir müs­sen also den Monat che­cken.

Wir bet­ten die in den Zel­len D31 und F31 ste­hen­den For­meln ein­fach in eine Wenn-Anwei­sung ein.

=Wenn(Monat(D30+1)=2;     ursprüngliche Formel     ;””)

Um die Zel­le E31 brau­chen Sie sich nicht küm­mern, die ist schon geschickt pro­gram­miert. Zwinkerndes Smiley

29. Februar nur in Schaltjahren anzeigen Kalenderwoche am 29. Februar nur in Schaltjahren anzeigen

Nun for­ma­tie­ren Sie die Zei­le in der die Anzahl der Arbeits­ta­ge ste­hen sol­len. Mar­kie­ren Sie dazu Zei­le 2 (hier ste­hen die Mona­te) und Zei­le 34 (hier sol­len die Arbeits­ta­ge ste­hen). Fül­len Sie die Inhal­te nach unten aus, bspw. <STRG>U oder über das Men­üband.

Jetzt ermit­teln Sie die Arbeits­ta­ge, dazu gibt es die Funk­ti­on NET­TO­AR­BEITS­TA­GE. Kli­cken Sie bit­te in Zel­le A34, dort steht z.Z. Janu­ar und geben fol­gen­de For­mel ein:

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

Das Start­da­tum ist immer das kleins­te Datum in der Spal­te (MIN), das End­da­tum dann das größ­te Datum (MAX). Um ggf. Fei­er­ta­ge abzu­zie­hen geben Sie den Bereichs­na­men “Fei­er­ta­ge” ein. Zäh­len Sie bspw. die Arbeits­ta­ge im Dezem­ber, es funk­tio­niert. Benut­zen Sie die benut­zer­de­fi­nier­te For­ma­tie­rung für die Zel­le A34 und geben Sie [0 “Arbeits­ta­ge] ohne ecki­ge Klam­mern ein.

Formatierung für die Zeile Arbeitstage nach unten ausfüllen Arbeitstage benutzerdefiniert formatiert - [0 "Arbeitstage"] ohne eckige Klammern

Fül­len Sie den Bereich A34:AJ34 nach rechts aus, benut­zen Sie den Befehl aus dem Men­üband oder <STRG>R. Nun soll­ten alle Arbeits­ta­ge von Janu­ar bis Dezem­ber berech­net wer­den.

Mar­kie­ren Sie den Bereich A1:AJ34 und wäh­len Sie auf dem Men­üband “Sei­ten­lay­out” die Grup­pe “Sei­te ein­rich­ten”, dann “Druck­be­reich” und “Druck­be­reich fest­le­gen”.

Jetzt müs­sen Sie Excel so ein­stel­len, dass es alles auf eine Sei­te druckt. Dazu Kli­cken Sie auf dem Men­üband “Sei­ten­lay­out” inner­halb der Grup­pe “Sei­te ein­rich­ten” auf das klei­ne Käst­chen in der rech­ten unte­ren Ecke, wäh­len dann als Aus­rich­tung das Quer­for­mat und Kli­cken im Bereich Ska­lie­rung auf Anpas­sen und wäh­len 1 Sei­te breit und 1 Sei­te hoch.

Menüband “Seitenlayout” innerhalb der Gruppe “Seite einrichten”, dann “Druckbereich” und “Druckbereich festlegen” Seite einrichten

Die Druck­vor­schau über Datei – Dru­cken zeigt wie der Kalen­der im Aus­druck aus­schaut.

Druckvorschau

Das war es mit Teil 4 des Work­shops. Im 5. Teil wer­den Sie das Tabel­len­blatt “Berech­nung” ver­ber­gen und die Datei in einem For­mat für älte­re Excel Ver­sio­nen abspei­chern. Dazu müs­sen Sie die Funk­ti­on NET­TO­AR­BEITS­TA­GE erset­zen, denn die­se gab es damals noch nicht.

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

Down­loads

7 Kommentare » Schreibe einen Kommentar

  1. Hal­lo Herr Kütt­ner,
    die Anlei­tung zum Erstel­len des Kalen­ders ist wirk­lich klas­se. Bis Teil 4 des Work­shops habe ich mich durch­ge­ar­bei­tet. Bis auf das nament­li­che Anzei­gen der Fei­er­ta­ge (Neu­jahr; Kar­frei­tag usw.) hat alles wirk­lich gut geklappt. Ich habe die For­meln und die mar­kier­ten Berei­che bei der Ver­ga­be der Namen mehr­fach neu eige­ge­ben, aber es funk­tio­niert ein­fach nicht. In den ein­zel­nen Zel­len des fer­ti­gen Kalen­ders wird jeweils die Excel-Sys­tem­zahl ange­zeigt. Für Neu­jahr 40544. KW, für Kar­frei­tag 40655. KW. Wo liegt der Feh­ler?
    Ange­la

  2. Lie­be Ange­la,
    wenn es Ihnen nichts aus­macht, dann schi­cken Sie mir Ihren Kalen­der doch per Mail zu. Ich mel­de mich dann. Die Zah­len um 40655 deu­ten aber auf ein fal­sches Zah­len­for­mat, 40655 ent­spricht dem 22.04.2011. Die Mail­adres­se: (office at kuettner.it).
    Tschüß
    Clau­di­us H. Kütt­ner

  3. Hal­lo Herr Kütt­ner,
    das ist rich­tig. Ich habe den Kalen­der für Pro­be­wei­se für das Jahr 2011 ange­legt. Ände­re ich die Jah­res­zahl auf 2013, wer­den alle Zel­len kor­rekt ver­än­dert. Ledig­lich die Anzei­ge in denn Zel­len, in den Neu­jahr usw. ste­hen soll, zeigt die Sys­tem­zahl mit dem Benut­zer­de­fi­nier­ten For­mat . KW.
    Ände­re ich die For­mel in Zel­le C3
    von =WENN(ISTFEHLER(FINDEN(A3;Feiertagsliste));WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7);””);SVERWEIS(A3;Berechnen_der_Feiertage;1;WAHR))
    in =WENN(ISTNV(SVERWEIS(A3;Berechnen_der_Feiertage;2;FALSCH));””;SVERWEIS(Kalender!A3;Berechnen_der_Feiertage;2;FALSCH))
    wird mir in der Zel­le auch das Wort Neu­jahr aus­ge­ge­ben. Zie­he ich die­se For­mel auf, gehen aber die Anzei­gen der KW in den ande­ren Zel­len ver­lo­ren. Es braucht also eine Kom­bi­na­ti­on von bei­den For­meln oder eine klei­ne Kor­rek­tur.
    Gruß
    Ange­la

  4. Lie­be Ange­la,
    Ihre For­mel

    =WENN(ISTNV(SVERWEIS(A3;Berechnen_der_Feiertage;2;FALSCH));      "" ;SVERWEIS(A3;Berechnen_der_Feiertage;2;FALSCH))

    fragt ab, ob der Wert in Zel­le A3 nicht in der Lis­te “Berechnen_der_Feiertage” vor­han­den ist, dann schrei­ben Sie eine lee­re Zei­chen­ket­te “""”. Anstel­le die­ser zwei Hoch­kom­ma schrei­ben Sie ein­fach die For­mel für die Kalen­der­wo­che, lei­der ist die ame­ri­ka­ni­sche Kalen­der­wo­che nicht iden­tisch mit der nach DIN, des­halb steht hier die fol­gen­de For­mel:

    WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7); "" )

    Klei­ner Tip. Ver­su­chen Sie es doch noch ein­mal mit mei­nen For­meln. In einem wei­te­ren Work­shop pas­se ich die Sei­te mit den Berech­nun­gen für den Kalen­der an, so dass Sie bestimm­te Fei­er­ta­ge deak­ti­vie­ren kön­nen und somit der Kalen­der uni­ver­sel­ler ver­wend­bar wird.
    Ich hof­fe Sie kom­men nun wei­ter.
    Vie­le Grü­ße
    Clau­di­us

  5. Hal­lo Clau­di­us,
    herz­li­chen Dank für die­ses Tuto­ri­al! Das hat mir die Excel-Welt deut­lich näher gebracht, auch wenn ich immer ein wenig umden­ken muss­te, da ich Excel 2011 für Mac nut­ze.
    Eine Fra­ge konn­te ich jedoch noch nicht wirk­lich lösen:
    wie wür­de ich es bewerk­stel­li­gen, einen sol­chen Kalen­der für belie­bi­ge Jah­re zu erstel­len, bei dem die Wochen­ta­ge immer an der sel­ben Stel­le blei­ben und sich statt­des­sen das Datum drum­her­um anpasst?
    Gruß
    Mark

  6. Hal­lo,

    vie­len Dank für die tol­le Anlei­tung.
    Bin Excel-Neu­ling. Ich habe zwar sehr lan­ge gebraucht, aber ch habe es hin bekom­men.
    Lei­der Feh­len beo mir 3 Fei­er­ta­ge=
    6. Janu­ar Drei­kö­nig
    1. Novem­ber Aller­hei­li­gen
    un d ein Don­ners­tag Fron­leich­nam

    Gibt es eine Mög­lich­keit, die­se ein­zu­fü­gen?
    Dan­ke, schö­ne Grü­ße Son­ja

  7. Hal­lo Son­ja,
    ich habe schon eine neue Vari­an­te des ewi­gen Kalen­ders, den ich vor eini­ger zeit mit mei­nen Schü­lern ent­wi­ckelt habe. Ich muss mal ein wenig “Drum­her­um” schrei­ben und dann stel­le ich die Anlei­tung online. Mal sehen, wird die­se oder nächs­te Woche.
    Wenn’s pres­sie­ren soll­te kann ich die Tabel­le auch per Mail zusen­den, eMail war ja im Kom­men­tar dabei.
    Tschüß
    Clau­di­us