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 Workshops: 

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 checken.

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 programmiert. 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 werden.

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 festlegen”. 

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 ausschaut.

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 Nachmachen. 

Down­loads