Das hier ist Teil 2 der Serie „So erstellst du dir einen Terminplaner in Excel“.

Die anderen Teile findest du, indem du auf einen der folgenden Links klickst:

Teil 1: So erstellst du dir einen Terminplaner in Excel die Ausgangsdaten

Teil 3: So erstellst du dir einen Terminplaner in Excel die Formeln

Teil 4: So erstellst du dir einen Terminplaner in Excel das Makro und die letzten Arbeiten

In diesem Artikel lernst du, wie du mithilfe von Formeln und ganz einfachen Makros, einen Terminplan erstellen und benutzen kannst.

Im vorherigen Teil haben wir bereits die Eingabemaske und alle Arbeitsblätter erstellt, die wir für den Terminplaner brauchen.

In diesem Teil kümmern wir uns nun um die Erstellung der Arbeitsblätter für die Monate und die ersten Formeln. Bevor wir aber mit der eigentlichen Arbeit beginnen, möchte ich dir kurz noch meine Gedanken dazu erläutern.

So soll der Terminplaner später funktionieren

Der Terminplaner besteht später aus dem Arbeitsblatt für die Stammdaten, dass alle wichtigen Daten zur Verfügung stellt, der Eingabemaske, den Arbeitsblättern für die einzelnen Monate und einem Jahresblatt, dass alle Daten gesammelt enthält.

Das Jahresblatt wird jenes, indem alle Formeln zum Einsatz kommen und dass alle Prüfungen durchführt sozusagen das Gehirn des Terminplaners.

Ich stelle es mir so vor, der Nutzer gibt seinen Wunschtermin in die Eingabemaske ein.

Im nächsten Schritt wird im Jahresblatt geprüft, ob schon ein Termin eingetragen ist und wenn ja, ob im gewählten Raum noch die gewünschte Anzahl an Plätzen zur Verfügung steht.

Die geprüften Daten gibt eine Formel dann wieder in die Eingabemaske zurück und teilt dem Nutzer mit, ob der Termin möglich ist oder nicht.

Wenn der Termin nun möglich ist, soll ein Makro die Werte auslesen, sofern welche vorhanden sind und um die neuen Werte ergänzen.

Parallel sollen diese Werte auch in die Arbeitsblätter der einzelnen Monate eingetragen werden.

Der Grund, dass ich Monats und Jahresansichten erstelle ist die Berechnung und Prüfung der Eingaben, das ist in einem Arbeitsblatt für das gesamte Jahr einfacher.

Ich hoffe damit verstehst du meine Vorgehensweise etwas besser. Jetzt bleibt nur noch zu hoffen, dass ich das so in die Tat umsetzen kann.

Die Formeln sollten kein großes Problem sein, beim Makro bin ich mir da nicht sicher, da ich absolut kein VBA Spezialist bin. Aber ich freue mich sehr darauf gemeinsam mit dir zu lernen wie es funktioniert.

Nun aber zurück zu etwas leichteren Tätigkeiten.

Monatsblätter für den Terminplaner erstellen

In den Arbeitsblättern für die einzelnen Monate, ist erst mal nur Formatierungsarbeit zu erledigen.

Die Schwierigkeit hierbei ist, die Arbeitsblätter möglichst übersichtlich zu erstellen, sodass später alle Termindaten eingesehen werden können, gleichzeitig aber auch die Übersichtlichkeit des Monats gegeben ist.

Der Vorteil, wir müssen das nur einmal erstellen und dann elfmal kopieren.

Ich werde die Monatsblätter Wochenweise aufbauen. Links immer die Uhrzeiten und oben die Tage. Außerdem soll es ja möglich sein, immer zwei Termine parallel zu buchen daher sind dann pro Tag zwei Spalten erforderlich.

Die einzigen Formeln, die wir hier benötigen, sind jene die uns das richtige Datum zum richtigen Tag der Woche berechnen, alles andere wird später das Makro für uns übernehmen.

Was wir allerdings brauchen, sind Orientierungspunkte für unser Makro.

Einerseits das Datum aber auch eine Uhrzeit ist erforderlich, nur so weiß Excel später, wo der Termin einzutragen ist. Theoretisch würden allein die Zeilen reichen zur Orientierung aber auch der Nutzer möchte später schnell erkennen, wann der Termin stattfindet.

Dazu erstellst du nun eine Liste mit Uhrzeiten im 15-Minuten-Intervall in einer Spalte.

Gleich rechts daneben gibst du in jeder vierten Zelle die Zeit der vollen Stunde ein.

Das dient einer besseren Übersicht. So können die Zeilen verkleinert werden und der Nutzer sieht später nur noch die vollen Stunden, die Termine werden aber immer Viertelstunden weise eingetragen.

Und so sollte dein Arbeitsblatt nun aussehen, recht unspektakulär eigentlich.

In der Spalte mit den vollen Stunden verbindest du nun immer vier Zellen untereinander dadurch sind die Stunden auch später noch zu lesen, wenn die Höhe der Zeilen verkleinert wird.

Formeln für das Datum im Monatsblatt

Im nächsten Schritt erstellst du das Datum für die einzelnen Tage.

Wenn du ein regelmäßiger Leser meines Blogs bist, wirst du diese bereits kennen. Ich habe sie nämlich bereits in einem anderen Artikel verwendet und auch deren Funktion erklärt, daher gehe ich jetzt nicht noch mal im Detail darauf ein, wie sie aufgebaut werden muss.

Lies am besten selbst nach. Den Artikel dazu findest du hier: Mit der Excel Kalender Vorlage das ganze Jahr organisieren

Diese Formeln erstellst du nun in jeder zweiten Spalte, da ja täglich bis zu zwei Termine parallel stattfinden können.

Dabei referenzierst du auf die Zelle B2 welche das Ausgangsdatum enthält.

Die Zelle, über der mit dem Datum referenzierst, du auf die Zelle darunter, dass in beiden nun derselbe Wert steht.

Das hat den Hintergrund, dass ich über dem Datum auch den Tag anzeigen lassen möchte.

Im nächsten Schritt bestimmst du nun mit dem Zellenformat das Aussehen des Datums.

Für die obere Zelle folgendes Format.

Und für die untere dieses.

Außerdem werden immer zwei der oberen Zellen verbunden.

In Zelle B2 fehlt nun noch das Datum. Das erzeugst du am besten über folgende Formel.

=DATUM(STAMMDATEN!C2;1;1)

Dadurch ändert sich der gesamte Kalender nach Eingabe des Jahres in den Stammdaten.

Zusätzlich kannst du die Zeilenhöhe der viertel Stunden Intervalle noch anpassen ich habe eine Höhe von 5 gewählt.

Noch etwas Formatierungsarbeit und die erste Woche ist im Groben fertig erstellt.

Das ist noch keine Augenweide, aber wir sind ja noch nicht am Ende.

Ach ja, du kannst jetzt schon mal die erste Woche kopieren und so die restlichen Wochen des Monats erstellen. Achte dabei aber auf die Formelreferenzen und auf die andere Formel für das Datum.

Schau dazu gerne noch mal im Artikel nach, dort ist sie genau beschrieben. Hier der Link zum Artikel: Mit der Excel Kalender Vorlage das ganze Jahr organisieren

Kalenderwochen ergänzen

Im nächsten Schritte möchte ich zusätzliche noch die Wochen im Kalender einfügen.

Dazu gibst du einfach die Formel =“KW “&WENN(SUMME(C4:P4)=0;““;ISOKALENDERWOCHE(MIN(C4:P4))) links neben dem ersten Tag der Woche ein. Die Formel gibt die Kalenderwoche des jeweiligen ersten Datums der Woche zurück. Das erste Datum erhältst du über die MIN-Funktion. Zuvor prüft die Formel jedoch auch, ob überhaupt Datums-Werte in der jeweiligen Woche vorhanden sind.

Achte dabei darauf, dass du die Funktion ISOKALENDEWOCHE verwendest und nicht die Funktion KALENDERWOCHE denn nur die Erste der beiden berechnet die Kalenderwochen nach europäischem Standard. Eigenartigerweise ist es genau umgekehrt auf der Microsoft Office Support Seite beschrieben.

Macht aber nichts. Du brauchst dir nur zu merken, dass die Funktion KALENDERWOCHE die Zahl der Kalenderwoche nach amerikanischem Vorbild erzeugt und die Funktion ISOKALENDEWOCHE nach europäischem.

Das Format der Monatsansicht anpassen

Nun fehlt nur noch die Formatierung, dabei sind dir keine Grenzen gesetzt.

Ich habe die viertel Stunden Intervalle ausgeblendet, um die Tage einen Rahmen gezogen und auch um die vollen Stunden.

Als Rahmenfarbe habe ich aber ein Grau gewählt anstelle des Standard Schwarz. Ich finde, das ist nicht ganz so aggressiv.

Und so sieht mein fertiges Ergebnis aus. Wie ich finde sehr ansprechend. Natürlich habe ich gleich mal versucht, ein paar Termine manuell einzutragen, um das spätere Ergebnis zu erahnen. Was mir nicht gefällt, ist die leider winzige Darstellung der Texte in den Terminen. Das ist aber leider immer ein Kompromiss zwischen Detailgrad und Übersichtlichkeit.

Ich werde es erst mal so belassen und eventuell später ändern.

Jetzt fehlt nur noch, dass du den ersten erstellten Monat in alle weiteren Blätter kopierst. Ändere dabei immer die Formel in Zelle B2 für den aktuellen Monat.

Außerdem erstelle ich ein Vorlagenblatt, in dem ich die leere Vorlage kopiere, vielleicht ist uns das später noch mal nützlich.

Damit sind die Blätter für die Monate so weit vorbereitet. Kommen wir nun zum Herzstück der gesamten Datei, dem Jahresblatt.

Jahresblatt für den Terminplaner erstellen

Im Blatt selbst ist keine großartige Formatierung erforderlich, hier geht es vorrangig um Funktion.

Ich habe mir ganz schön das Hirn zermartert, bis alles so funktioniert hat, wie es soll.

Die Idee dahinter ist die folgende.

Basis ist eine fortlaufende Uhrzeit in einer Spalte, jede Zelle wird dabei um eine viertel Stunde erhöht und reicht über ein ganzes Jahr. Das sind eine Menge Zellen, um genau zu sein 35040 für ein normales Jahr und um 97 mehr für ein Schaltjahr.

Auf Basis dieser Spalte werden sämtliche Feiertage, Urlaube und alle anderen Daten berechnet, die für die Eintragung des Termins dienen und überprüfen, ob ein Termin möglich ist oder nicht.

Das müssen wir berücksichtigen im Jahresblatt des Terminplaners

Hier gibt es einige Variablen zu berücksichtigen, bis eine Aussage getroffen werden kann, ob ein Termin möglich ist. Schaltjahre, Feiertage, andere Termine, Raumgrößen, Teilnehmern Anzahl und vieles mehr.

Wichtig hierbei ist, dass du nicht das gesamte Konstrukt auf einmal aufbaust, ich denke, das wäre nicht fehlerfrei hinzubekommen.

Arbeite besser immer in Etappen, daher habe ich mich dazu entschieden, die Bestandteile in einzelne Spalten aufzuteilen.

Damit meine ich Folgendes.

Der Aufbau der Jahresübersicht im Terminplaner

Diese Spalten sind im Jahresplaner enthalten.

  1. Uhrzeit
  2. Termin 1
  3. Termin 2
  4. Feiertag
  5. Sonstige Termine
  6. Freie Räume und Plätze
  7. innerhalb der Arbeitszeit
  8. Arbeitstag
  9. Raum der gebucht werden soll
  10. Plätze die gebucht werden sollen
  11. Freie Plätze zum gewählten Termin
  12. Sind die Plätze, die gebucht werden sollen, frei?
  13. Ist noch ein Terminslot frei?
  14. Wird der gleiche Termin gebucht wie der bereits eingetragene?
  15. Termin möglich?
  16. Neuer Gesamttermin T1
  17. Neuer Gesamttermin T2

Da es sehr mühsam ist, zu einem bestimmten Datum zu springen, erstelle ich außerdem Links zum ersten Tag des Monats und einen individuellen Link, der zum Datum führt, das du eingibst.

Jetzt wirst du dich bestimmt fragen, wozu so viele Spalten benötigt werden, lass es mich dir erklären.

Uhrzeit

In der Spalte werden die Zeiten in aufsteigender Reihenfolge angezeigt und sind die Basis aller Berechnungen.

Termin 1

Hier werden alle Termine des ersten Zeitslots angezeigt. Hier soll später das Makro die Kombination aus bestehenden Terminen und neuen einfügen.

Termin 2

Wie die Spalte für Termin 1 nur eben für den zweiten Terminslot.

Feiertag

Hier werden alle Feiertage angezeigt.

Sonstige Termine

Hier werden alle sonstigen eingetragenen Termine wie Urlaube angezeigt.

Freie Räume und Plätze

In dieser Spalte werden alle Räume und die verfügbaren Plätze ausgegeben.

Innerhalb der Arbeitszeit

Die Spalte gibt eine „JA“ aus, wenn die Zeit innerhalb der definierten Arbeitszeit liegt.

Arbeitstag

Wenn der Tag ein Arbeitstag ist, dann steht in der Zelle ein „JA“.

Raum der gebucht werden soll

Gibt den Raum aus, der gerade durch den Nutzer gebucht werden soll.

Plätze die gebucht werden sollen

Gibt die Anzahl an Teilnehmer aus, die an dem Termin teilnehmen.

Freie Plätze zum gewählten Termin

Hier wird die Anzahl der Freien Plätze zum gewählten Termin im gewünschten Raum berechnet.

Sind die Plätze, die gebucht werden sollen, frei?

Berechnet, ob die zu buchenden Plätze im Raum möglich sind.

Ist noch ein Terminslot frei?

Die Formeln in dieser Spalte berechnen, ob überhaupt noch ein Terminslot zum gebuchten Termin frei ist.

Wird der gleiche Termin gebucht wie der bereits eingetragene?

Hier wird berechnet ob der zu buchende Termin eventuell ident mit einem bestehenden ist, das ist nötig für die Funktion des hinzu Buchens zu einem Termin.

Termin möglich?

Gibt an, ob ein Termin final nach allen Anforderungen gebucht werden kann oder nicht.

Neuer Gesamttermin T1

Erstellt mittels Formel eine Kombination aus dem bestehenden Termin und dem neuen. Diese Spalte soll anschließend mit dem Makro, die Spalte „Termin 1“ überschreiben.

Neuer Gesamttermin T2

Ident zur Spalte „neuer Gesamttermin T1“ nur eben für den zweiten Termin.

Nun aber zu den einzelnen Formeln und wie diese funktionieren.

Allgemeine Hinweise für alle Formeln

Bei berechneten Zeiten wie in der Spalte „Uhrzeit“ kommt es oft zu Rundungsfehlern.

So kann es sein, dass ein berechnetes Datum nicht ident dem eingegebenen ist.

Das kann dazu führen, dass der Terminplaner nicht richtige funktioniert.

Deshalb runde ich die berechneten Zeiten auf fünf Nachkommastellen, um die Rundungsfehler wieder auszugleichen.

Achte aber darauf das du je nach Bedarf richtig rundest, willst du nur Stundengenau runden, reichen drei Nachkommastellen, um Minutengenau zu runden, müssen es schon fünf sein.

Formelerstellung für Feiertage und sonstige Termine im Terminplaner

Die Formeln in den Spalten „Feiertag“ und „Sonstige Termine“ dienen dazu, festzustellen, ob das Datum in der jeweiligen Zeile ein Arbeitstag ist oder nicht.

Das ist deshalb wichtig, da nur Termine erlaubt sind, die an einem Arbeitstag gebucht werden.

Die Formel in der Spalte „Feiertag“ sieht so aus:

=WENNFEHLER(SVERWEIS(ABRUNDEN(B5039;0);tbl_feiertage;2;FALSCH);““)

Die Formel rundet das Datum in der Spalte „Uhrzeit“ auf ganze Tage und sucht anschließend über die Funktion SVERWEIS in der Tabelle der Feiertage nach diesem Datum.

Wird es gefunden, gibt die Formel den Wert in der zweiten Spalte zurück.

Sollte der Wert nicht vorhanden sein, wird über die Funktion WENNFEHLER kein Wert ausgegeben.

Die Formel zur Abfrage der sonstigen Termine ist die folgende:

Keine Angst, die beißt nicht. 😊

Leider ist es hier nicht mehr möglich eine SVERWEIS Funktion zu benutzen, da ich das Datum in der Tabelle der zu blockenden Termine nicht aufsteigend sortieren möchte. Und da wir in der Tabelle einen Datumsbereich also ein Start und ein Enddatum eines möglichen Termins angeben.

Deshalb ist die Formel etwas aufwendiger.

Im Kern steht die SUMMENPRODUKTFormel, die überprüft, ob das Datum aus der aktuellen Zeile irgendeine Überschneidung mit dem Datum in der Tabelle hat.

Die WENN-Formel im zweiten Argument sorgt dafür, dass Leere Zellen in der Datumstabelle zu keinen falschen Werten führen.

Das dritte Argument sorgt dafür, die Zeile auszugeben, in der der Wert, auf den das Datum zutrifft, steht. Das ist wichtig für die INDEX-Funktion mit welcher die Bezeichnung des Termins ausgegeben wird.

Und darüber wieder eine WENNFEHLER-Funktion die uns den Rücken freihält.

Es ist aber nicht wichtig, ob du die Bezeichnung oder nur eine WAHR oder FALSCH Ergebnis ausgegeben wird, mit beiden können wir hier weiterrechnen.

Später für die Monatsblätter jedoch schon und so muss die Formel ohnehin erstellt werden.

Formel zur Ermittlung der Arbeitszeit

Nun müssen wir prüfen, ob die Zeit aus der aktuellen Zeile innerhalb der definierten Arbeitszeiten liegt, nur dann darf es möglich sein, einen Termin zu buchen.

Hierzu kommt folgende Formel zum Einsatz:

=WENN(AUFRUNDEN((B5040ABRUNDEN(B5040;0));5)>=INDEX(tbl_arbeitszeiten;WOCHENTAG(ABRUNDEN(B5040;0);11);2);

                  WENN(AUFRUNDEN((B5040ABRUNDEN(B5040;0));5)<INDEX(tbl_arbeitszeiten;WOCHENTAG(ABRUNDEN(B5040;0);11);3);“JA„;“NEIN„);

                 „NEIN„)

Eine einfache WENN Formel, die überprüft, ob die Zeit aus der aktuellen Zeile innerhalb oder außerhalb der in den Stammdaten definierten Arbeitszeiten liegt.

Hier sind einige wiederkehrende Elemente vorhanden.

Erstens der Teil AUFRUNDEN((B5040ABRUNDEN(B5040;0));5), der die Zeit der aktuellen Zeile ausgibt, indem vom kompletten Datum der Tag abgezogen wird. Das alles steckt dann in einer AUFRUNDEN-Funktion, um Rundungsfehler bei der Berechnung auszugleichen.

Ein weiterer wiederkehrender Teil ist INDEX-Funktion INDEX(tbl_arbeitszeiten;WOCHENTAG(ABRUNDEN(B5040;0);11);2), welche die Zeit des aktuellen Arbeitstages aus den Stammdaten ausliest und zum Vergleich bereitstellt.

Im ersten Argument der gesamten WENN-Formel wird überprüft, ob die Zeit der aktuellen Zeile größer oder gleich der Zeit des Wochentages ist.

Ist das Argument WAHR, wird im zweiten Argument zusätzlich noch überprüft, ob die Zeit kleiner ist als die „Bis-Zeit“ des jeweiligen Wochentages.

Sind beide Prüfungen WAHR, wird eine „JA“ ausgegeben, wenn nicht ein „NEIN“.

Hier erst mal stopp.

Darauf darfst du dich freuen im nächsten Teil der Terminplaner Serie

Wir haben wieder einiges geschafft.

Damit der Artikel aber nicht zu lange wird, darfst du dich im nächsten Teil auf folgendes freuen.

  • Formelerstellung zur Überprüfung der Doppelbuchung
  • Formelerstellung zur Überprüfung der maximalen Teilnehmerzahl
  • Formelerstellung zum Eintrag der geblockten Termine
  • Formelerstellung zum Vorschlag des richtigen Raumes
  • Formelerstellung zum Vorschlag eines alternativ Termins in Kombination mit dem richtigen Raum
  • Makro erstellen zum Eintragen des Termins
  • Verknüpfung der Daten in die Monatsblätter
  • Erstellen der bedingten Formatierung in den Monatsblättern
  • Abschlussarbeiten

Das erfährst du also im nächsten Teil der Serie „So erstellst du dir einen Terminplaner in Excel“.

Teil 3: So erstellst du dir einen Terminplaner in Excel die Formeln

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.