Bedingte Formatierung für Datumsangaben

Hier erfahren Sie, wie sie in einem Projektplan, Arbeitsplan, oder Projektkalender Datumsangaben übersichtlich gestalten und farbig hervorheben. Einzelne Zeiträume werden im Kalender sichtbar. Dabei kann man auch Wochenenden und Feiertage automatisch finden und auf den ersten Blick erkennen. Ich möchte die bedingte Formatierung für Datumsangaben erklären.

Beispieldatei: Bedingte Formatierung-Arbeitsplan.xlsx

Dieses Beispiel enthält das Anfangsdatum für verschiedene geplante Arbeitsgänge. Das Anfangs- und Enddatum für die Arbeitsgänge soll ermittelt werden und danach sollen die geplante Zeiten rechts in der Tagesübersicht durch die bedingte Formatierung farbig hervorgehoben werden, ebenso die Wochenenden und die Feiertage.

Dabei soll nur an Wochentagen gearbeitet werden. Um die Anfangs- und Enddaten zu berechnen, muss man also auch die Feiertage kennen. Diese befinden sich in dem Tabellenblatt Feiertage und dieser Bereich trägt auch den Namen Feiertage, so dass dieser Name in Formeln und bei der bedingten Formatierung für Datumsangaben verwendet werden kann.

Den Bereichsnamen für die Feiertage vergeben

Das ist die Liste mit den Feiertagen des Jahres. Sie bekommt hier einen Namen zugewiesen, damit man in den Formeln leicht auf diese Liste zugreifen kann.

Wechseln Sie auf das Tabellenblatt Feiertage und markieren Sie die Zellen, die die Feiertage enthalten.
Klicken Sie in das Namensfeld und tragen Sie dort den Namen Feiertage ein, dann Enter drücken.
Nun ist der Name diesem Bereich zugewiesen und kann in Formeln verwendet werden.

Der Kalenderbereich

In dieser Liste der Kalendertage sollen Arbeitstage, Wochenenden und Feiertage einen farbigen Hintergrund erhalten.
Der Kalenderbereich, auf den die bedingte Formatierung angewendet werden soll

Der Bereich von F2 bis CR15 enthält Datumsangaben der entsprechenden Tage, von denen aus Platzgründen nur der Tag durch Formatierung angezeigt wird. Das komplette Datum ist aber Inhalt der Zelle, das kann man in der Bearbeitungsleiste sehen, wenn eine dieser Zellen markiert ist.

Folgende Formatierung wurde auf die Datumsangaben angewendet:

Das Dialogfeld Zellen formatiern. Der angezeigte Formatcode zeigt von dem enthaltenen Datum nur den Tag zweistellig an.
Die benutzerdefinierte Formatierung „TT“ sorgt dafür, dass von den in den Zellen enthaltenen Datumsangaben nur der Tag als zweistellige Zahl angezeigt wird.

Enddatum des ersten Arbeitsgangs berechnen

Wir benutzen die Funktion Arbeitstag, um die benötigte Zeit zum Ausgangsdatum hinzu zu zählen. Diese Funktion berücksichtigt auch Wochenenden und freie Tage.

Der Funktionsassistent zeigt die Funktion Arbeitstag an.
Mit Hilfe der Funltion Arbeitstag wird der letzte Tag des Arbeitspaketes ermittelt

Das Ausgangsdatum steht in der Zelle B2. Die Tage stehen in der Zelle C2, ich subtrahiere davon 1, weil sonst der auf den letzten Arbeitstag folgende Tag als Ergebnis angezeigt werden würde.

Die freien Tage befinden sich in dem Zellbereich, der den Namen Feiertage trägt und werden so berücksichtigt.

Anfangsdatum des folgenden Arbeitsgangs berechnen

In der Zelle B2 wird die Funktion Arbeitstag verwendet:

Hier wird mit der Funktion Arbeitstag der erste Arbeitstag des neuen Arbeitsschrittes berechnet.
Hier wird mit Hilfe der Funktion Arbeitstag das Anfangsdatum für das nächste Arbeitspaket berechnet. Dadurch werden Wochenenden und Feiertage berücksichtigt.

Das Ausgangsdatum steht in D2, zu diesem Tag soll ein Arbeitstag hinzugerechnet werden. Durch Angabe des Bereichs Feiertag bei den freien Tagen werden eventuelle Feiertage berücksichtigt und tatsächlich der nächste Arbeitstag ermittelt.

Die Formeln in den Zellen D2 und B3 können nach unten gezogen und kopiert werden.

Die Arbeitstage im Kalenderbereich farbig hervorheben

Auch bei der bedingten Formatierung für Datumsangaben können teilweise die in Excel enthaltenen vordefinierten Regeln für die bedingte Formatierung genutzt werden.

Markieren Sie den Bereich von F2 bis CR15.

Hier kann auch eine vordefinierte Regel zum Einsatz kommen: 
Klicken Sie im Register Start auf Bedingte Formatierung/Regeln zum Hervorheben von Zellen/Zwischen

Erstellung der bedingten Formatierung für das Anzeigen der Arbeitstage.
Diese bedingte Formatiertierung hebt die Datumsangaben farbig hervor, die sich zwischen dem Anfang- und Enddatum des Arbeitspakets befinden.

Die Zellen, deren enthaltene Datumsangaben zwischen den Anfangs- und Enddaten der Arbeitsgänge liegen, werden hier formatiert.

Dabei muss beachtet werden, dass der Bezug auf Spalte B und D absolut sein muss, deshalb muss die Angabe =$B2 und =$D2 lauten.

Anzeige der Arbeitstage im Kalenderbereich
Hier sieht man das Ergebnis. Die Tage, an denen eine bestimmte Tätigkeit durchgeführt wird, sind farbig hervorgehoben. Allerdings kann man hier Wochenenden und Feiertage noch nicht erkennen. Das folgt im nächsten Schritt.

Die Wochenenden farbig hervorheben

Dazu wird die Funktion Wochentag in Verbindung mit der bedingten Formatierung verwendet.

Markieren Sie den Bereich von F2 bis CR15.

Klicken Sie im Register Start auf Bedingte Formatierung/Neue Regel/Formel zur Ermittlung der formatierenden Zellen verwenden

Erstellen einer neuen Formatierungsegel
Dialogfeld zum Bearbeiten der Formatierungsregel. In diesem Dialogfeld gibt es keine Hilfe von Excel, alle Funktionen müssen anuell erstellt werden. Vorsicht hier auch bei der Verwendung der Pfeiltasten. Die verändern den angegebenen Zellbezug, was meist nicht erwünscht ist.

Die Funktion

=WOCHENTAG(F2;2)>5

bezieht sich auf die Zelle F2 und ermittelt den Wochentag des enthaltenen Datums als Zahl. Das zweite Argument, die 2, gibt dabei an, dass der Montag der erste Tag der Woche ist und wir das richtige Ergebnis erhalten. Es wird dann überprüft, ob die Zahl des Wochentags >5 ist. Die Wochentage 6 und 7 sind dann das Wochenende.

Der Bezug auf F2 ist relativ, Excel überträgt die Funktion dann auch auf die anderen Zellen des markierten Bereichs.

Klicken Sie dann auf die Schaltfläche Formatieren und wählen Sie eine Füllung für die zu formatierenden Zellen aus. Am Ende bestätigen Sie alles mit OK, nun sollten die Wochenenden farbig hervorgehoben sein.

Anzeige der Wochenenden im Kalenderbereich.
Hier sind nun auch die Samstage und Sonntage hervorgehoben, hier in blau.

Die Feiertage farbig hervorheben

Um die Feiertage mit Hilfe der bedingten Formatierung für Datumsangaben hervorzuheben, muss für jede Zelle im Kalenderbereich überprüft werden, ob das enthaltene Datum in der Liste der Feiertage vorhanden ist. Ich benutze dafür die Funktion ZÄHLENWENN. Falls es enthalten ist, ist das Ergebnis 1, falls nicht ist das Ergebnis 0.

Markieren Sie den Bereich von F2 bis CR15.

Klicken Sie im Register Start auf Bedingte Formatierung/Neue Regel/Formel zur Ermittlung der formatierenden Zellen verwenden

Neue Formatierungsregel zur Darstellung der Feiertage
Mit der Formel werden die Zellen ermittelt, die einen Feiertag enthalten.

Die Funktion

=ZÄHLENWENN(Feiertage;F2)=1

Klicken Sie auf Formatieren, um dann die gewünschte Formatierung für die zutreffenden Zellen auszuwählen und bestätigen Sie alles mit OK.

Bezieht sich auf den Bereich Feiertage, F2 ist das Suchkriterium. Es wird also gezählt, wie oft der Wert aus der Zelle F2 im Bereich Feiertage vorkommt. Dann wird überprüft, ob das Ergebnis = 1 ist.

Kalenderbereich mit hervorgehobenen Arbeitstage, Wochenenden und Feiertagen
Die Feiertage werden nun mit rotem Hintergrund dargestellt. Der Bezug erfolgt dabei auf die Liste „Feiertage“

durch die bedingte Formatierung für Datumsangaben werden nun die Feiertage in Kalenderbereich farbig hervorgehoben.

Qualitätshandbuch und Qualitätsmanagement

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht.