In diesem Artikel lernst du, eine Excel Kalender Vorlage zu erstellen, um dein Jahr perfekt zu planen. Darüber hinaus kannst du noch zwei weitere kostenlos downloaden.

Also habe ich für dich drei Kalender Vorlagen, die dich in deiner täglichen Arbeit unterstützen und dir bei der Planung helfen.

Dazu gehören einmal eine Jahresübersicht, die wir gleich gemeinsam erstellen werden.

Ein Schichtkalender, um deine Schichtarbeit oder die deiner Mitarbeiter für das gesamte Jahr zu planen.

Und ein Jahreskalender der für jedes Monat ein eigens Arbeitsblatt enthält.

Die Vorlagen kannst du dir hier herunterladen: Excel Kalender Vorlage

Merkmale und Nutzen der Excel Kalender Vorlagen

Bevor wir die Vorlage erstellen, möchte ich dir kurz zeigen, welche Merkmale die Kalender haben und welchen Nutzen sie dir bringen.

So kannst du gleich vorab sehen, ob die Kalender etwas für dich sind und ob du sie einsetzen möchtest.

Der Schichtkalender als Excel Kalender Vorlage

Die erste Vorlage, die ich für dich habe, ist ein Schichtarbeit-Kalender. Mit welchem du unterschiedliche Schichten farblich darstellen kannst.

Die Merkmale:

  • Der Kalender wird automatisch erstellt, du musst nur die Jahreszahl ändern.
  • Schaltjahre werden automatisch berücksichtigt.
  • Wenig Eingabeaufwand, lediglich das Start-Datum und das Schicht-Schema müssen, eingegeben werden.
  • Die Schichten werden in unterschiedlichen Farben dargestellt.
  • Übersichtliche Darstellung des gesamten Kalenderjahres.

Der Nutzen:

  • Durch die völlig automatisch erstellten Daten sparst du Zeit, da du nur die Jahreszahl ändern musst.
  • Es treten weniger Fehler auf, da die Schaltjahre automatisch berücksichtigt werden.
  • Wenn du die Eingabe einmal durchgeführt hast, musst du diese für den Rest des Jahres nicht mehr wiederholen.
  • Übersichtliche Darstellung dank automatischer Einfärbung der Schichten.

Hier kannst du dir die Vorlage herunterladen: Excel Kalender Vorlage

Die Excel Kalender Vorlage, der Jahreskalender

Als Nächstes habe ich für dich einen Jahreskalender mit den Monaten als einzelnes Arbeitsblatt.

Die Merkmale:

  • Du erhältst sowohl eine Übersicht über das gesamte Jahr als auch die einzelnen Monate im Detail
  • Auch dieser Kalender aktualisiert sich automatisch anhand der gewählten Jahreszahl.
  • Schaltjahre werden ebenfalls berücksichtigt.
  • Feiertage und Wochenenden werden farblich hervorgehoben.
  • Feiertage können anhand einer Liste frei bestimmt werden.
  • In der Monatsübersicht sind Notizen je Tag möglich.
  • Verlinkung der Jahresübersicht und der Monatsblätter

Der Nutzen:

  • Schnelle und einfache Übersicht über das gesamte Jahr möglich.
  • Zeitersparnis und Fehlervermeidung durch automatische Berechnung aller Tage auch eines Schaltjahres.
  • Vollständige Übersicht dank farblicher Markierung der Feiertage und Wochenenden.
  • Detailplanung des Jahres mögliche, dank Notizen.
  • Zeitersparnis beim Wechseln zwischen den Blättern dank Verlinkung.

Hier kannst du dir die Vorlage herunterladen: Excel Kalender Vorlage

Die Jahresübersicht als Excel Kalender Vorlage

Die Jahresübersicht bietet dir eine schnelle Möglichkeit, das gesamte Jahr im Blick zu behalten.

Die Merkmale:

  • Wichtige Termine und Geburtstage können über eine separate Liste in den Kalender eingepflegt werden.
  • Kalender aktualisiert sich automatisch anhand der gewählten Jahreszahl.
  • Schaltjahre werden ebenfalls berücksichtigt.
  • Feiertage und Wochenenden werden farblich hervorgehoben.
  • Feiertage können anhand einer Liste frei bestimmt werden.

Der Nutzen:

  • Die Terminpflege ist schnell und einfach über die separate Liste möglich.
  • Zeitersparnis und Fehlervermeidung durch automatische Berechnung aller Tage auch eines Schaltjahres.
  • Vollständige Übersicht dank farblicher Markierung der Feiertage und Wochenenden.
  • Feiertage und Urlaube können frei definiert werden.
  • Die Wochenzahlen werden automatisch berechnet.

Hier kannst du dir die Vorlage herunterladen: Excel Kalender Vorlage

So erstellst du eine Jahresübersicht als Excel Kalender Vorlage

Kommen wir nun zum wichtigsten Teil des Artikels, nämlich wie du eine Jahresübersicht in Excel erstellen kannst.

Dazu ist es wichtig zu wissen, dass jeder Monat später aus vier Spalten besteht.

Der Wochennummer, dem Tag als Zahl, dem Tag als Text und dem Hinweisfeld.

Das bedeutet, wir erstellen den ersten Monat und kopieren dann alle Spalten bis zum Monat Dezember.

So erstellst du das korrekte Datum in jeder Spalte

Basis des Kalenders ist natürlich ein Datum, welches wir auf unterschiedlich Art darstellen.

Einmal als Monatsname, als Zahl oder als Text.

Ausgehend von einer Jahreszahl soll der Kalender dann automatisch aktualisiert werden.

Das Erste was wir erstellen ist also die Jahreszahl in Zelle E1 und das Wort „JAHR“ in Zelle D1.

In Zelle B4 musst du nun folgende Formel einfügen.

=WENN(MONAT(DATUM($E$1;AUFRUNDEN(SPALTE()/4;0);ZEILE()-3))  =  AUFRUNDEN(SPALTE()/4;0);   DATUM($E$1;AUFRUNDEN(SPALTE()/4;0);ZEILE()-3);   „“)

Was macht diese Formel und wieso brauchst du sie?

Du könntest natürlich einfach mit der Funktion DATUM und zwei Zahlen den ersten Januar erstellen und dann einfach immer eins hinzuzählen, um das Datum in den anderen Zeilen  zu erzeugen.

Die oben gezeigte Formel bietet aber mehrere Vorteile.

  • Der Monat wird automatisch anhand der Spalte berechnet, somit ist die Formel in allen Spalten gleich.
  • Die Formel gibt keine Tage über dem Monatsletzten aus. Warum das so ist, zeige ich dir später.

So funktioniert die Formel

Die WENN Formel gibt nur Werte aus, wenn die „Prüfung“ WAHR ergibt.

Die Wahrheitsprüfung

MONAT(DATUM($E$1;AUFRUNDEN(SPALTE()/4;0);ZEILE()-3))  =  AUFRUNDEN(SPALTE()/4;0)

Der Linke Teil erstellt ein Datum aus der Jahreszahl in Zelle E1, der aktuellen Spalte AUFRUNDEN(SPALTE()/4;0) und der Zeile ZEILE()-3.

Von diesem Datum wird dann noch mit der Funktion MONAT, der Monat bestimmt und dann mit der aktuellen Spalte verglichen AUFRUNDEN(SPALTE()/4;0).

Aber warum so umständlich?

Ich denke, das Verwenden der Jahreszahl ist klar.

Auch das Erstellen des Tages über die Formel ZEILE()-3 ist klar. Die Funktion ZEILE() gibt die Zahl der aktuellen Zeile als Zahl aus. Von dem ziehst du 3 ab, da wir in der vierten Zeile mit der Formel beginnen. Somit erhöht sich die Zahl des Tages in jeder Zeile um eins.

Wie ich dir zu Beginn gesagt habe, besteht jeder Monat aus vier Spalten. Und mit der Formel AUFRUNDEN(SPALTE()/4;0) erstellen wir für jede Spalte den richtigen Monatswert.

In folgender Tabelle siehst du die Ergebnisse der einzelnen Spalten.

Spalte

Ergebnis der Spalten Funktion

Aufrunden Funktion

Ergebnis

A

SPALTE(A) = 1

AUFRUNDEN(0,25;0)

1

B

SPALTE(B) = 2

AUFRUNDEN(0,5;0)

1

C

SPALTE(C) = 3

AUFRUNDEN(0,75;0)

1

D

SPALTE(D) = 4

AUFRUNDEN(1;0)

1

E

SPALTE(E) = 5

AUFRUNDEN(1,25;0)

2

F

SPALTE(F) = 6

AUFRUNDEN(1,5;0)

2

G

SPALTE(G) = 7

AUFRUNDEN(1,75;0)

2

H

SPALTE(H) = 8

AUFRUNDEN(2;0)

2

I

SPALTE(I) = 9

AUFRUNDEN(2,25;0)

3

J

SPALTE(J) = 10

AUFRUNDEN(2,5;0)

3

K

SPALTE(K) = 11

AUFRUNDEN(2,75;0)

3

Die AUFRUNDEN Formel gibt somit für jede Spalte den Richtigen Monat auf Basis unserer „vier Spalten je Monat“ Bedingung aus.

Der zweite entscheidende Vorteil ist der, dass wenn die Zeile 35 erreicht ist, die Formel den 32. Jänner als Ergebnis liefern würde. Die Funktion DATUM ist aber so intelligent, dass es dann automatisch den 01. Februar ausgibt. Und der Wahrheitstest dann FALSCH ergeben würde da der Monat dann ja nicht mehr mit dem errechneten aus den Spalten übereinstimmt.

Das Ergebnis

Sollte die Bedingung nun erfüllt sein, wird mit der Formel DATUM($E$1;AUFRUNDEN(SPALTE()/4;0);ZEILE()-3) das korrekte Datum ausgegeben. Wenn nicht, wird kein Wert ausgegeben.

Die Formel kannst du nun nach unten erweitern und in folgendem Zahlenformat formatieren.

Hier ist es egal, wie weit du die Formel erweiterst, denn die Tage werden immer nur bis zum Monatsletzten berechnet.

Und da wir immer auf diese Spalte referenzieren werden auch alle anderen Daten nur bis zum letzten Tag des Monats erweitert.

Wenn du mehr zum Thema Benutzerdefiniertes Zahlenformat erfahren möchtest, dann schau dir gerne den Artikel Benutzerdefinierte Zahlenformate an.

Die richtigen Wochentage ausgeben in der Excel Kalender Vorlage

Als Nächstes kümmern wir uns um die Wochentage in Text Form.

Diese erstellen wir in Spalte C.

Also gibst du in Zelle C4 die Formel =B4 ein. Du verlinkst also einfach auf die zuvor berechneten Werte nur, dass du diesmal eine andere Formatierung wählst.

Den Monat richtig darstellen

In Zelle A3 soll später der Monat stehen.

Also gibst du auch hier =B4 ein formatierst aber die Zellen A3 bis D3 folgendermaßen:

So berechnest du die Kalenderwoche korrekt

Die Kalenderwoche soll entweder am ersten Tag der Woche oder am ersten Tag des Monats ausgegeben werden.

Diese beiden Bedingungen packen wir in eine ODER Funktion und das wiederum in eine WENN Funktion als Bedingung. Wenn die Bedingung dann erfüllt ist, dann soll die Kalenderwoche ausgegeben werden.

Darüber hinaus verpacken wir die Formel dann noch in eine WENNFEHLER Funktion.

So sieht die Formel aus:

=WENNFEHLER(WENN(ODER(WOCHENTAG(B4;11)=1;TAG(B4)=1);ISOKALENDERWOCHE(B4);““);““)

ACHTUNG: In Excel gibt es zwei Kalenderwochen Funktionen. Die „ältere“ KALENDERWOCHE und die seit Excel 2007 mögliche ISOKALENDERWOCHE Funktion. Wann immer du Dateien für den europäischen Raum erstellst, dann solltest du die Funktion ISOKALENDERWOCHE verwenden denn nur so werden sie nach unseren ISO-Standards richtig berechnet.

So übernimmst du die Feiertage und Urlaube

Was jetzt noch fehlt, ist die Ausgabe der Feier- und Urlaubstage.

Dazu verwenden wir einfach diese Formel:

=WENNFEHLER(SVERWEIS(B4;tbl_feiertage;2;FALSCH);““)   &   WENN(ODER(WENNFEHLER(SVERWEIS(B4;tbl_feiertage;2;FALSCH);““)=““;WENNFEHLER(INDEX(tbl_Urlaub[Typ];VERGLEICH(1;(B4>=tbl_Urlaub[VON])*(B4<=tbl_Urlaub[Bis]);0));““)=““);““;“;“)   &   WENNFEHLER(INDEX(tbl_Urlaub[Typ];VERGLEICH(1;(B4>=tbl_Urlaub[VON])*(B4<=tbl_Urlaub[Bis]);0));““)

Auf diesem Blog sollst du lernen, wie du Excel am effektivsten einsetzt, um möglichst viel Zeitersparnis in deiner Arbeit zu erhalten.

Und du weißt ich bin davon überzeugt, du lernst am meisten, wenn du etwas Neues anwendest. Daher habe ich folgende Herausforderung für dich.

Lies dir den Artikel „So gelingt dir jede Formel Analyse“ durch und analysiere die Formel selbst. Ich bin überzeugt, du schaffst das.

Wenn du das nicht möchtest, ist es entscheidend, dass du die Feiertags- und Urlaubsliste ident zur Übungsdatei formatierst, diese findest du hier.

Dann kannst du die Formel von oben einfach kopieren.

Wenn du trotzdem wissen möchtest, wie die Formel funktioniert, dann schreib mir eine Nachricht und ich erkläre dir die Funktionsweise sehr gerne.

Du kannst mich hier kontaktieren.

Füge also die Formel in D4 ein und kopiere sie in die darunter liegenden Zeilen.

Bedingte Formatierung für die Wochenenden erstellen

Nun hast du alle Formeln für den Kalender erstellt und wir können uns dem optischen Erscheinen widmen.

Da kommen wir leider zu einem leidigen Thema in Excel der bedingten Formatierung.

Versteh mich nicht falsch, die bedingte Formatierung in Excel ist wirklich sehr hilfreich und unverzichtbar.

Leider aber auch umständlich in der Erstellung.

Lass dir die bedingte Formatierung von mir nicht vermiesen, sie ist ein wirklich großartiges Werkzeug und du solltest sie unbedingt einsetzen.

Ihre Vorteile und wie du sie richtig einsetzt, zeige ich dir in diesem Artikel: Die Bedingte Formatierung in Excel

Nun aber zurück zum Kalender.

Jeder Monat setzt sich aus vier Spalten zusammen. Daher musst du jede Regel in jede Spalte separat einfügen, da wir innerhalb der Regeln immer auf eine bestimmte Spalte referenzieren.

Das ist deshalb so wichtig da du, wenn du die Formeln und Formatierung in andere Spalten kopierst auch die Referenzen mit verschiebst.

Du markierst also einfach alle Zellen mit Formeln in Spalte B und öffnest den Assistenten für die Bedingte Formatierung. Dort wählst du nun eine beliebige Formatierung und gibst folgende Formel ein.

Die Zelle wird also immer dann formatiert, wenn der Wochentag größer als 5 ist also an Samstagen und Sonntagen.

Das Gleiche wiederholst du jetzt für Spalte C und D, dann sollte dein Kalender bereits so aussehen:

So erstellst du die Bedingte Formatierung für die Feiertage

Genau dasselbe wiederholst du nun für die Feiertage. Lediglich die Formel ändert sich.

Denn diesmal werden nur jede Zellen formatiert, die in der Feiertagsliste ein „JA“ in der Spalte „Gesetzlich“ haben.

TIPP: Du kannst in Bedingten Formatierungen nur auf Tabellen referenzieren, wenn du diese über die Funktion INDIREKT einbindest oder der Tabelle vorher einen Namen vergibst.

Auch diese Formatierung musst du nun in die Spalten C und D einfügen.

Die Bedingte Formatierung der Urlaubstage in der Excel Kalender Vorlage

Die Jahresübersicht soll darüber hinaus auch noch die Urlaubstage hervorheben, welche du über die Tabelle tbl_Urlaub definieren kannst.

Hier ist die Formel eine andere, da die Urlaube über Start- und Enddatum definiert werden.

Die Formel für die Bedingte Formatierung sieht folgendermaßen aus:

=UND(B4>=SVERWEIS(B4;nam_Urlaub;1;WAHR);B4<=SVERWEIS(B4;nam_Urlaub;2;WAHR))

Ich habe dazu für die ganze Tabelle den Namen „nam_Urlaub“ definiert. Damit du sie in der Bedingten Formatierung verwenden kannst.

Die Formel überprüft, ob das Datum in der jeweiligen Zelle größer oder gleich ist als das Datum in der Spalte „Von“ in der Tabelle „tbl_Urlaub“.

Und zusätzlich, ob das Datum kleiner oder gleich dem Datum in der Spalte „Bis“ ist.

Also zwischen den beiden Daten liegt. Wenn das zutrifft, wird die Zelle formatiert.

Diese Formatierung musst du nun ebenfalls in Spalte B, C und D einfügen.

Du musst außerdem auf die Reihenfolge der Regeln achten. Wenn du die Formatierung für die Urlaubstage, über die der Feiertage platzierst, dann werden die Feiertage „überschrieben“.

Das folgende Bild sollte das ganz gut erklären.

Auch dieses Thema behandle ich ausführlich im Artikel zur Bedingten Formatierung.

Fast geschafft. Jetzt fehlen nur noch die Rahmen um den Kalender und ein paar Verschönerungen.

Formatiere die Monatsnamen und die Jahreszahl nach deinen Wünschen.

Für die Rahmen müssen wir noch ein paar Bedingungen definieren.

Die richtigen Rahmen in der Excel Kalender Vorlage erstellen

Du benötigst in Summe fünf Regeln.

  1. Für einen Rahmen am Monatsanfang
  2. Für einen Rahmen am Monatsende
  3. Eine Regel für einen Rahmen am Ende der Woche
  4. Eine für einen Rahmen links vom Monat
  5. Eine für einen Rahmen rechts vom Monat

Der Monatsanfang ist etwas umständlich zu erstellen, das habe ich schon einmal im Artikel „Nie wieder zu wenig Zeit: Excel DATUM und UHRZEIT Funktionen einfach erklärt näher erklärt.

Hier musst du das Ende des vorherigen Monats bestimmen und dann einen Tag hinzuzählen, diesen Wert vergleichst du dann mit dem Datum in der jeweiligen Zelle.

Die Regel für den Rahmen am Monatsende ist da schon einfacher.

Hier überprüft die Formel einfach, ob das Datum in der jeweiligen Zelle dem Monatsende der Zelle entspricht.

Auch die Regel für das Ende der Woche ist schnell erstellt.

Die Formel überprüft, ob der Wochentag dem siebenten Tag entspricht, was der Sonntag wäre.

Die ersten drei Regeln musst du in den Spalten A bis D einfügen.

Die Regel für den Rahmen links nur in Spalte A.

Und die für den Rahmen rechts nur in Spalte D.

Beide Formeln für den Rahmen links und rechts überprüfen, ob in der Zelle in Spalte B ein Wert enthalten ist und führen die Formatierung nur dann aus, wenn die Zelle nicht leer ist.

Bei der Bedingten Formatierung ist es immer einfacher, die Formeln in einem Tabellenblatt zu erstellen und erst wenn diese funktionieren in das Dialogfeld zu kopieren.

Der Abschluss zur perfekten Excel Kalender Vorlage

Damit sind wir auch schon beinahe fertig.

Als Nächstes musst du nur noch den gerade erstellten Monat Januar kopieren, damit dir alle Monate bis Dezember zur Verfügung stehen.

Markiere dazu einfach die Zellen A3 bis D40 und ziehe dann den Ziehpunkt bis in Spalte AV.

Passe die Spaltenbreiten noch an und die Jahresübersicht als Excel Kalender Vorlage ist fertig erstellt.

Hier noch mal der Download der Vorlagen: Excel Kalender Vorlage

Fazit

Du siehst, es ist eigentlich ganz einfach seinen eigenen Kalender in Excel zu erstellen.

Dabei sind dir wie immer kaum Grenzen gesetzt.

Selbst wenn du nur Teile des Kalenders in anderen Dateien verwendest, hast du nun einen guten Überblick, wie du selbst einen erstellen kannst.

Oder du verwendest nur die Vorlagen, die du hier herunterladen kannst.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor Excel der smarte Excel Problem Löser

PS.: Wenn der Artikel hilfreich für dich war, dann teile ihn doch gerne mit anderen, denen er auch helfen könnte.

Doktor Excel

Doktor Excel

Heißt eigentlich Michael und arbeitet seit über 10 Jahren privat und beruflich mit Excel. Er hat dabei viele Zeitsparende Arbeitsweisen kennen gelernt und teilweise selbst entwickelt.

Hier findest du seine Geschichte: Wer ist dieser Doktor Excel und was hat ein Arzt mit Excel zu tun?

Du kannst ihm auch jederzeit über die Kontakseite eine Nachricht zukommen lassen.