Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 1 — Grundlagen

kalender

Ziel die­ses Work­shops ist es einen Kalen­der zu erstel­len, der nur durch Ein­ga­be 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­ta­ge mar­kiert und die Anzahl der Arbeits­ta­ge ange­zeigt wer­den. Der­ar­ti­ge Kalen­der lie­gen zwar am Ende eines Jah­res oft der Tages­zei­tung bei, aber ich möch­te ihn als elek­tro­ni­sche Lösung haben. 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. Der vor­ge­stell­te Kalen­der ist ab Excel 2002 lauf­fä­hig, ein­zig für die Berech­nung der Net­to­ar­beits­ta­ge muss eine spe­zi­el­le Funk­ti­on ein­ge­bun­den wer­den, dazu jedoch erst im Teil 3.

Vie­le “ewi­ge” Kalen­der, die Sie im Netz fin­den, kön­nen Sie frei her­un­ter­la­den, aber sel­ten fin­den Sie eine Anlei­tung wie es gemacht wird. Die­se Lücke möch­te ich schlie­ßen. Natür­lich stel­le ich am Ende den mit Excel erstell­ten Kalen­der zum Down­load zur Ver­fü­gung.

In die­sem 1. Teil des Work­shops ler­nen Sie die Vor­ge­hens­wei­se zur “Berech­nung” der Fei­er­ta­ge.

Zur mathe­ma­ti­schen Umset­zung: Es gibt fest­ste­hen­de Fei­er­ta­ge, bspw. Neu­jahr am 1. Janu­ar und glei­ten­de Fei­er­ta­ge wie bspw. Ostern. Alle ver­schieb­li­chen Fei­er­ta­gen sind von fol­gen­den 2 Fei­er­ta­gen abhän­gig bzw. aus die­sen bere­chen­bar:

  • Oster­sonn­tag und der
  • Vier­te Advent.

Mathematik

Oster­sonn­tag DM((TAG(MINUTE(Jahr/38)/2+55)&”.4.”&Jahr)/7;)*7–6
Vier­ter Advent DATUM(Jahr;12;25-WOCHENTAG(DATUM(Jahr;12;24)))

Zuerst müs­sen Sie eine Lis­te der in Ihrem Bun­des­land gel­ten­den gesetz­li­chen Fei­er­ta­ge zusam­men­stel­len und die For­meln ermit­teln.

Ich habe die in allen Regio­nen Thü­rin­gens gül­ti­gen Fei­er­ta­ge auf­ge­lis­tet. Soll­ten Sie wei­te­re benö­ti­gen, so emp­feh­le ich von www.oeftg.de die Abhand­lung zur Berech­nung gesetz­li­cher Fei­er­ta­ge. Auf der Sei­te gibt es ein PDF zum Down­load in dem wei­te­re Abhän­gig­kei­ten erklärt sind.

Berechnung der gesetzlichen Feiertage für Thüringen

Neu­jahr DATUM(Jahr;1;1)
Kar­frei­tag Oster­sonn­tag-2
Oster­sonn­tag DM((TAG(MINUTE(Jahr/38)/2+55)&”.4.”&Jahr)/7;)*7–6
Oster­mon­tag Oster­sonn­tag+1
Mai­fei­er­tag DATUM(Jahr;5;1)
Chris­ti Him­mel­fahrt Oster­sonn­tag+39
Pfingst­sonn­tag Oster­sonn­tag+49
Pfingst­mon­tag Oster­sonn­tag+50
Tag der Deut­schen Ein­heit DATUM(Jahr;10;3)
Hei­lig Abend DATUM(Jahr;12;24)
1. Weih­nachts­tag DATUM(Jahr;12;25)
2. Weih­nachts­tag DATUM(Jahr;12;26)
Sil­ves­ter DATUM(Jahr;12;31)

Nun öff­nen Sie bit­te eine neue Arbeits­map­pe mit zwei Tabel­len, die ers­te nen­nen Sie “Kalen­der”, die zwei­te “Berech­nung”. Die Tabel­le “Berech­nun­gen” wer­den Sie spä­ter ver­ste­cken.

In die Zel­le A1 der Tabel­le “Kalen­der” tra­gen Sie bit­te das Jahr 2011 ein. Dann bege­ben Sie sich in das Namen­feld und tra­gen dort anstel­le “A1” den Feld­na­men “Jahr” ein und bestä­ti­gen Ihre Ein­ga­be mit <ENTER>. Sie haben für die Zel­le “A1” den Bereichs­na­men “Jahr” defi­niert und kön­nen die­sen dann in For­meln als Varia­ble benut­zen.

Namenfeld rot umrandet Feldname “Jahr”

Nun fügen Sie im Tabel­len­blatt “Berech­nung” die Über­schrift “Berech­nun­gen für den Kalen­der” in die Zel­le A1, “Berechnen_der_Feiertage” in die Zel­le A3, “Fei­er­ta­ge” in die Zel­le B3 und “Fei­er­tags­lis­te” in die Zel­le A18. Das sind nur Über­schrif­ten die zur Bezeich­nung wei­te­rer zur Berech­nung not­wen­di­ger Berei­che die­nen.

Fügen Sie nun vor allen ein­ge­füg­ten For­meln ein “=” ein und bestä­ti­gen Sie Ihre Ein­ga­be mit <ENTER>.

Übertragen der Feiertage und Ihrer Formeln aus dem BlogPost Fügen Sie vor allen eingefügten Formeln ein “=” ein.

Nach­dem Sie alle “=” ein­ge­tra­gen haben, wer­den Sie fest­stel­len, dass in allen Zel­len, die Fei­er­ta­ge ent­hal­ten, die mit Hil­fe des Datums für den Oster­sonn­tag berech­net wer­den, Feh­ler­mel­dun­gen “#NAME?” erschei­nen. Das ist soweit kor­rekt, denn Sie haben noch kei­ne Bereichs­de­fi­ni­ti­on für “Oster­sonn­tag” durch­ge­führt.

Kli­cken Sie  nun in das Feld in dem das Datum für den Oster­sonn­tag ste­hen soll­te, hier steht die Zahl “40657”. Das ist kor­rekt, da Excel noch nicht weis, dass das ein Datum ist. Ver­ge­ben Sie für die Zel­le B6 den Bereich­sa­men “Oster­sonn­tag”.

Fehlermeldungen “#NAME?” Vergeben Sie für die Zelle B6 den Bereichsamen “Ostersonntag”.

Nach dem Bestä­ti­gen mit der <ENTER> Tas­te soll­ten in den For­meln alle Feh­ler­mel­dun­gen ver­schwun­den sein. Soll­ten bei Ihnen in der Spal­te B nicht nur Datums­an­ga­ben ste­hen, son­dern auch Zah­len, dann mar­kie­ren Sie bit­te alle Datums­wer­te und for­ma­tie­ren Sie die Zel­len mit einem Datums­for­mat.

Alle Feitage werden berechnet

Nun kom­men Sie zu einer etwas auf­wen­di­gen For­mel. Geben Sie bit­te in Zel­le A19 fol­gen­de For­mel ein:

=VERKETTEN(B4;" ";B5;" ";Ostersonntag;" ";B7;" ";B8;" ";B9;" ";B10;" ";B11;" ";B12;" ";B13;" ";B14;" ";B15;" ";B16)

Hier­mit erzeu­gen Sie eine Lis­te aller zuvor berech­ne­ten Fei­er­ta­ge, die durch Leer­zei­chen getrennt sind. Ich ver­wen­de dazu die Funk­ti­on Ver­ket­ten. Ach­ten Sie bit­te dar­auf, kei­ne Zel­le dop­pelt anzu­kli­cken und auch kei­ne Zel­le zu ver­ges­sen. Schlie­ßen Sie am Ende die Ver­ket­ten Funk­ti­on mit einer Klam­mer. Ist ein typi­scher Anfän­ger Feh­ler den Excel meist auto­ma­tisch kor­ri­gie­ren kann, aber rum­me­ckert.

Mar­kie­ren Sie die Zel­len A19:F19 und ver­bin­den Sie die­se, der Schön­heit wegen kön­nen Sie auch einen Rah­men dar­um erstel­len.

=VERKETTEN(B4;" ";B5;" ";Ostersonntag;" ";B7;" ";B8;" ";B9;" ";B10;" ";B11;" ";B12;" ";B13;" ";B14;" ";B15;" ";B16)

Lei­der müs­sen Sie noch eine klei­ne Umge­stal­tung der Posi­ti­on der Daten vor­neh­men. Für die Lis­te “Berechnen_der_Feiertage” müs­sen die Datums­an­ga­ben zwin­gend in der ers­ten Spal­te und die Bezeich­ner der Fei­er­ta­ge in der zwei­ten Spal­te ange­ord­net sein. Bit­te rea­li­sie­ren Sie das, indem Sie zuerst die Bezeich­ner nach rechts in die Spal­te C ver­schie­ben und dann den gesam­ten Bereich wie­der nach links schie­ben.

kalender11

Nun müs­sen Se wei­te­re Bereichs­de­fi­ni­tio­nen vor­neh­men, geht in einem jung­fräu­li­chen Excel Blatt am ein­fachs­ten durch Mar­kie­ren des Berei­ches, Ein­ge­ben des Bereichs­na­mens im Namen­feld und anschlie­ßen­dem Bestä­ti­gen mit der <ENTER> Tas­te. Fol­gen­de Bereichs­na­men müs­sen Sie defi­nie­ren:

  • Berechnen_der_Feiertage (1), rot
  • Fei­er­ta­ge (2), grün
  • Fei­er­tags­lis­te (3), blau

Jahr und Oster­sonn­tag haben Sie schon defi­niert. Sie kön­nen gut erken­nen, dass auch über­lap­pen­de Berei­che mög­lich sind. Bit­te kon­trol­lie­ren Sie die Bereichs­na­men mit Hil­fe des Namens-Mana­gers, Men­üband “For­meln” – Grup­pe “defi­nier­te Namen” – “Namens-Mana­ger”.

Haben Sie sich ver­tan, dann kön­nen Sie feh­ler­haf­te Defi­ni­tio­nen mit dem Namens-Mana­ger löschen oder bear­bei­ten.

 Menüband “Formeln” – Gruppe “definierte Namen” – “Namens-Manager” Kontrolle der Bereiche mit dem Namens-Manager

So das war es im 1. Teil die­ses Work­shops. In Teil 2 wer­den Sie den Kalen­der “pro­gram­mie­ren”.

Soll­ten Sie Fra­gen haben, dann mel­den Sie sich bit­te in einem Kom­men­tar oder per PM. Natür­lich dür­fen Sie auch einen Kom­men­tar schrei­ben, wenn  Sie den Work­shop toll fin­den. Zwinkerndes Smiley

Viel Erfolg beim Nach­voll­zie­hen des Work­shops.

Downloads

  1. Kalen­der in einer mög­li­chen fina­len Ver­si­on für Excel 2010,
  2. Kalen­der in einer mög­li­chen fina­len Ver­si­on für Excel 2003 und
  3. Kalen­der Teil 1 für Excel 2010.

4 Kommentare » Schreibe einen Kommentar

  1. Hal­lo Herr Kütt­ner,
    Der Work­shop gefällt mir. Bei der Lis­te der th. Fei­er­ta­ge kann aber etwas nicht stim­men. Der Mai­fei­er­tag wird bestimmt nicht mit der For­mel “Oster­sonn­tag+1” berech­net. Zumin­dest zeigt mir mein Brow­ser das so an. In der Fol­ge stim­men dann auch die ande­ren For­meln nicht mehr.
    Mit freund­li­chem Gruß
    Hans-Jür­gen Kunow

  2. Hal­lo Herr Kunow,
    das alte Pro­blem mit dem IE, er igno­riert die Schrift­grö­ße in der Tabel­le, in der ich die For­meln für die Fei­er­ta­ge ein­ge­tra­gen habe. Es gibt bei der For­mel für den Oster­sonn­tag in der rech­ten Spal­te einen Umbruch und beim Tag der Deut­schen Ein­heit einen Umbruch in der lin­ken Spal­te. Des­halb ist es am Ende links und rechts gleich lang.

    Als schnel­len Work­a­round habe ich hier die Fei­er­ta­ge als Bild ver­linkt.

    Vie­len Dank für die Mel­dung des Feh­lers.

    Mit freund­li­chen Grü­ßen
    Clau­di­us H. Kütt­ner

  3. Hal­lo Herr Kütt­ner,

    mit gro­ße Freu­de habe ich den Work­shop für die­sen Kalen­der durch­ge­ar­bei­tet (nach so etwas hat­te ich schon lan­ge gesucht).

    Die Freu­de und der Lern­ef­fekt war dabei so groß, dass ich mich spon­tan dazu ent­schie­den habe mich dar­an zu ver­su­chen, einen “Urlaubs­pla­ner” unter Berück­sich­ti­gung von Feiertagen/Urlaub zu erstel­len.
    Das Umge­stall­ten war mit Hil­fe des Work­shops ein Leich­tes. Infor­ma­tio­nen zu Urlaub wer­den manu­ell ein­ge­tra­gen.
    Nun hät­te ich gern, dass je Kalen­der­wo­che die tat­säch­li­chen Arbeits­ta­ge (abzgl. Feiertage/Wochenende/Urlaub) in einer Zel­le am ers­ten Tag in der Woche (Schwie­rig­keit Jahresanfang/Jahresende) zusam­men­ge­zählt wer­den (evtl. über ANZAHL­LE­E­RE­ZEL­LEN).

    Lei­der kom­me ich an die­ser Stel­le nicht wei­ter und hof­fe etwas auf Ihre Unter­stüt­zung.

    Vie­len Dank für die­sen Work­shop
    Mit freund­li­chen Grü­ßen

  4. Hal­lo Jay,
    schön, dass mein Work­shop so gut ange­kom­men ist. Mitt­ler­wei­le gibt es schon ein Update, das ich aber n.n. ver­öf­fent­licht habe. Dort kann man Fei­er­ta­ge an- bzw. abwäh­len. Zu Ihrem Pro­blem muss ich erst ein­mal ein wenig malen und über­le­gen wie es gehen könn­te. Vor Weih­nach­ten habe ich aber immer viel um die Ohren. Aber ich blei­be dran.
    Tschüß
    Clau­di­us H. Kütt­ner