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-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 4. Teil des Work­shops legen Sie die restlichen Monate und Tage an, ermitteln die Arbeitstage und stellen den Druckbereich ein.

Sie könnten die ersten Teile der Workshops:

nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der Dateien herunter und starten mit dem Workshop Ihrer Wahl:

Die Dateien zum Workshop finden Sie wie immer am Ende dieses Blog Post. Für die vergrößerte Darstellung eines Bildes klicken Sie auf das Bild.

Überschriften und Monate mit Schriftfarbe versehen

Markieren Sie die Zellen A1, B2 und D2, also die Zellen mit dem Jahr und den Monaten Januar und Februar. Wählen Sie dann eine Designfarbe für die Schrift aus, hier habe ich wieder das mittlere Design-Blau gewählt.

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

Markieren Sie das Feld D2, das mit dem Februar, und gehen Sie auf die rechte untere Ecke, klicken Sie mit der linken Maus darauf und ziehen die Maus nach rechts, so füllen Sie die Monate bis Dezember aus. Wahrscheinlich werden Sie am Bildrand absetzen, den Fensterinhalt nach rechts scrollen und dann weiter ausfüllen. Enden Sie mit dem Dezember, es sollte die Zelle AJ2 sein.

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

Markieren Sie im Monat Februar die Tage vom 1. bis zum 7. Februar (D3:D9). Halten Sie die <STRG>-Taste gedrückt und markieren Sie die Tage für die Monate März bis Dezember. sollten Sie abrutschen, dann müssen Sei weder von vorn beginnen. Klicken Sie dann auf dem Menüband “Start” die Gruppe “Bearbeiten” und dann “Füllbereich” und “Rechts”. Alternativ hätten Sie auch <STRG>R benutzen können. Verfahren Sie für die Wochentage und die Feiertage/KW analog. Am Ende sollte die erste Woche von Januar bis Dezember eingetragen 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

Markieren Sie nun in Zeile 9 den Bereich A9:AJ9, Sie könnten auch im Namenfeld “A9:AJ9” ohne Anführungszeichen eingeben und mit der <ENTER>-Taste bestätigen. Klicken Sie auf die rechte untere Ecke, Mauszeiger muss zuerst zu dünnem schwarzen Plus werden, und ziehen Sie die Maus bis zur Zeile 33. Sie müssen ggf. absetzen. Wenn Sie alles korrekt ausgefüllt haben, dann erscheint in der rechten unteren Zelle “Silvester”.

Kalender nach unten ausfüllen

Sie wissen, dass einige Monate 28/29, manche 30 und andere 31 Tage haben. Das müssen Sie nun korrigieren. Markieren Sie dazu die Felder, bei denen es mit dem 1. des Nachfolgemonats weiter geht. Das müssten der 2. und 3. März, der 1. Mai, der 1. Juli, der 1. Oktober und der 1. Dezember sein.

Nach dem Markieren drücken Sie bitte die <ENTF>-Taste, die Formeln in den Zellen werden gelöscht. Nun müssen Sie noch die bedingte Formatierung entfernen. Wählen Sie auf dem Menüband “Start” in der Gruppe “Formatvorlagen” die “Bedingte Formatierung”, dann “Regeln löschen” und “Regeln in ausgewählten Zellen 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

Markieren Sie alle Tage-Spalten und alle Wochentag-Spalten, sie werden auf eine Breite von 7 Excel-Einheiten eingestellt. Klicken Sie mit der rechtem Maus auf den Spaltenkopf und wählen Sie “Spaltenbreite…”. Geben Sie hier den Wert 7 ein. Verfahren Sie analog für die Feiertag/KW-Spalten und stellen Sie die Breite auf 25 ein.

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

Nun müssen Sie sich um den 29. Februar kümmern. Dazu verwenden wir einen Trick, denn Excel kennt Schaltjahre und wendet Sie auch an. Wenn Sie in einem Schaltjahr zum 28. Februar einen Tag hinzuaddieren, dann erhalten Sie den 29. Februar. Vollziehen Sie das in einem Nichtschaltjahr, dann erhalten Sie den 1. März. Wir müssen also den Monat checken.

Wir betten die in den Zellen D31 und F31 stehenden Formeln einfach in eine Wenn-Anweisung ein.

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

Um die Zelle E31 brauchen Sie sich nicht kümmern, die ist schon geschickt programmiert. Zwinkerndes Smiley

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

Nun formatieren Sie die Zeile in der die Anzahl der Arbeitstage stehen sollen. Markieren Sie dazu Zeile 2 (hier stehen die Monate) und Zeile 34 (hier sollen die Arbeitstage stehen). Füllen Sie die Inhalte nach unten aus, bspw. <STRG>U oder über das Menüband.

Jetzt ermitteln Sie die Arbeitstage, dazu gibt es die Funktion NETTOARBEITSTAGE. Klicken Sie bitte in Zelle A34, dort steht z.Z. Januar und geben folgende Formel ein:

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

Das Startdatum ist immer das kleinste Datum in der Spalte (MIN), das Enddatum dann das größte Datum (MAX). Um ggf. Feiertage abzuziehen geben Sie den Bereichsnamen “Feiertage” ein. Zählen Sie bspw. die Arbeitstage im Dezember, es funktioniert. Benutzen Sie die benutzerdefinierte Formatierung für die Zelle A34 und geben Sie [0 “Arbeitstage] ohne eckige Klammern ein.

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

Füllen Sie den Bereich A34:AJ34 nach rechts aus, benutzen Sie den Befehl aus dem Menüband oder <STRG>R. Nun sollten alle Arbeitstage von Januar bis Dezember berechnet werden.

Markieren Sie den Bereich A1:AJ34 und wählen Sie auf dem Menüband “Seitenlayout” die Gruppe “Seite einrichten”, dann “Druckbereich” und “Druckbereich festlegen”.

Jetzt müssen Sie Excel so einstellen, dass es alles auf eine Seite druckt. Dazu Klicken Sie auf dem Menüband “Seitenlayout” innerhalb der Gruppe “Seite einrichten” auf das kleine Kästchen in der rechten unteren Ecke, wählen dann als Ausrichtung das Querformat und Klicken im Bereich Skalierung auf Anpassen und wählen 1 Seite breit und 1 Seite hoch.

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

Die Druckvorschau über Datei – Drucken zeigt wie der Kalender im Ausdruck ausschaut.

Druckvorschau

Das war es mit Teil 4 des Workshops. Im 5. Teil werden Sie das Tabellenblatt “Berechnung” verbergen und die Datei in einem Format für ältere Excel Versionen abspeichern. Dazu müssen Sie die Funktion NETTOARBEITSTAGE ersetzen, denn diese gab es damals noch nicht.

Viel Erfolg und Spaß beim Nachmachen.

Down­loads

7 Kommentare » Schreibe einen Kommentar

  1. Hallo Herr Küttner,
    die Anleitung zum Erstellen des Kalenders ist wirklich klasse. Bis Teil 4 des Workshops habe ich mich durchgearbeitet. Bis auf das namentliche Anzeigen der Feiertage (Neujahr; Karfreitag usw.) hat alles wirklich gut geklappt. Ich habe die Formeln und die markierten Bereiche bei der Vergabe der Namen mehrfach neu eigegeben, aber es funktioniert einfach nicht. In den einzelnen Zellen des fertigen Kalenders wird jeweils die Excel-Systemzahl angezeigt. Für Neujahr 40544. KW, für Karfreitag 40655. KW. Wo liegt der Fehler?
    Angela

  2. Liebe Angela,
    wenn es Ihnen nichts ausmacht, dann schicken Sie mir Ihren Kalender doch per Mail zu. Ich melde mich dann. Die Zahlen um 40655 deuten aber auf ein falsches Zahlenformat, 40655 entspricht dem 22.04.2011. Die Mailadresse: (office at kuettner.it).
    Tschüß
    Claudius H. Küttner

  3. Hallo Herr Küttner,
    das ist richtig. Ich habe den Kalender für Probeweise für das Jahr 2011 angelegt. Ändere ich die Jahreszahl auf 2013, werden alle Zellen korrekt verändert. Lediglich die Anzeige in denn Zellen, in den Neujahr usw. stehen soll, zeigt die Systemzahl mit dem Benutzerdefinierten Format . KW.
    Ändere ich die Formel in Zelle 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 Zelle auch das Wort Neujahr ausgegeben. Ziehe ich diese Formel auf, gehen aber die Anzeigen der KW in den anderen Zellen verloren. Es braucht also eine Kombination von beiden Formeln oder eine kleine Korrektur.
    Gruß
    Angela

  4. Liebe Angela,
    Ihre Formel

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

    fragt ab, ob der Wert in Zelle A3 nicht in der Liste “Berechnen_der_Feiertage” vorhanden ist, dann schreiben Sie eine leere Zeichenkette “""”. Anstelle dieser zwei Hochkomma schreiben Sie einfach die Formel für die Kalenderwoche, leider ist die amerikanische Kalenderwoche nicht identisch mit der nach DIN, deshalb steht hier die folgende Formel:

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

    Kleiner Tip. Versuchen Sie es doch noch einmal mit meinen Formeln. In einem weiteren Workshop passe ich die Seite mit den Berechnungen für den Kalender an, so dass Sie bestimmte Feiertage deaktivieren können und somit der Kalender universeller verwendbar wird.
    Ich hoffe Sie kommen nun weiter.
    Viele Grüße
    Claudius

  5. Hallo Claudius,
    herzlichen Dank für dieses Tutorial! Das hat mir die Excel-Welt deutlich näher gebracht, auch wenn ich immer ein wenig umdenken musste, da ich Excel 2011 für Mac nutze.
    Eine Frage konnte ich jedoch noch nicht wirklich lösen:
    wie würde ich es bewerkstelligen, einen solchen Kalender für beliebige Jahre zu erstellen, bei dem die Wochentage immer an der selben Stelle bleiben und sich stattdessen das Datum drumherum anpasst?
    Gruß
    Mark

  6. Hallo,

    vielen Dank für die tolle Anleitung.
    Bin Excel-Neuling. Ich habe zwar sehr lange gebraucht, aber ch habe es hin bekommen.
    Leider Fehlen beo mir 3 Feiertage=
    6. Januar Dreikönig
    1. November Allerheiligen
    un d ein Donnerstag Fronleichnam

    Gibt es eine Möglichkeit, diese einzufügen?
    Danke, schöne Grüße Sonja

  7. Hallo Sonja,
    ich habe schon eine neue Variante des ewigen Kalenders, den ich vor einiger zeit mit meinen Schülern entwickelt habe. Ich muss mal ein wenig „Drumherum“ schreiben und dann stelle ich die Anleitung online. Mal sehen, wird diese oder nächste Woche.
    Wenn’s pressieren sollte kann ich die Tabelle auch per Mail zusenden, eMail war ja im Kommentar dabei.
    Tschüß
    Claudius