In einem Dienstplan, einer Urlaubsliste oder einem Kalender sollen Samstage und Sonntage auf einen Blick erkennbar sein. Die bedingte Formatierung färbt sie automatisch ein, auch wenn sich die Daten ändern.
Die Kurzlösung
- Markieren Sie die Datumswerte, zum Beispiel A2:A32.
- Start, Bedingte Formatierung, Neue Regel, „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
- Formel eingeben und über „Formatieren“ eine Füllfarbe wählen:
=UND($A2<>"";WOCHENTAG($A2;2)>5)
WOCHENTAG mit dem zweiten Argument 2 zählt Montag als 1 bis Sonntag als 7. Werte größer als 5 sind also Samstag und Sonntag. Der Teil $A2<>"" verhindert, dass leere Zellen gefärbt werden.
Beispiel
Für den Januar 2002 färbt die Regel diese Tage:
| Datum | Wochentag | markiert |
|---|---|---|
| 04.01.2002 | Freitag | |
| 05.01.2002 | Samstag | ja |
| 06.01.2002 | Sonntag | ja |
| 07.01.2002 | Montag | |
| 12.01.2002 | Samstag | ja |
| 13.01.2002 | Sonntag | ja |
Ganze Zeile färben
Soll in einer Tabelle die ganze Zeile farbig werden, markieren Sie alle Spalten, etwa A2:F32, und verwenden dieselbe Formel. Das Dollarzeichen vor dem A sorgt dafür, dass jede Zelle der Zeile das Datum in Spalte A prüft.
Steht das Datum in einer Kopfzeile, etwa in einem Monatskalender mit den Tagen nebeneinander in B1:AF1, markieren Sie B1:AF20 und setzen das Dollarzeichen vor die Zeile:
=UND(B$1<>"";WOCHENTAG(B$1;2)>5)
Feiertage mit markieren
Schreiben Sie die Feiertage in einen Bereich und geben Sie ihm im Namensfeld den Namen Feiertage. Dann prüft die Regel zusätzlich, ob das Datum in dieser Liste steht:
=UND($A2<>"";ODER(WOCHENTAG($A2;2)>5;ZÄHLENWENN(Feiertage;$A2)>0))
Kürzer geht es mit NETTOARBEITSTAGE. Die Funktion zählt die Arbeitstage zwischen zwei Datumswerten. Für einen einzelnen Tag ergibt sie 0, wenn er ein Wochenende oder Feiertag ist:
=UND($A2<>"";NETTOARBEITSTAGE($A2;$A2;Feiertage)=0)
Wer Samstage und Sonntage in unterschiedlichen Farben möchte, legt zwei Regeln an: =WOCHENTAG($A2;2)=6 für Samstag und =WOCHENTAG($A2;2)=7 für Sonntag.
Typische Fehler
- Formel bezieht sich auf die falsche Zeile: Die Formel muss sich auf die erste Zeile der Markierung beziehen. Wer für den Bereich A1:A20 eine Formel mit A20 eingibt, lässt jede Zelle ein Datum 19 Zeilen tiefer prüfen. Dann ist schnell die ganze Liste gefärbt.
- Leere Zellen werden gefärbt: Eine leere Zelle gilt als 0, und das ist für Excel der 00.01.1900, ein Samstag. Ohne
$A2<>""färbt die Regel deshalb auch leere Zeilen. Das gilt für WOCHENTAG und für NETTOARBEITSTAGE. - WOCHENTAG ohne zweites Argument: Dann zählt Sonntag als 1 und Samstag als 7, und
>5markiert Freitag und Samstag. Geben Sie immer die 2 an. - Datum als Text: WOCHENTAG wandelt einen Text im deutschen Format wie „05.01.2002“ noch um, liest „03/11/2015“ aber als 3. November. Texte, die kein Datum ergeben, führen zu #WERT!, die Regel greift dann nicht. Echte Datumswerte sind die sichere Grundlage.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
