11:25 Uhr
Urlaubsplaner und dynamischer Kalender mit Monatsansicht (Urlaubsplaner 2020/2021/2022 in Excel)
Für eine Urlaubsplanung soll für das laufende Kalenderjahr eine Excelliste erstellt werden in der für jeden Monat ein Tabellenblatt mit allen Beschäftigten und die jeweiligen Wochentage innerhalb des Monats aufgelistet werden, so dass hier die entsprechende Urlaubsplanung erfolgen kann.
Dabei sollen die Wochentage (Montag bis Sonntag) ausgegeben werden und sowohl Wochenende (Samstag und Sonntag) als auch Feiertage farblich hervorgehoben werden.
Als Vorlage dient hier das Tabellenblatt "Monat" wobei in der Zelle A8 der Januar für das Jahr 2014 ausgewählt ist:
Im Beispiel ist der Neujahrestag (1. Januar) als Feiertag grau hinterlegt und das Wochenende vom 25. bis 26. Januar 2014 ebenfalls mit blau hinterlegt.
Ferner hat der Monat Januar 31 Tage.
Da die Tabelle händisch sehr aufwändig zu erstellen ist, sollen für alle Monate nach Eingabe eines Kalenderjahres die einzelnen Wochentage ermittelt werden und auch nur die Tage ausgegeben werden, die im Monat auch vorhanden sind. Insbesondere gilt dieses natürlich für Februar, wo sowohl 28 als auch 29 Tage möglich sind.
Ziel ist es, die Liste der Beschäftigten im Tabellenblatt "Monat" zu aktualisieren und im Tabellenblatt "Einstellungen" das Jahr einzutragen um über ein Makro die Monate Januar bis Dezember des Jahres zu erstellen.
Wie im Beitrag "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben wird über die Schaltfläche "Kalenderblätter anlegen" das Blatt Monat 12 mal kopiert und mit entsprechender Monatsauswahl in den jeweiligen Monat unbenannt.
Bis es soweit ist, bedarf es aber einiger Schritte.
Nachtrag: Excel-Datei als Beispielvorlage
Nach Erläuterung der einzelnen Schritte wird zum Abschluss die Umsetzung als Exceldatei zur Verfolgung gestellt. Zum besseren Verständnis ist es aber sicherlich sinnvoll auch die einzelnen Schritte bis zur Fertigstellung nachzuvollziehen, da dadurch auch Anpassungen an die eigenen Bedrüfnisse wesentlich leichter werden.
Lösung
Zur Umsetzung eines Kalenders werden in diesen Beispiel einige Hilfstabellen verwendet, die dann entsprechende Informationen zum jeweiligen Monat enthalten. Da der Urlaubsplaner innerhalb Hessen verwendet werden soll, sind hier zum Beispiel die hessischen Feiertage (damit sind nicht lokale Feiertage wie der Wäldchestag gemeint) eingeplant.
1. Hilfstabelle: Wieviele Tage hat ein Monat
Jeder Monat im Jahr hat 28 Tage. Als Besonderheit hat der Februar entweder 28 Tage (normales Jahr) oder 29 Tage (im Schaltjahr).
Entsprechend wurde eine Tabelle mit allen Monaten angelegt und sowohl der Monat als Zahl als auch die Anzahl des Tages für den Monat festgelegt.
Das Tabellenblatt "Monate" sieht dabei wie folgt aus:
Dieses Tabellenblatt ist als Hilfstabelle ausgeblendet und bedarf auch keiner weiteren Anpassungen.
Die Zellen A2 bis A13 haben hierbei den Namen "Monat" zugewiesen bekommen um diesen als Auswahlfeld für die einzelnen Monatsblätter nutzen zu können.
- Die Definition von Namensräumen durch Verwendung des Namensmanager sowie die Anlage von Dropdownliste in Excel ist im Artikel "Formulare gestalten in Excel" beschrieben.
Daneben sind die Zellen A2 bis C13 als "Tage_Monat" definiert. Durch die Namenszuweisung muss nun nicht mehr auf das Tabellenblatt Monate verwiesen werden sondern auf die zugewiesenen Namen.
Die Monate Januar sowie März bis Dezember haben stets die gleichen Anzahl an Tagen (entweder 30 oder 31) nur der Monat Februar hat entweder 28 oder 29 Tage, je nachdem ob es sich um ein Schaltjahr oder um ein normales Jahr handelt.
Zur Berechnung des Schaltjahres wurde abhängig vom Geschäftsjahr folgende Formel verwendet:
=WENN((REST(Geschäftsjahr;4)=0)-(REST(Geschäftsjahr;100)=0)+(REST(Geschäftsjahr;400)=0)=0;28;29)
Ein Schaltjahr tritt dann auf, wenn ein Jahr durch 4, aber nicht auch durch 100 ohne Rest teilbar ist, mit der Ausnahme, dass ein durch 400 ohne Rest teilbares Jahr wiederum ein Schaltjahr ist (z. B. das Jahr 2000).
Zur Erklärung der Formel
Rest(Geschäftsjahr;4=0) liefert FALSCH aus, sofern das Jahr durch 4 mit Rest teilbar ist, ansonsten WAHR. Ähnlich verhält es sich mit den anderen Bestandteilen dieser Formel.
2. Hilfstabelle: Feiertage
Eine der wichtigsten Punkte ist die Behandlung von Feiertagen. Da bestimmte Feiertage auf unterschiedliche Termine im Jahr fallen gibt es unterschiedliche Berechnungsformen. Bezugsdaten sind dabei Ostersonntag und Weihnachten beziehungsweise der 4. Advent.
Zur Berechnung der Osterformel wurde ein mathematisches Verfahren entwickelt. Die Gaußsche Osterformel von Carl Friedrich Gauß erlaubt die Berechnung des Osterdatums für ein gegebenes Jahr.
- Im Rahmen eines Wettbewerbes wurde eine entsprechende kurze Formel zur Ermittlung des Osterdatum ermittelt. Eine ausführliche Erläuterung dieser Berechnung ist auf der Seite Excelformeln.de: Ermittlung des Ostersonntags und aller beweglichen Feiertage erläutert. Neben der Berechnung des Osterdatum ist hier auch die Berechnung der übrigen Feiertage auf Excelformeln.de: Von Weihnachten abgeleitete Feier- und Gedenktage erläutert.
Da nicht alle Feiertage in allen Bundesländern gültig sind bedarf es auch der Information, ob ein Feiertag entsprechende Gültigkeit hat.
- Die gesetzliche Grundlage für Feiertage in Hessen ist zum Beispiel das Hessisches Feiertagsgesetz (HFeiertagsG) hier sind die gesetzlichen Feiertage im § 1 Gesetzliche Feiertage HfeiertagsG geregelt.
Hierzu wurde das Tabellenblatt "Feiertage" angelegt, welches ebenfalls als Hilfstabelle ausgeblendet ist. Hier kann es jedoch sinnvoll sein, diese Tabelle eingeblendet zu behalten, um etwaige Feiertage ergänzen zu können.
Dabei haben die Zellen A2 bis C25 den Namen "Feiertage_Hessen" zugewiesen, so dass dieses Array später verwendet werden kann.
Zur leichteren Berechnung, beziehungsweise besserer Lesbarkeit der Formeln, wurde der Zelle A8 der Name "VierteAdvent", der Zelle A10 der Name "Weihnachten" und der Zelle A16 der Name "Ostersonntag" zugewiesen.
Somit kann in den einzelnen Formeln Bezug auf das jeweilige Datum genommen werden. In der Spalte C ist für eine spätere Hervorhebung des Feiertages für gesetzlich gültige Feiertage eine 2 einzutragen, so dass diese später gesondert behandelt werden. Im Beispiel sind die für Hessen relevante Feiertage markiert.
Hierbei haben die einzelnen Formeln folgenden Aufbau:
Zelle | Datumsformel | Feiertag |
---|---|---|
A2 | =VierteAdvent-35 | Volkstrauertag |
A3 | =VierteAdvent-32 | Buss- u. Bettag |
A4 | =VierteAdvent-28 | Totensonntag/Ewigkeitssonntag |
A5 | =VierteAdvent-21 | 1. Advent |
A6 | =VierteAdvent-14 | 2. Advent |
A7 | =VierteAdvent-7 | 3. Advent |
A8 | =Weihnachten-WOCHENTAG(Weihnachten;2) | 4. Advent „VierteAdvent“ |
A9 | =DATUM(Geschäftsjahr;12;24) | Heiligabend |
A10 | =DATUM(Geschäftsjahr;12;25) | 1. Weihnachtstag „Weihnachten“ |
A11 | =DATUM(Geschäftsjahr;12;26) | 2. Weihnachtstag |
A12 | =DATUM(Geschäftsjahr;12;31) | Silvester |
A13 | =Ostersonntag-52 | Altweiber |
A14 | =Ostersonntag-48 | Rosenmontag |
A15 | =Ostersonntag-2 | Karfreitag |
A16 | =7*RUNDEN((4&-Geschäftsjahr)/7+REST(19*REST(Geschäftsjahr;19)-7;30)*0,14;)-6 | Ostersonntag „Ostersonntag“ |
A17 | =Ostersonntag+1 | Ostermontag |
A18 | =Ostersonntag+39 | Himmelfahrt |
A19 | =Ostersonntag+49 | Pfingstsonntag |
A20 | =Ostersonntag+50 | Pfingstmontag |
A21 | =Ostersonntag+60 | Fronleichnam |
A22 | =DATUM(Geschäftsjahr;5;1) | Tag der Arbeit |
A23 | =DATUM(Geschäftsjahr;10;3) | Tag der deutschen Einheit |
A24 | =DATUM(Geschäftsjahr;12;6) | Nikolaus |
A25 | =DATUM(Geschäftsjahr;1;1) | Neujahrstag |
Im Wesentlichen werden bei Feiertagen die Funktionen DATUM und WOCHENTAG genutzt. Gerade beim 4. Advent wird dieses deutlich, ist dieses doch der Sonntag vor Weihnachten.
Die Formel DATUM setzt aus den Daten Jahr, Monat, Tag ein entsprechendes Datum zusammen.
So kann der 13. Juli 1978 als =DATUM(1978,07,13) ausgegeben werden. Dieses ist als Formel später noch wichtig, um das Monatsblatt zu gestalten.
Die Formel WOCHENTAG ermittelt um welchen Tag in der Woche es sich bei einen Datum handelt.
Neben einen Bezug auf das Datum kann auch ein Typ der Woche mit angegeben werden. Standardmäßig beginnt bei Excel die Woche mit Sonntag. Um nun die Wochentage ab Montag zu zählen kann in der Formel der Montag als erster Wochentag mit angegeben werden.
Ein Teil der Formel in der Zelle A8 liefert durch WOCHENTAG(Weihnachten;2) auf den wievielten Tag in der Woche ab Montag Weihnachten fällt. Um nun den Sonntag vor Weihnachten zu ermitteln wird diese Anzahl von Tagen von Weihnachten abgezogen.
Fällt nun der 1. Weihnachtsfeiertag auf Freitag, dieses ist am 25.12.2015 der Fall, so liefert die Formel den 5. Wochentag so dass der vierte Advent fünf Tage vor den 25.12.2015 liegt und damit am 20.12.2015.
3. Aufbau des Monatblattes
Zur Darstellung der einzelnen Monate werden wiederum einige Hilfszeilen verwendet, die sowohl das Datum, als auch etwaige weitere Informationen zum Monat (Anzahl der Tage, Wochentag, Feiertag, gesetzlich relevanter Feiertag) verwenden und dadurch das Aussehen eines jeden Monats gestalten.
Zur besseren Lesbarkeit wurden hier einzelne Hilfszeilen verwendet, obgleich die farbliche Hervorhebung des Wochenendes oder Feiertages über die bedingte Formatierung auch direkt als Formel funktionieren würde.
Der erste Tag eines Monats beinhaltet folgende Hilfszeilen:
Ab den 28. Tag eines Monats wird es dann relevant, wie viele Tage ein Monat hat (unter Berücksichtigung eines Schaltjahres).
Sofern ab Spalte B der Monat mit 1. beginnt wären die Tage 29 bis 31 dann in den Spalten AD bis AF zu finden.
Zelle | Inhalt | Formel |
---|---|---|
A1 | Jahr | =Geschäftsjahr |
A8 | Monat | Liste mit =Monat - Definitionen von Listen ist im Artikel "Formulare gestalten in Excel" beschrieben |
B2 | Tage im Monat | =SVERWEIS(A8;Tage_Monat;2;FALSCH) |
B14 | Tag | Für die Tage 1 bis 28 ein fester Wert (Beispiel 1) |
B3 | Datum | =WENN(B14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));B14);““) |
B4 | Feiertag | =WENN(ISTNV(SVERWEIS(B3;Feiertage_Hessen;3;FALSCH));0;SVERWEIS(B3;Feiertage_Hessen;3;FALSCH)) |
B5 | Bezeichnung Feiertag | =WENN(ISTNV(SVERWEIS(B3;Feiertage_Hessen;2;FALSCH));““;SVERWEIS(B3;Feiertage_Hessen;2;FALSCH)) |
B6 | Wochenende | =WENN(B3=““;0;WENN(WOCHENTAG(B3;2)>5;1;0)) |
B7 | Hilfstabelle | =B4+B6 |
B9 | Bezeichnung | =WENN(B3=““;““;WENN(B5=““;TEXT(B3;“TTTT“);B5)) |
AD3 | Datum | =WENN(B2>28;WENN(AD14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AD14);““);““) |
AD9 | Bezeichnung | =WENN(AD3=““;““;WENN(AD5=““;TEXT(AD3;“TTTT“);AD5)) |
AD14 | Tag | =WENN(B2>28;29;““) |
AE3 | Datum | =WENN(B2>29;WENN(AE14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AE14);““);““) |
AE9 | Bezeichnung | =WENN(AE3=““;““;WENN(AE5=““;TEXT(AE3;“TTTT“);AE5)) |
AE14 | Tag | =WENN(B2>29;30;““) |
AF3 | Datum | =WENN(B2>30;WENN(AF14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AF14);““);““) |
AF9 | Bezeichnung | =WENN(AF3=““;““;WENN(AF5=““;TEXT(AF3;“TTTT“);AF5)) |
AF14 | Tag | =WENN(B2>30;31;““) |
Zur Erklärung noch eine kurze Erläuterung zu den einzelnen Formeln anhand der Bezeichnung.
Jahr
Hier wird Bezug auf das Jahr, welches im Tabellenblatt "Einstellungen" festgelegt wurde genommen. Die entsprechende Zelle hat auch die Bezeichnung Geschäftsjahr
Monat
In der Zelle A8 kann der jeweilige Monat aus einer Liste unter Bezug auf den Namen Monat ausgewählt werden. Innerhalb des Tabellenblatt "Monat" kann hier jeder Monat ausgewählt werden, bei der Anlage der einzelnen Monate wird später der entsprechende Monat vorausgewählt, so dass hier im Januar der Monat Januar ausgewählt ist usw.. Wie erwähnt ist die Definition von Dropdownliste in Excel im Artikel "Formulare gestalten in Excel" beschrieben.
Tage im Monat
In der Zelle B2 wird über die Formel SVERWEIS die Anzahl der Tage im entsprechend gewählten Monat ausgegeben. Dieses kann von 28 bis 31 jeder Wert sein (Januar hat 31 Tage, Februar 28 oder 29, März 31 Tage, April 30 Tage und so weiter).
Tag
Abhängig vom Wert in der Spalte B2 werden hier entweder ein fester Wert (für die Tage 1 bis 28) oder je nach Wert in der Zelle B2 in den Zellen AD14, AE14 und AF14 die Werte 29 bis 31 ausgegeben. Wenn der Monat nicht ausreichend Tage hat, wird hier "" ausgegeben.
Datum
In der Zeile Datum werden abhängig von der Zeile 14 (entspricht Tag) das Datum aus Geschäftsjahr, Monat und Tag ausgegeben. Aus der Tabelle Monate wird zum Text des Monats der Monat als Zahl ermittelt. So ist dieses bei Januar 1 und bei Dezember 12. Um etwaige Fehler zu vermeiden wurde die Formel noch mit ISTNV erweitert. Sofern die Hilfstabellen korrekt sind, kann dieser Part auch ausgespart werden.
Die Verwendung von ISTNV im SVERWEIS ist im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche " beschrieben.
Wenn die Zeile Tag keinen Wert hat wird hier ebenfalls "" ausgegeben.
Feiertag
In der Zeile 3 wird als Feiertag die dritte Spalte aus Feiertage_Hessen ausgegeben, bzw. wenn das Datum nicht als gesetzlicher Feiertag in Hessen relevant ist der Wert 0.
In der Tabelle Feiertage wurde in der dritten Spalte eine 2 eingetragen, sofern der entsprechende Feiertag ein gesetzlicher Feiertag in Hessen ist.
Bezeichnung Feiertag
Handelt es sich beim Datum um einen Feiertag wird in der Zeile 5 die Bezeichnung des Feiertages ausgegeben. Dieses ist unabhängig davon, ob der Tag ein gesetzlicher Feiertag oder nicht ist. Denkbar ist es hier bspw. das Datum des Betriebsausfluges schon Anfang des Jahres festzulegen ;-).
Wochenende
Hier wird anhand des vorhandenen Datums festgestellt um den wievielten Wochentag es sich handelt. Sofern der Wochentag größer als 5 (beginnend mit Montag = 1 und somit nach Freitag = 5) ist wird hier eine 1 ausgegeben. Andernfalls wird hier eine 0 ausgegeben.
Hilfstabelle
In der Zeile 7 werden sowohl die Zeile 4 (gesetzlicher Feiertag = 2 sonst 0) und Wochenende (1 oder 0) addiert.
Hierdurch können Feiertage und Wochenenden voneinnander unterschieden werden.
An einen Wochenende ist der Wert = 1 und an einen Feiertag > 1.
Man könnte auch sagen, dass ein Feiertag mehr Wert als ein Wochenende hat.
Bezeichnung
Hier wird die Bezeichnung des Tages ermittelt. Handelt es sich um einen Feiertag wird der Wert aus der Zeile 5 "Bezeichnung Feiertag" genommen. Ansonsten wird über die Formel TEXT die Bezeichnung des Wochentages ermittelt.
Die Formel hat dabei das Format =TEXT(Wert;Textformat)
Wenn nun das Datum in der Zelle B3 steht kann der Wochentag über die Formel
=TEXT(B3;"TTTT") (ergibt den Wochentag in Langform bspw. Montag) oder über =TEXT(B3;"TTT") (ergibt den Wochentag in Kurzform bspw. Mo) ermittelt werden.
Für das Monatsblatt soll die Tagesbezeichnung in Langform daher TTTT genommen werden. Ferner sind die Zellen über Zelle Formatierung in der Registerkarte Ausrichtung um 90 Grad gedreht dargestellt.
Besonderheiten
Wie beschrieben wurde die Problematik eines Schaltjahres, bzw. der unterschiedlichen Monatslänge, in den Spalten AD bis AF durch eine Anpassung der Formeln in der Zeile 3 (Datum), 9 (Bezeichnung) und 14 (Tag) angepasst.
Die übrigen Formeln können entsprechend übertragen werden.
4. Bedingte Formatierung
Über die Funktion "Bedingte Formatierung" sind entsprechende Formatregeln für jede Spalte hinterlegt um Wochenende oder Feiertage besonders hervorzuheben bzw. die Tage 29 bis 31 im Bedarfsfall auszublenden (ohne Rahmen und ohne Farbhintergrund) festzulegen.
Im Standardfall sind die einzelnen Spalten wie im rot umrandeten Beispielfall formatiert. Der Hintergrund des Tages und der Tagesbezeichnung ist gelb und die einzelnen Beschäftigten haben ein entsprechende Zelle für den Urlaubstag. Sofern es sich um eine Wochenende handelt (Zeile 7 =1) wird die Spalte blau hervorgehen. Wenn es sich um einen Feiertag handeln würde (Zeile 7 >1) wäre die Hervorhebung grau)
Wenn in der Zeile 3 "Datum" kein Datum angegeben ist werden die Zellen auch ohne Formatierung (weißer Hintergrund, kein Rahmen) ausgegeben.
5. Makro zum Anlegen der einzelnen Kalendermonate
Zwischenergebnis:
Im Kalenderblatt Monat könnte nun jeder Monat einzeln ausgewählt werden und das Blatt Monat entsprechend kopiert werden und als entsprechender Monat umbenannt werden.
Wie im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben eignet sich hier der Einsatz eines Makros.
Grundlage Tabellenblatt Monat
Das Tabellenblatt Monat stellt dabei die Grundlage für alle Monate dar und enthält die Liste der einzelnen Beschäftigten:
Hier ist in der Zelle B1 (im Gegensatz zum hier beschriebenen Artikel) der Monat eingetragen.
Im Blatt Einstellungen kann dann das entsprechende Kalenderjahr eingetragen werden und über die Schaltfläche "Kalenderblätter anlegen" ein Makro zum Kopieren und Umbenennen des Tabellenblatt "Monat" gestartet werden.
Das entsprechende Makro "Kalender anlegen" hat dabei folgenden VBA Code:
Sub Kalender_anlegen()
'
' Kalender_anlegen Makro
' Sofern die Beschäftigtenliste vollständig ist, werden die einzelnen Monatsblätter kopiert
'
'
Range("B1").Select
Sheets("Monat").Visible = True
Sheets("Monat").Select
Range("B1").Select
Sheets("Monat").Copy After:=Sheets(5)
Sheets("Monat (2)").Select
Sheets("Monat (2)").Name = "Januar"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Januar"
Sheets("Januar").Select
Sheets("Januar").Copy After:=Sheets(6)
Sheets("Januar (2)").Select
Sheets("Januar (2)").Name = "Februar"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Februar"
Sheets("Februar").Select
Sheets("Februar").Copy After:=Sheets(7)
Sheets("Februar (2)").Select
Sheets("Februar (2)").Name = "März"
Range("B1").Select
ActiveCell.FormulaR1C1 = "März"
Sheets("März").Select
Sheets("März").Copy After:=Sheets(8)
Sheets("März (2)").Select
Sheets("März (2)").Name = "April"
Range("B1").Select
ActiveCell.FormulaR1C1 = "April"
Sheets("April").Select
Sheets("April").Copy After:=Sheets(9)
Sheets("April (2)").Select
Sheets("April (2)").Name = "Mai"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Mai"
Sheets("Mai").Select
Sheets("Mai").Copy After:=Sheets(10)
Sheets("Mai (2)").Select
Sheets("Mai (2)").Name = "Juni"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Juni"
Sheets("Juni").Select
Sheets("Juni").Copy After:=Sheets(11)
Sheets("Juni (2)").Select
Sheets("Juni (2)").Name = "Juli"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Juli"
Sheets("Juli").Select
Sheets("Juli").Copy After:=Sheets(12)
Sheets("Juli (2)").Select
Sheets("Juli (2)").Name = "August"
Range("B1").Select
ActiveCell.FormulaR1C1 = "August"
Sheets("August").Select
Sheets("August").Copy After:=Sheets(13)
Sheets("August (2)").Select
Sheets("August (2)").Name = "September"
Range("B1").Select
ActiveCell.FormulaR1C1 = "September"
Sheets("September").Select
Sheets("September").Copy After:=Sheets(14)
Sheets("September (2)").Select
Sheets("September (2)").Name = "Oktober"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Oktober"
Sheets("Oktober").Select
Sheets("Oktober").Copy After:=Sheets(15)
Sheets("Oktober (2)").Select
Sheets("Oktober (2)").Name = "November"
Range("B1").Select
ActiveCell.FormulaR1C1 = "November"
Sheets("November").Select
Sheets("November").Copy After:=Sheets(16)
Sheets("November (2)").Select
Sheets("November (2)").Name = "Dezember"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Dezember"
Sheets("Monat").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Anleitung").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Einstellungen").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Hierbei wird das Blatt Monat als Kopiervorlage für die Monate Januar bis Dezember genutzt und zum Abschluss das Tabellenblatt Monat ausgeblendet.
Ferner werden die Tabellenblätter "Monat", "Anleitung" und "Einstellungen" ausgeblendet.
Im Ergebnis ist damit das gesamte Jahr mit einzelnen Monatsblättern angelegt.
Danach kann die Tabelle entsprechend gespeichert werden. Hier bietet sich dann die Speicherung als XLS (oder XLSX) ohne Makro an.
Da das Tabellenblatt "Monat" einen Blattschutz hat (es sollen nur bestimmte Personen den Urlaub übertragen können), wird dieser ebenfalls mit kopiert.
Somit kann hier die tagesgenaue Planung von Urlaub beginnen. Die Grundtabelle (ohne angelegten Monate) kann auch für die Folgejahre wieder genutzt werden und sollte daher entsprechend zum Beispiel als "Vorlage_Urlaubsplaner mit Makro für Kopie der Monatsblätter.xlsm" gespeichert werden.
6. Urlaubskontingente verwalten
Sofern auch das Urlaubskonto von Beschäftigten mit in Excel verwaltet werden soll bietet sich noch eine Anpassung in der Vorlage Tabellenblatt "Monat" an.
Über die Excelfunktion ZÄHLENWENN kann am Ende eines jeden Monats gezählt werden, wieviel Urlaub genommen worden ist.
Die Funktion ZÄHLENWEN zählt gefüllte Zellen eines Bereiches, wenn diese ein bestimmtes Kriterium entsprechen. Sollen mehrere Kriterien überprüft werden, bietet sich hier die Funktion ZÄHLENWENNS an.
Im Beispiel soll jedoch nur der genommene Jahresurlaub gezählt werden.
Hierzu bekommt die Farblegende für den Jahresurlaub noch den Wert "JU".
In der Zelle AH62 wird durch die Formel =ZÄHLENWENN(B62:AF62;$AH$11) der Urlaub im entsprechenden Monat gezählt.
Soll neben dem Jahresurlaub auch der Sonderurlaub gezählt werden (sprich JU und SU bzw. Zellen AH11 und AH13 wäre die Formel wie folgt anzupassen =ZÄHLENWENNS(B62:AF62;AH11;B62:AF62;AH13) wobei dieses natürlich nicht nur in der Zelle AH62 sondern für jede beschäftigte Person zu ergänzen wäre.
Dieses kann für alle Beschäftigten im Monat erfolgen.
Nach Start des unter 5. beschriebenen Makros zur Anlage der einzelnen Monatsblätter kann nun eine Vorlage für ein Urlaubskonto je Beschäftigten angelegt werden, indem in jeden einzelnen Monat aus der Spalte AH der genommene Urlaub heraus gerechnet wird.
Da noch keine Einzelmonate angelegt worden sind bietet es sich an, ein weiteres Makro anzulegen, dass dann eine Vorlage für die Urlaubsübersicht anlegt.
Hierbei wird für die erste Beschäftigte der Urlaubsplan angelegt, so dass im Folgenden die weiteren Beschäftigten per Autoausfüllen ergänzt werden können.
Dieses ist erforderlich, da nicht ohne Vorhandener Arbeitsblätter eine Formel wie geplanter Urlaub = Januar!AH15+Februar!AH15 ... usw. angelegt werden kann.
Die elegantere Methode dürfte jedoch sein, das Monatsblatt als Vorlage mit der Summe der genommenen Urlaube (über ZÄHLEWENN) zu erstellen und ein Tabellenblatt in einer fertigen Jahresplan zu erstellen. Hier kann dann das Tabellenblatt "Urlaubskonto" von der alten in die neue Exceltabelle kopiert werden (rechte Maustaste auf die Registerkarte und Kopieren in die neue Datei).
Da die Formel auch hier relativ auf die Monate Januar bis Dezember verweist, kann diese dann als Vorlage genommen werden.
7. Nachtrag Vorlage & Copyright
Da ich vermehrt um die fertige Exceldatei angefragt worden bin habe ich die 💾 Datei als Vorlage ebenfalls online gestellt. Es ist sicherlich sinnvoll diese später als Vorlage_Urlaubsplaner.xlsm zu speichern. Nachdem das Makro ausgeführt wurde (und alle Monatsblätter angelegt worden sind) ist es sinnvoll die Datei dann als Urlaubsplaner_Jahr zu speichern. Das Blatt Feiertage ist im Gegensatz zum Blatt Monate nicht ausgeblendet, so dass die Feiertage noch auf das eigene Bundesland angepasst werden können. Die unter 6. erläuterte Variante zur Urlaubskontingenteverwaltung ist dabei allerdings noch nicht umgesetzt kann aber wie unter 6. erläutert entsprechend der lokalen Erfordernisse leicht eingefügt werden.Die Datei dient ausschliesslich der Darstellung dieses Artikels und darf nicht für eigene Veröffentlichungen verwendet werden. Sofern Sie die Datei persönlich (oder an der Arbeit) verwenden möchten habe ich nichts dagegen. Es sollte jedoch die Urheberangabe (URL zu diesen Artikel) erhalten bleiben. Selbstverständlich kann das Blatt Monat an ihre eigenen Bedürfnisse (Abteilung, Name, Abwesenheiten etc.) angepasst werden.
Eine kurze Rückmeldung, gerne in Form eines Kommentars, würde mich sehr freuen.
Da der Artikel an sich schon sehr umfangreich ist hoffe ich, dass mit der beigefügten Datei die einzelnen Formeln noch klarer werden.
Für weitere Rückfragen stehe ich gerne zur Verfügung. Hierzu ist insbesondere die Kommentarfunktion unterhalb dieses Artikels gedacht. Dieses hat auch den Vorteil, dass andere Personen mit vergleichbaren Fragen ebenfalls von einer Antwort profitieren.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Urlaubsplaner und dynamischer Kalender mit Monatsansicht (Urlaubsplaner 2020/2021/2022 in Excel)« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 12.1.2014, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=495 (Abgerufen am 3.10.2024)
3 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn