Verwenden Sie benutzerdefinierte Regeln für die bedingte Formatierung für Datumsangaben in Excel

Durch das Hinzufügen einer bedingten Formatierung zu einer Zelle in Excel können Sie verschiedene Formatierungsoptionen anwenden, z. B. Farbe, wenn die Daten in dieser Zelle die von Ihnen festgelegten Bedingungen erfüllen.

Um die bedingte Formatierung zu vereinfachen, unterstützt Excel vordefinierte Optionen, die häufig verwendete Situationen abdecken, z.

• Termine
• Daten duplizieren
• Werte über oder unter dem Durchschnittswert in einem Zellbereich

Im Falle von Datumsangaben vereinfachen die voreingestellten Optionen die Überprüfung Ihrer Daten auf Datumsangaben, die dem aktuellen Datum nahekommen - beispielsweise gestern, morgen, letzte Woche oder nächsten Monat.

Wenn Sie jedoch nach Datumsangaben suchen möchten, die außerhalb der aufgeführten Optionen liegen, können Sie die bedingte Formatierung anpassen, indem Sie Ihre eigene Formel mit einer oder mehreren Datumsfunktionen von Excel hinzufügen.

01 von 06

Überprüfen auf Termine 30, 60 und 90 Tage nach Fälligkeit

Passen Sie die bedingte Formatierung mithilfe von Formeln an, indem Sie eine neue Regel festlegen, nach der Excel die Daten in einer Zelle auswertet.

Excel wendet die bedingte Formatierung in der Reihenfolge von oben nach unten an, wie sie in der Datei angezeigt werden Manager für bedingte Formatierungsregeln Dialogbox.

Obwohl für einige Zellen möglicherweise mehrere Regeln gelten, wird die erste Regel, die die Bedingung erfüllt, auf die Zellen angewendet.

Lesen Sie weiter unten

02 von 06

Überprüfen auf Termine 30 Tage nach Fälligkeit

Markieren Sie die Zellen C1 bis C4, um sie auszuwählen. Dies ist der Bereich, auf den wir die Regeln für die bedingte Formatierung anwenden

1. KlickenZuhause > Bedingte Formatierung > Neue Regeldie öffnenNeue Formatierungsregel Dialogbox.
2. Drücke denVerwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen Möglichkeit.
3. Geben Sie die folgende Formel in das Feld darunter einFormatwerte, bei denen dieser Wert wahr ist Option in der unteren Hälfte des Dialogfelds:= HEUTE () - C1> 30Diese Formel überprüft, ob die Datumsangaben in den Zellen C1 bis C4 mehr als 30 Tage zurückliegen
4. Drücke den Format Schaltfläche, um das Dialogfeld Zellen formatieren zu öffnen.
5. Drücke denFüllen Registerkarte, um die Optionen für die Hintergrundfüllfarbe anzuzeigen.
6. Wählen Sie eine Hintergrundfüllfarbe.
7. Drücke denSchriftart Registerkarte, um die Optionen für das Schriftformat anzuzeigen
8. Legen Sie die Schriftfarbe fest.
9. KlickenOK zweimal, um das Dialogfeld zu schließen und zum Arbeitsblatt zurückzukehren.
10. Die Hintergrundfarbe der Zellen C1 bis C4 ändert sich in die ausgewählte Füllfarbe, auch wenn sich in den Zellen keine Daten befinden.

Lesen Sie weiter unten

03 von 06

Hinzufügen einer Regel für Termine, die mehr als 60 Tage überfällig sind

Anstatt alle obigen Schritte zu wiederholen, um die nächsten beiden Regeln hinzuzufügen, verwenden wir die Option Regeln verwalten, mit der wir alle zusätzlichen Regeln auf einmal hinzufügen können.

1. Markieren Sie ggf. die Zellen C1 bis C4.
2. Klicken Zuhause > Bedingte Formatierung > Regeln verwalten die öffnen Manager für bedingte Formatierungsregeln Dialogbox.
3. Drücke den Neue Regel Option in der oberen linken Ecke des Dialogfelds
4. Drücke den Verwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen Option aus der Liste oben im Dialogfeld.
5. Geben Sie die folgende Formel in das Feld darunter ein Formatwerte, bei denen dieser Wert ist Option in der unteren Hälfte des Dialogfelds:= HEUTE () - C1> 60Diese Formel überprüft, ob die Datumsangaben in den Zellen C1 bis C4 länger als 60 Tage zurückliegen.
6. Drücke den Format Taste zum Öffnen der Zellen formatieren Dialogbox.
7. Drücke den Füllen Registerkarte, um die Optionen für die Hintergrundfüllfarbe anzuzeigen.
8. Wählen Sie eine Hintergrundfüllfarbe.
9. Klicken OK zweimal, um das Dialogfeld zu schließen und zum Fenster zurückzukehren Manager für bedingte Formatierungsregeln Dialogbox.

04 von 06

Hinzufügen einer Regel für Termine, die mehr als 90 Tage überfällig sind

Wiederholen Sie die Schritte fünf bis sieben, um eine neue Regel hinzuzufügen.

1. Für die Formel verwenden Sie:= HEUTE () - C1> 90
2. Wählen Sie eine Hintergrundfüllfarbe.
3. Legen Sie die Schriftfarbe fest.
4. KlickenOK zweimal, um das Dialogfeld zu schließen und zum Fenster zurückzukehrenManager für bedingte Formatierungsregeln Dialogbox
5. KlickenOK erneut, um dieses Dialogfeld zu schließen und zum Arbeitsblatt zurückzukehren.
6. Die Hintergrundfarbe der Zellen C1 bis C4 ändert sich in die zuletzt gewählte Füllfarbe.

Lesen Sie weiter unten

05 von 06

Testen der Bedingungsformatierungsregeln

Testen Sie die Bedingungsformatierungsregeln in den Zellen C1 bis C4, indem Sie die folgenden Daten eingeben:

• Das aktuelle Datum: Die Zelle sollte sich auf den weißen Standardhintergrund mit schwarzem Text ändern, da keine der Regeln für die bedingte Formatierung angewendet wird.
• 40 Tage vor dem aktuellen Datum - Die Zelle sollte sich auf einen hellgrünen Hintergrund mit weißem Text ändern. Die Formel = HEUTE () - 40 wird das Datum 40 Tage vor dem aktuellen Datum eingeben.
• 70 Tage vor dem aktuellen Datum - Die Zelle sollte einen gelben Hintergrund mit weißem Text haben.
• 100 Tage vor dem aktuellen Datum - Die Zelle sollte sich in einen dunkelroten Hintergrund mit weißem Text ändern.

06 von 06

Alternative bedingte Formatierungsregeln

Wenn in Ihrem Arbeitsblatt bereits das aktuelle Datum angezeigt wird (und viele Arbeitsblätter dies tun), verwendet eine alternative Formel einen Zellbezug auf die Zelle, in der das aktuelle Datum angezeigt wird, anstatt die TODAY-Funktion zu verwenden.

Wenn das Datum beispielsweise in Zelle B4 liegt, kann die als Regel eingegebene Formel zum bedingten Formatieren von Datumsangaben, die mehr als 30 Tage überfällig sind, lauten:

= $ B $ 4> 30

Die Dollarzeichen ($) um die Zellenreferenz B4 verhindern, dass sich die Zellenreferenz ändert, wenn die Regel für die bedingte Formatierung in andere Zellen des Arbeitsblatts kopiert wird.

Die Dollarzeichen bilden einen sogenannten absoluten Zellenbezug.

Wenn die Dollarzeichen nicht angegeben werden und die Regel für die bedingte Formatierung kopiert wird, werden in der Zielzelle oder den Zielzellen höchstwahrscheinlich a angezeigt #REF! Fehlermeldung.