Tutorial zum eigenen Team Urlaubsplaner in Excel
Die Urlaubsorganisation in Unternehmen ist immer wieder ein herausforderndes Thema und oftmals nicht transparent bzw. nicht leicht zu organisieren. Excel ist hierfür besonders gut geeignet, da sich hier Detailinformationen gebündelt darstellen lassen und sich im Folgenden aus unserer Erfahrung eine praxisnahe Anwendung realisieren lässt.
Vorlagen von Urlaubsplanern für Teams in Excel
Es gibt einige Vorlagen, die Du im Internet zu Urlaubskalendern in Excel finden kannst, diese sind allerdings häufig statisch und lassen sich nur bedingt anpassen. Mit unserem Beispiel für eine smarte Urlaubsplanung für Teams findest Du eine individuelle Lösung, die einfach aufzubauen ist, Detailinformationen enthält und transparent ist. In unserem Beispiel haben wir die Urlaubsplanung für das Jahr 2017 gewählt.
Legende
Zunächst kannst Du die Detailinformationen in Form einer Legende, wie in der Abbildung dargestellt, in den obigen Zellen hinterlegen. Hier sind dann u.a. in Spalte A die unterschiedlichen „Abwesenheitstage“ aufgeführt. In unserem Beispiel haben wir Mitarbeiter 1 bis 3 angelegt, durch Hinzufügen weiterer Spalten lassen sich beliebig viele Mitarbeiter ergänzen. Bei den einzelnen Mitarbeitern kannst Du dann den entsprechenden Jahresurlaub in der Zeile 5 hinterlegen, in unserem Beispiel haben wir 30 Tage gewählt.
In Zeile 6 kannst Du den ggf. den vorhandenen Resturlaub aus dem Vorjahr bei den Mitarbeitern erfassen, in unserem Beispiel hat lediglich Mitarbeiter 1 einen Tag Resturlaub aus dem Vorjahr. Die Berechnung der Gleitzeittage, Resturlaubstage und Kranktage anhand von Formeln wird später erläutert. Ebenfalls lassen sich die einzelnen Arten von „Abwesenheitstagen“ in der Legende in den Spalten i bis L hinterlegen und auch farblich voneinander abgrenzen. Beim geplanten Urlaub, genehmigten Urlaub und dem Gleitzeittag lassen sich auch halbe Tage definieren, diese sind in den Spalten K und L hinterlegt.
Unserer Meinung nach ist es sinnvoll, den obigen Bereich bis Zeile 8 zu fixieren, damit die Überschriften sichtbar bleiben, auch wenn Du in der Tabelle herunter-scrollst. Hierzu kannst Du Sie die ersten 8 Zeilen markieren und wie in der Abbildung dargestellt, über den Menüpunkt Ansicht unter „Fenster fixieren“ entsprechend fixieren.
Urlaubsplaner für Teams anlegen
Du startest nun am besten mit dem Anlegen der Tagesbezeichnung in Zelle C7 und der Datumsangabe in Zelle D7 für die Jahresübersicht 2017.
Dann kannst Du die beiden Zellen markieren und bis zum Datum 31.12.2017 herunterziehen (rechts unten an der Ecke greifen und ziehen). Es lässt sich natürlich auch eine mehrjährige Urlaubsplanung aufbauen. In unserem Beispiel beziehen wir uns allerdings auf das Jahr 2017.
Die Kalenderwochen und die Monatsangaben lassen sich dann entsprechend mit den Daten erweitern. Hierfür können zunächst die Zellen einer Kalenderwoche verbunden werden, indem du die Zellen B10-B16 markierst und über den Button „Verbinden und Zentrieren“ miteinander verbindest.
In die Zelle (B10-16) kannst Du dann den Text „KW 01“ hinterlegen. Diese Formatierung mit der fortlaufenden Kalenderwochenbezeichnung kannst Du dann an der Zelle unten rechts mit dem Kreuz bis KW 52 nach unten ziehen und übernehmen.
Die Monate kannst Du auf die gleiche Weise anlegen, wobei Du hier beim Kopieren und Einfügen der Formatierung darauf achten solltest, dass der Februar nur 28 Tage und Juli und August 31 Tage haben.
Formatierung des Urlaubsplaners für Dein Team
Die Formatierung der Monate und Kalenderwochen kannst Du anpassen. Hierzu markiere den entsprechenden Bereich und klick mit der rechten Maustaste darauf, dann kannst Du „Zellen formatieren“ auswählen“. Hier lässt sich unter dem Reiter Ausrichtung die Beschriftung vertikal einstellen, indem Sie bei Ausrichtung „90 °“ und unter den Feldern Vertikal „Zentrieren“ und Horizontal „Zentriert“, hinterlegen.
Sie können die Zellen, wie gewünscht, auch farblich anhand des Buttons Füllfarbe unter dem Menüpunkt Start hinterlegen.
Die Datumsangabe lässt sich ebenfalls formatieren, so dass hier nur noch der Tag angegeben wird. Unter „Zellen formatieren“ im Reiter Zahlen kannst Du die Kategorie Benutzerdefiniert auswählen. Hier trägst Du dann bei Typ „TT“ ein, dadurch wird nur die Zahl 1-31 angezeigt.
Anhand der obigen Legende lassen sich nun, wenn gewünscht, die Samstage sowie Sonn- und Feiertage farblich in der Kalenderübersicht markieren, die Vorgehensweise wurde hierzu bereits erläutert. Ebenfalls kannst Du die Feiertage noch in Spalte J unter den einzelnen Tagen ergänzen.
Urlaubstage usw. per Formeln übersichtlich darstellen
Nachdem der Kalenderaufbau nun soweit steht, kannst Du die entsprechenden Formeln hinterlegen.
Die Kranktage lassen sich mit der ZÄHLENWENN Formel ermitteln. Für den Mitarbeiter 1 kann dann die Formel in Zelle E7 so angewandt werden: =ZÄHLENWENN(E9:E373;$I$6)
Der Bereich, der gezählt werden soll, ist hierbei die Zellen unter Mitarbeiter 1 (E9 bis E373) . Das Suchkriterium ist K und dementsprechend Zelle I6, da das Suchkriterium für alle Mitarbeiter gilt, ist es sinnvoll, hier einen festen Bezug durch das $ Zeichen zu setzen. Die Formel lässt sich nun durch Ziehen auch auf die anderen Mitarbeiter anwenden.
Die Gleitzeittage kannst du für den Mitarbeiter 1 ebenfalls mit der ZÄHLENWENN-Formel ermitteln: =ZÄHLENWENN(E9:E373;$I$5)+ZÄHLENWENN(E9:E373;$L$5)*0,5
Sie ist ähnlich wie bei den Kranktagen aufgebaut, nur dass die Suchkriterien in diesem Fall Ü (Zelle I5) und ü/2 (Zelle L5) sind. Da wir in diesem Fall sowohl volle Gleitzeittage sowie halbe Gleitzeittage berücksichtigen möchten, werden beide Suchkriterien berücksichtigt und die beiden Formeln addiert. Die Suchkriterien sollten wieder einen festen Bezug haben, die Bereiche sind identisch. Bei den halben Gleitzeittagen muss die Formel noch mit *0,5 ergänzt werden, damit nur halbe Tage berechnet werden.
Die Resturlaubstage kannst Du ebenfalls anhand der ZÄHLENWENN Formel ermitteln. Grundlage ist zunächst der vollständige Urlaubsanspruch p.a. sowie der Resturlaub aus dem Vorjahr. Diese kannst Du in den Zeilen 5-6 bei jedem Mitarbeiter individuell hinterlegen.
Unser Beispiel zeigt die Berechnung für den Mitarbeiter 2: =F5+F6-ZÄHLENWENN(F9:F373;$I$4)-ZÄHLENWENN(F9:F373;$L$4)*0,5
Vom gesamten Urlaubsanspruch (Zellen F5, F6) werden, analog der Berechnung der Gleitzeittage, die genehmigten Urlaubstage mit den Suchkriterien U (Zelle I4) und u/2 (Zelle L4) abgezogen. Die Suchkriterien sollten wieder einen festen Bezug haben, die Bereiche sind identisch. Bei den halben Urlaubstagen muss die Formel noch mit *0,5 ergänzt werden, damit nur halbe Tage berechnet werden.
Fazit
Mit der vorgestellten Variante einer smarten Urlaubsplanung für Teams lässt sich in Excel eine transparente Lösung darstellen, die Du leicht organisieren kannst. Sie ist nicht statisch, sondern kann auf Deine oder Eure Bedürfnisse entsprechend angepasst werden, wie z.B.: andere Ausfalltage, z.B. Mutterschutz, Elternzeit, Kundenbesuche, Sonderurlaub, Ferientermine usw.
Die kostenlose Beispieldatei kannst Du hier downloaden: