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

kalender

Ziel dieses Workshops ist es einen Kalender zu erstellen, der nur durch Eingabe der Jahreszahl automatisch ein Kalenderblatt erzeugt, in dem die Wochenenden und die gesetzlichen Feiertage markiert und die Anzahl der Arbeitstage angezeigt werden. Derartige Kalender liegen zwar am Ende eines Jahres oft der Tageszeitung bei, aber ich möchte ihn als elektronische Lösung haben. Der Kalender soll für die Nutzung der Design-Features der neueren Excel Versionen vorbereitet sein. Der vorgestellte Kalender ist ab Excel 2002 lauffähig, einzig für die Berechnung der Nettoarbeitstage muss eine spezielle Funktion eingebunden werden, dazu jedoch erst im Teil 3.

Viele “ewige” Kalender, die Sie im Netz finden, können Sie frei herunterladen, aber selten finden Sie eine Anleitung wie es gemacht wird. Diese Lücke möchte ich schließen. Natürlich stelle ich am Ende den mit Excel erstellten Kalender zum Download zur Verfügung.

In diesem 1. Teil des Workshops lernen Sie die Vorgehensweise zur “Berechnung” der Feiertage.

Zur mathematischen Umsetzung: Es gibt feststehende Feiertage, bspw. Neujahr am 1. Januar und gleitende Feiertage wie bspw. Ostern. Alle verschieblichen Feiertagen sind von folgenden 2 Feiertagen abhängig bzw. aus diesen berechenbar:

  • Ostersonntag und der
  • Vierte Advent.

Mathematik

Ostersonntag DM((TAG(MINUTE(Jahr/38)/2+55)&“.4.“&Jahr)/7;)*7-6
Vierter Advent DATUM(Jahr;12;25-WOCHENTAG(DATUM(Jahr;12;24)))

Zuerst müssen Sie eine Liste der in Ihrem Bundesland geltenden gesetzlichen Feiertage zusammenstellen und die Formeln ermitteln.

Ich habe die in allen Regionen Thüringens gültigen Feiertage aufgelistet. Sollten Sie weitere benötigen, so empfehle ich von www.oeftg.de die Abhandlung zur Berechnung gesetzlicher Feiertage. Auf der Seite gibt es ein PDF zum Download in dem weitere Abhängigkeiten erklärt sind.

Berechnung der gesetzlichen Feiertage für Thüringen

Neujahr DATUM(Jahr;1;1)
Karfreitag Ostersonntag-2
Ostersonntag DM((TAG(MINUTE(Jahr/38)/2+55)&“.4.“&Jahr)/7;)*7-6
Ostermontag Ostersonntag+1
Maifeiertag DATUM(Jahr;5;1)
Christi Himmelfahrt Ostersonntag+39
Pfingstsonntag Ostersonntag+49
Pfingstmontag Ostersonntag+50
Tag der Deutschen Einheit DATUM(Jahr;10;3)
Heilig Abend DATUM(Jahr;12;24)
1. Weihnachtstag DATUM(Jahr;12;25)
2. Weihnachtstag DATUM(Jahr;12;26)
Silvester DATUM(Jahr;12;31)

Nun öffnen Sie bitte eine neue Arbeitsmappe mit zwei Tabellen, die erste nennen Sie “Kalender”, die zweite “Berechnung”. Die Tabelle “Berechnungen” werden Sie später verstecken.

In die Zelle A1 der Tabelle “Kalender” tragen Sie bitte das Jahr 2011 ein. Dann begeben Sie sich in das Namenfeld und tragen dort anstelle “A1” den Feldnamen “Jahr” ein und bestätigen Ihre Eingabe mit <ENTER>. Sie haben für die Zelle “A1” den Bereichsnamen “Jahr” definiert und können diesen dann in Formeln als Variable benutzen.

Namenfeld rot umrandet Feldname “Jahr”

Nun fügen Sie im Tabellenblatt “Berechnung” die Überschrift “Berechnungen für den Kalender” in die Zelle A1, “Berechnen_der_Feiertage” in die Zelle A3, “Feiertage” in die Zelle B3 und “Feiertagsliste” in die Zelle A18. Das sind nur Überschriften die zur Bezeichnung weiterer zur Berechnung notwendiger Bereiche dienen.

Fügen Sie nun vor allen eingefügten Formeln ein “=” ein und bestätigen Sie Ihre Eingabe mit <ENTER>.

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

Nachdem Sie alle “=” eingetragen haben, werden Sie feststellen, dass in allen Zellen, die Feiertage enthalten, die mit Hilfe des Datums für den Ostersonntag berechnet werden, Fehlermeldungen “#NAME?” erscheinen. Das ist soweit korrekt, denn Sie haben noch keine Bereichsdefinition für “Ostersonntag” durchgeführt.

Klicken Sie  nun in das Feld in dem das Datum für den Ostersonntag stehen sollte, hier steht die Zahl “40657”. Das ist korrekt, da Excel noch nicht weis, dass das ein Datum ist. Vergeben Sie für die Zelle B6 den Bereichsamen “Ostersonntag”.

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

Nach dem Bestätigen mit der <ENTER> Taste sollten in den Formeln alle Fehlermeldungen verschwunden sein. Sollten bei Ihnen in der Spalte B nicht nur Datumsangaben stehen, sondern auch Zahlen, dann markieren Sie bitte alle Datumswerte und formatieren Sie die Zellen mit einem Datumsformat.

Alle Feitage werden berechnet

Nun kommen Sie zu einer etwas aufwendigen Formel. Geben Sie bitte in Zelle A19 folgende Formel ein:

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

Hiermit erzeugen Sie eine Liste aller zuvor berechneten Feiertage, die durch Leerzeichen getrennt sind. Ich verwende dazu die Funktion Verketten. Achten Sie bitte darauf, keine Zelle doppelt anzuklicken und auch keine Zelle zu vergessen. Schließen Sie am Ende die Verketten Funktion mit einer Klammer. Ist ein typischer Anfänger Fehler den Excel meist automatisch korrigieren kann, aber rummeckert.

Markieren Sie die Zellen A19:F19 und verbinden Sie diese, der Schönheit wegen können Sie auch einen Rahmen darum erstellen.

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

Leider müssen Sie noch eine kleine Umgestaltung der Position der Daten vornehmen. Für die Liste “Berechnen_der_Feiertage” müssen die Datumsangaben zwingend in der ersten Spalte und die Bezeichner der Feiertage in der zweiten Spalte angeordnet sein. Bitte realisieren Sie das, indem Sie zuerst die Bezeichner nach rechts in die Spalte C verschieben und dann den gesamten Bereich wieder nach links schieben.

kalender11

Nun müssen Se weitere Bereichsdefinitionen vornehmen, geht in einem jungfräulichen Excel Blatt am einfachsten durch Markieren des Bereiches, Eingeben des Bereichsnamens im Namenfeld und anschließendem Bestätigen mit der <ENTER> Taste. Folgende Bereichsnamen müssen Sie definieren:

  • Berechnen_der_Feiertage (1), rot
  • Feiertage (2), grün
  • Feiertagsliste (3), blau

Jahr und Ostersonntag haben Sie schon definiert. Sie können gut erkennen, dass auch überlappende Bereiche möglich sind. Bitte kontrollieren Sie die Bereichsnamen mit Hilfe des Namens-Managers, Menüband “Formeln” – Gruppe “definierte Namen” – “Namens-Manager”.

Haben Sie sich vertan, dann können Sie fehlerhafte Definitionen mit dem Namens-Manager löschen oder bearbeiten.

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

So das war es im 1. Teil dieses Workshops. In Teil 2 werden Sie den Kalender “programmieren”.

Sollten Sie Fragen haben, dann melden Sie sich bitte in einem Kommentar oder per PM. Natürlich dürfen Sie auch einen Kommentar schreiben, wenn  Sie den Workshop toll finden. Zwinkerndes Smiley

Viel Erfolg beim Nachvollziehen des Workshops.

Downloads

  1. Kalender in einer möglichen finalen Version für Excel 2010,
  2. Kalender in einer möglichen finalen Version für Excel 2003 und
  3. Kalender Teil 1 für Excel 2010.

4 Kommentare » Schreibe einen Kommentar

  1. Hallo Herr Küttner,
    Der Workshop gefällt mir. Bei der Liste der th. Feiertage kann aber etwas nicht stimmen. Der Maifeiertag wird bestimmt nicht mit der Formel „Oster­sonn­tag+1“ berechnet. Zumindest zeigt mir mein Browser das so an. In der Folge stimmen dann auch die anderen Formeln nicht mehr.
    Mit freundlichem Gruß
    Hans-Jürgen Kunow

  2. Hallo Herr Kunow,
    das alte Problem mit dem IE, er ignoriert die Schriftgröße in der Tabelle, in der ich die Formeln für die Feiertage eingetragen habe. Es gibt bei der Formel für den Ostersonntag in der rechten Spalte einen Umbruch und beim Tag der Deutschen Einheit einen Umbruch in der linken Spalte. Deshalb ist es am Ende links und rechts gleich lang.

    Als schnellen Workaround habe ich hier die Feiertage als Bild verlinkt.

    Vielen Dank für die Meldung des Fehlers.

    Mit freundlichen Grüßen
    Claudius H. Küttner

  3. Hallo Herr Küttner,

    mit große Freude habe ich den Workshop für diesen Kalender durchgearbeitet (nach so etwas hatte ich schon lange gesucht).

    Die Freude und der Lerneffekt war dabei so groß, dass ich mich spontan dazu entschieden habe mich daran zu versuchen, einen „Urlaubsplaner“ unter Berücksichtigung von Feiertagen/Urlaub zu erstellen.
    Das Umgestallten war mit Hilfe des Workshops ein Leichtes. Informationen zu Urlaub werden manuell eingetragen.
    Nun hätte ich gern, dass je Kalenderwoche die tatsächlichen Arbeitstage (abzgl. Feiertage/Wochenende/Urlaub) in einer Zelle am ersten Tag in der Woche (Schwierigkeit Jahresanfang/Jahresende) zusammengezählt werden (evtl. über ANZAHLLEEREZELLEN).

    Leider komme ich an dieser Stelle nicht weiter und hoffe etwas auf Ihre Unterstützung.

    Vielen Dank für diesen Workshop
    Mit freundlichen Grüßen

  4. Hallo Jay,
    schön, dass mein Workshop so gut angekommen ist. Mittlerweile gibt es schon ein Update, das ich aber n.n. veröffentlicht habe. Dort kann man Feiertage an- bzw. abwählen. Zu Ihrem Problem muss ich erst einmal ein wenig malen und überlegen wie es gehen könnte. Vor Weihnachten habe ich aber immer viel um die Ohren. Aber ich bleibe dran.
    Tschüß
    Claudius H. Küttner