Jahreskalender, nur Arbeitstage

Einen zeilenweisen Kalender mit nur den Arbeitstagen des Jahres erstellen

  • Tragen Sie in A1 den ersten Tag des entsprechenden Jahres ein, beispielsweise 1.1.2016.
  • Erforderlichenfalls noch einmal A1 markieren, Strg, der Bereich bis zur letzten Zeile des Arbeitsblatts wird markiert.
  • Menü Start | Gruppe Bearbeiten | Füllbereich ▼ | Reihe… 
  • Als Endwert tragen Sie den letzten Tag des entsprechenden Jahres ein, hier also beispielsweise 31.12.2016:

Automatisch das ganze Jahr ausfüllen

Automatisch das ganze Jahr ausfüllen

Damit wird erst einmal jeder Tag des Jahres in Spalte A, beginnend in A1 eingetragen. Selbstverständlich auch der 29. Februar, falls es (wie 2016) ein Schaltjahr ist. Die kalendarischen Daten stehen nun als ganz normales Datum, nicht als Formel in den Zellen. Nun empfiehlt es sich, den Daten noch eine Überschrift zu geben. Noch stehen zwar alle Tage in der Liste, aber später sollen es ja nur die Arbeitstage des Jahres sein.

Idealerweise fügen Sie nun in Zeile 1 eine neue Zeile ein. Einfach ein Rechtsklick in A1 und dann die entsprechende Auswahl. Schreiben Sie nun in (die neue Zelle) A1 beispielsweise Arbeitstage 2016 hinein. - Nicht zwingend erforderlich aber ausgesprochen hilfreich und durch uns hier im Skript verwendet: Markieren Sie eine beliebige Zelle im Datenbereich und dann StrgT, um daraus ein Intelligente Tabelle zu erstellen. Wir haben der Intelligenten Tabelle dann noch den Namen Werktage_2016 gegeben.

Als nächstes ist es erforderlich, eine Liste/Tabelle der Feiertage des entsprechenden Jahres zu erstellen. Das kann auf dem gleichen oder einem anderen Blatt geschehen. Schauen Sie beispielsweise hier nach, wie Sie das recht elegant in plain Excel oder per VBA erledigen können. – Unsere Tabelle mit den Feiertagen haben wir direkt aus dem verlinkten File übernommen. Die Tabelle/Liste hat den Namen tbl_Feiertage und die Spalten sind mit den Überschriften nach den Konventionen der Intelligenten Tabelle „benamst“.

Wenn Sie nun in dem Tabellenblatt mit allen Tagen des Jahres in Zelle B1 eine Überschrift (beispielsweise) WE oder FT eingeben, wird die Liste automatisch um eine Spalte erweitert. Geben Sie nun in B2 eine Formel nach dieser Vorgehensweise ein:
=oder(wochentag(
und dann direkt A2 anklicken. Die Formel stellt sich nun so dar, wen Sie die gleichen Überschriften wie wir verwendet haben:
=oder(wochentag([@[Arbeitstage 2016]] 

Ergänzen Sie nun diese Formel um ; 2)>5;, jetzt steht dort:
=oder(wochentag([@[Arbeitstage 2016]]; 2)>5; 

Damit ist erst einmal das Wochenende „abgehakt“. Das so allein stehende Semikolon am Ende der Formel und die fehlende schließenden Klammer zeigen an, dass da noch etwas folgen wird. Klar, es soll ja auch noch berechnet werden, ob es sich bei dem Datum um einen Feiertag handelt. Hier kommt die Funktion ZÄHLENWENN() zur Anwendung. Ergänzen Sie die Formel also direkt mit zählenwenn( und danach ein Klick auf den oberen Rand der zweiten Spalte der Feiertagstabelle:

Alle kalendarischen Daten der Feiertage markieren

Alle kalendarischen Daten der Feiertage markieren

Damit wird dann automatisch der Bereich der kalendarischen Daten markiert und in die Formel übernommen. Jetzt noch ein Semikolon vor dem noch folgendem zweiten Argument. Verglichen werden soll ja mit dem jeweiligen Datum. Also Wechsel in die Jahrestabelle und ein Klick in A2. Gleich danach die schließende Klammer für diese Funktion und nun nur noch =1) und die gesamte Formel ist abgeschlossen. Folgende Formel wird nun automatisch in alle Zeilen des Datenbereichs eingetragen:
=oder(wochentag([@[Arbeitstage 2016]]; 2)>5; zählenwenn(tbl_Feiertage[FT-Datum]; [@[Arbeitstage 2016]])=1)

Layoutbedingt wird diese Formel hier vermutlich mehrzeilig dargestellt werden, es ist aber in Excel 1 Zeile. Das Ergebnis dieser Funktion ist natürlich ein Wahrheitswert, also WAHR oder FALSCH. Und da die „Behauptung“ war, dass es sich bei dem betroffenen Tag um ein Wochenende oder einen Feiertag handelt, werden die betroffenen Tage mit einem WAHR in Spalte B gekennzeichnet.

Hinweis: Diesen Stand der Dinge können Sie hier in unserem Blog herunter laden.

Nun bleibt nur noch, in B1 den Filter auf WAHR zu setzen und alle Zeilen, welche dann noch nach Zeile 1 sichtbar sind, zu löschen:

Wochenenden und Feiertage herausfiltern

Wochenenden und Feiertage herausfiltern

Wenn Sie sich jetzt einfach machen wollen, dann gehen Sie nach A2 (die Zelle ist ja auf jeden Fall sichtbar, denn es handelt sich ja um einen der Feiertage, weil es der der 1. Januar also Neujahr ist) und dann die Tastenkombination StrgShiftEnde, um alle sichtbaren Datenzellen zu markieren.

Nun ein Rechtsklick und im Kontextmenü Zeile/Spalte löschen und dort dann die zweite Möglichkeit des Untermenüs, Gesamte Blattzeile. Nun zuerst den Filter aus WE oder FT entfernen (Alle anzeigen) und danach einfach die Spalte B löschen. Sie hat ihren Dienst getan.Jetzt haben Sie ausschließlich die Tage des Jahres ohne Wochenenden und Feiertage in der Liste. Sie können die Intelligente Tabelle wieder in einen normalen Bereich umwandeln (Rechtsklick, Tabelle | In Bereich konvertieren und dann die Hintergrundfarbe auf Keine Füllung setzen und A1 wieder schwarze Schriftfarbe) oder alle so belassen. – In jedem Fall sind Sie nun fertig.

Ein Hinweis noch: Dass die Funktionsnamen hier im Skript immer klein geschrieben sind, hat seinen Grund. Denn wenn diese nach Verlassen der Zelle nicht automatisch in Großbuchstaben umgewandelt werden, dann sind sie falsch geschrieben. Das erleichtert im Falle eines Falles enorm die Fehlersuche!

Wollen Sie das Gegenstück solch einer Liste erstellen, dann setzen Sie entweder den Filter auf FALSCH oder schauen Sie einmal hier nach.

▲ nach oben …

Dieser Beitrag wurde unter Datum und Zeit, Musterlösungen, Ohne Makro/VBA, Tabelle und Zelle abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.