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
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
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:
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.
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:
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
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.
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
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.
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
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.
durch die bedingte Formatierung für Datumsangaben werden nun die Feiertage in Kalenderbereich farbig hervorgehoben.
Qualitätshandbuch und Qualitätsmanagement