So richten Sie die bedingte Formatierung in Excel für Datumsangaben ein

Bedingte Formatierung in Excel kann als wertvolles Dienstprogramm betrachtet werden, das dabei helfen kann, die Datenanalyse und Präsentation in der Tabelle visuell zu verbessern, indem bestimmte Formatierungen basierend auf vordefinierten Regeln oder Bedingungen angewendet werden. In diesem Beitrag erfahren Sie, wie Sie in Excel eine bedingte Formatierung für Datumsangaben einrichten können. Wir haben auch detaillierte Beispiele beigefügt, die Sie beim Lesen des Leitfadens ausprobieren können.

So richten Sie die bedingte Formatierung in Excel für Datumsangaben ein

Die bedingte Formatierung hilft nicht nur dabei, die Zellen mithilfe von Farbcodes hervorzuheben, sondern formatiert auch die Zeilen, Spalten und Zellen, die aus Datumsangaben bestehen, auf eine Weise, die als visuell aussagekräftig und ansprechend angesehen werden kann.

1. Vordefiniert
2. Benutzerdefinierte
3. Erstellen Sie eine benutzerdefinierte Formatierungsregel
4. Feiertage in Excel hervorheben
5. Bedingte Formatierung von Datumsangaben basierend auf mehreren Bedingungen
6. Hervorhebung bevorstehender Termine und Verzögerungen

Gehen Sie die Details in Excel sorgfältig durch. Es bietet viele Optionen, von denen einige möglicherweise den Rahmen dieses Handbuchs sprengen.

1] Vordefinierte Datumsregeln für bedingte Formatierung mit Datumsangaben

Die bedingte Formatierung mit Datumsangaben kann beim Umgang mit Datensätzen, die datumsbezogene Informationen enthalten, von Vorteil sein. Excel bietet fast zehn verschiedene Optionen zum Formatieren von Zellen basierend auf Datumsangaben, wie unten dargestellt:

• Um eine Zelle mit dem Datum als Datentyp zu formatieren, klicken Sie auf Startseite > Bedingte Formatierung > Markieren Sie Zellenregeln >Ein auftretendes Datum

• Die bedingten Formatierungsoptionen können aus den Dropdown-Menüs im nächsten Fenster ausgewählt werden. Im ersten Dropdown-Menü können wir den Tagesbereich auswählen und im zweiten Dropdown können die Formatierungsoptionen ausgewählt werden.

• Klicken Sie auf OK, um die ausgewählten Formatierungsregeln anzuwenden.

2] Benutzerdefinierte Datumsregeln für bedingte Formatierung mit Datumsangaben

Neben einer Reihe vordefinierter Regeln bietet Excel auch die Möglichkeit, diese Regeln entsprechend den Benutzeranforderungen anzupassen. Es gibt mehrere Möglichkeiten, die Anpassungen vorzunehmen, wie unten dargestellt:

Benutzerdefiniertes Format verwenden

1. Öffnen Sie das Formatfenster A Date Occuring, indem Sie die oben genannten Schritte ausführen.
2. Nachdem Sie den Tagesbereich ausgewählt haben, wählen Sie im Dropdown-Menü mit den Formatierungsoptionen die Option Benutzerdefiniertes Format aus.
3. DasFenster „Zellen formatieren“ würde aus dem oben Gesagten erscheinen, in dem wir die Farbe, den Rahmen, die Schriftart und die Effekte unserer Zellen auswählen können Wählen Sie Ihre Auswahl aus und klicken Sie dann auf OK, um die Änderungen zu übernehmen.

Erstellen Sie neue Regeln

Bei dieser Methode wird eine völlig neue Regel für die bedingte Formatierung erstellt, im Gegensatz zur oben genannten Methode, bei der wir aus einigen vordefinierten Regeln auswählen und nur ein benutzerdefiniertes Format erstellen können. Das Erstellen einer neuen Regel umfasst Folgendes:

• Auswahl der Zellen, auf die die Formatierung angewendet werden soll.
• Klicken Sie auf der Registerkarte Startseite auf Bedingte Formatierung > Neue Regel, wodurch das Fenster Neue Formatierungsregel geöffnet werden sollte

• In diesem Fenster können wir aus den verfügbaren Regeltypen auswählen und anschließend die Regelbeschreibung bearbeiten, um die Kriterien für bedingte Bedingungen festzulegen Formatierung.
• Sobald der Vorgang abgeschlossen ist, kann in der unteren rechten Ecke des Fensters auf die Schaltfläche Formatgeklickt werden, um die Formatierung für die besagte Regel auszuwählen.
• Im sich öffnenden Fenster Zellen formatieren können wir Schriftart, Rahmen, Zellenfarbe, Effekte usw. auswählen Wählen Sie die gewünschte Option aus und klicken Sie in diesem Fenster einmal auf OK. Anschließend wird das Fenster Neue Formatierungsregel angezeigt, in dem die Änderungen übernommen werden.

3] So erstellen Sie eine benutzerdefinierte Formatierungsregel mit Datumsangaben

Im obigen Beispiel haben wir die Schritte zum Erstellen einer neuen Regel für die bedingte Formatierung basierend auf bereits bereitgestellten Kriterien durchlaufen. Excel ermöglicht auch die Anwendung der genannten Formatierungsregeln auf benutzerdefinierte Kriterien.

Angenommen, wir müssen die Zellen mit Datumsangaben hervorheben, die älter als 30 Tage oder 60 Tage sind. In diesem Fall kann das Gleiche auch durch die Anwendung von in Excel verfügbaren Datumsformeln und -funktionen umgesetzt werden, wie unten dargestellt:

A] Markieren Sie aktuelle und ältere Daten

Unter Wählen Sie eine neue Regel auswählen Sie in der Option „Bedingte Formatierung“ die Option Verwenden Sie eine Formel, um zu bestimmen, welche Zu formatierende Zelle, geben Sie Folgendes ein:

=$H4=TODAY() um den aktuellen Tag hervorzuheben

=$H4>TODAY() um zukünftige Tage hervorzuheben

=$H4<TODAY() um die vorherigen Tage hervorzuheben

=$H4<=(TODAY()-30) um Daten hervorzuheben, die älter als 30 Tage sind.

=$H4<=(TODAY()-60) um Daten hervorzuheben, die älter als 60 Tage sind.

Die obige Formel überprüft den ausgewählten Datumsbereich auf das aktuelle Datum und ändert, sobald die Übereinstimmung gefunden wurde, die Hintergrundfarbe in Lila, wie in der Option „Format“ ausgewählt. Das neben H verwendete $ bedeutet, dass die Spalte konstant bleiben würde, aber Die Zeilen wären variabel.

• Sobald die Regelbeschreibung erwähnt ist, können Schriftart, Farben und Effekte durch Klicken auf die Schaltfläche „Format“ ausgewählt werden.
• Klicken Sie im Fenster „Zellen formatieren“ auf OK und anschließend im Fenster „Neue Regel“, damit die Änderungen wirksam werden.< /span>

B] Hervorhebung basierend auf einem benutzerdefinierten Datumsbereich

Ähnliche Methoden können zum Formatieren der von Benutzern angegebenen Datumsbereiche angewendet werden. Wenn wir beispielsweise Daten hervorheben müssen, die älter als 30 Tage, aber weniger als 45 Tage sind, kann die Funktion TODAY() in Kombination mit einem logischen Operator wie AND verwendet werden, um die Kriterien mit zwei Bedingungen anzugeben:

• Wählen Sie die Zellen aus, auf die die Formatierung angewendet werden soll, und öffnen Sie das Fenster „Neue Regel“.
• Geben Sie im Feld Regelbeschreibung bearbeiten die folgende Formel ein:

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

Die obige Formel verwendet den Operator AND, um die Daten gelb hervorzuheben, die älter als 30 Tage, aber weniger als 45 Tage sind gesamte Auswahl, beginnend mit Zellen-Nr. H2.

C] Wochenenden hervorheben

Im gleichen Bereich können mithilfe der WEEKDAY()-Formel auch die Daten hervorgehoben werden, die auf Wochenenden fallen:

• Wählen Sie die Zellen aus, auf die die Formatierung angewendet werden soll, und öffnen Sie das Fenster „Neue Regel“.
• Geben Sie im Feld Regelbeschreibung bearbeiten die folgende Formel ein:

=WEEKDAY($H2, 2)>5

Die-Syntax der Wochentagsformel kann als WEEKDAY(serial_number, [return_type]) dargestellt werden, wobei serial_number die Zellennummer des darstellt Datum, das wir überprüfen möchten (in diesem Fall SA1).

Der Parameter return_types stellt den Typ für die Woche dar, die von Montag (1) bis Sonntag (7) beginnt, unter Berücksichtigung des Starttags der Woche als Montag (dargestellt durch 1) und der letzte Tag der Woche als Sonntag (dargestellt durch 7).

return_types sind jedoch in der Formel optional und werden in dritten geschweiften Klammern dargestellt. >5 wird verwendet, um Samstag (6) und Sonntag (7) für den angegebenen Rückgabetyp hervorzuheben.

D] Hervorheben von Zeilen basierend auf einem bestimmten Datum

Diese Option kann beim Umgang mit großen Datenmengen nützlich sein, bei denen ein bestimmtes Datum hervorgehoben werden muss. Dasselbe kann erreicht werden, indem man den numerischen Wert des Datums ermittelt oder die Funktion DATEVALUE() verwendet. Der numerische Wert muss zunächst ermittelt werden, um die bedingte Formatierung für ein bestimmtes Datum anzuwenden, da Excel Datumsangaben dauerhaft als fortlaufende Seriennummern speichert. Um den numerischen Wert eines bestimmten Datums zu ermitteln,

• Klicken Sie mit der rechten Maustaste auf die Zelle mit dem Datum, klicken Sie auf Zellen formatieren >Nummer und wählen Sie dann Allgemein. In der Zelle wird ein 5-stelliger numerischer Wert angezeigt, der zur späteren Bezugnahme notiert werden kann.

• Klicken Sie auf „Abbrechen“, sobald die Zahl notiert ist, da wir das Datumsformat für die Zelle nicht ändern müssen.
• Klicken Sie auf Bedingte Formatierung>Neue Regel> Neue Regel auswählen> Verwenden Sie eine Formel, um zu bestimmen, welche Zelle formatiert werden soll und geben Sie die folgende Formel ein:

=$G5=45249

• Mit der Funktion DATEVALUE() kann das Gleiche auch durch Eingabe der unten genannten Formel erfolgen

=$G5=DATEVALUE("11/19/2023")

Das neben C verwendete $ bedeutet, dass die Spalte konstant bleibt, die Zeilen jedoch variabel sind.

4] Feiertage in Excel hervorheben

Da Feiertage je nach Bevölkerungsgruppe variieren können, verfügt Excel über keine integrierten Funktionen zum Hervorheben von Feiertagen. Um die Feiertage zu identifizieren, müssen wir daher die Daten in einer anderen Spalte erfassen und die Formel countif anwenden, um das Vorkommen der Feiertage zu überprüfen im Datenblatt.

Beispielsweise sind in Spalte D einige Daten und in Spalte G eine Liste von Feiertagen aufgeführt. Durch bedingte Formatierung können wir die Feiertage in Spalte D, sofern vorhanden, in ROT hervorheben, indem wir sie mit den in Spalte G genannten Feiertagsdaten vergleichen Verwenden Sie die unten aufgeführten Schritte:

• Öffnen Sie das Dialogfeld Neue Regel und wählen Sie Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen < /span>Regeltyp auswählen.unter
• Geben Sie die Formel ein: =COUNTIF($G$3:$G$5,$D3)
• Klicken Sie auf Formatieren > Füllen Sie und wählen Sie die Farbe ROT aus der Palette aus und klicken Sie auf OK< /span>.

Der countif() prüft das Auftreten der in Zelle G3 – G5 genannten Daten mit den in Spalte D genannten Daten ab Zelle D3 .

5] Bedingte Formatierung von Datumsangaben basierend auf mehreren Bedingungen

Nehmen wir ein Arbeitsblatt an, in dem wir Beschwerden zu verschiedenen Haushaltsgegenständen vorbringen. Auf dem Blatt sind die Daten aufgeführt, an denen die Beschwerden eingereicht wurden, sowie gegebenenfalls die Fälligkeits- und Abschlussdaten.

Mithilfe der bedingten Formatierung markieren wir die Beschwerden WEISS für diejenigen, die GESCHLOSSEN wurden, ROT für diejenigen, deren Fälligkeitsdatum überschritten, aber noch nicht abgeschlossen ist, und BLAU für diejenigen, die das Fälligkeitsdatum noch erreicht haben.

Der Prozess würde die Erstellung zweier bedingter Formatierungsregeln für dieselben Daten umfassen, eine für diejenigen, deren Fälligkeitsdatum noch nicht erreicht ist, und eine andere für diejenigen, deren Fälligkeitsdatum überschritten ist. Um dies zu tun,

• Öffnen Sie das Fenster „Neue Regel für bedingte Formatierung“ und wählen Sie Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen unter Wählen Sie einen Regeltyp aus.
• Geben Sie die unten stehende Formel ein

=AND($I10="",$H10>$K$6)

• Klicken Sie auf Formatieren, wählen Sie die blaue Farbe aus der Palette aus und klicken Sie auf OK.
• Erstellen Sie eine weitere Neue Regel mit demselben Regeltyp und geben Sie die unten genannte Formel ein

=AND($I10="",$H10<$K$6)

• Klicken Sie auf Format, wählen Sie die Farbe ROT aus der Palette aus und klicken Sie auf OK.

In dieser Formel prüfen wir zunächst, ob die Spalte „Datum des Abschlusses“ leer ist oder nicht, um festzustellen, ob die Beschwerde noch offen ist UND und prüfen dann ob das Fälligkeitsdatum größer oder kleiner als das aktuelle Datum ist, um festzustellen, ob das Fälligkeitsdatum näher rückt ($H10>$K$6) oder überschritten wird ($ H10<$K$6). Das Datum in Zelle K6 stellt das aktuelle Datum dar.

6] Hervorhebung bevorstehender Termine und Verzögerungen

Um zu verstehen, wie wir Verzögerungen in Bezug auf Daten in einer Excel-Tabelle hervorheben können, betrachten wir ein Beispiel, in dem wir eine Liste von Aktien mit ihren Namen und Ablaufdaten haben. Wenn wir durch bedingte Formatierung diejenigen, deren Ablaufdatum ansteht, in GRÜN und diejenigen, die bereits abgelaufen sind, in ROT markieren müssen,

• Wählen Sie den Datenbereich aus und öffnen Sie das Dialogfeld Neue Regel.
• Wählen Sie Nur Zellen formatieren, die enthalten unter Regeltyp auswählen
• Wählen Sie unter Regelbeschreibung bearbeiten Zellenwert aus. Größer als, =Now()+30 in den jeweiligen Dropdowns.
• Klicken Sie auf Formatieren, wählen Sie GRÜN aus der Farbpalette aus, und klicken Sie auf OK.

• Öffnen Sie erneut eine Neue Regel und wählen Sie denselben Regeltyp< aus a i=4>wie oben.
• Wählen Sie unter Regelbeschreibung bearbeiten Zellenwert aus. Kleiner als, =Jetzt() in den jeweiligen Dropdowns.
• Klicken Sie auf Format und wählen Sie ROT aus der Farbe aus Palette und klicken Sie auf OK.

Abschluss

Ziel dieses Tutorials ist es, umfassend die Optionen und Funktionen zu behandeln, die zum Anwenden der bedingten Formatierung in Excel-Arbeitsblättern basierend auf Datumsangaben verwendet werden können. Es kann helfen zu verstehen, wie Formatierungsregeln angewendet werden, um die Datenvisualisierung und -analyse zu verbessern. Die Beherrschung der bedingten Formatierung kann auch dabei helfen, Trends zu erkennen, Fristen zu überprüfen und Aufgabenprioritäten festzulegen, um fundierte Entscheidungen zu treffen.

Wie erstelle ich in Excel eine bedingte Formatierung basierend auf Datumsangaben?

Wählen Sie zunächst Ihre Datumszellen aus, um eine bedingte Formatierung in Excel basierend auf Datumsangaben zu erstellen. Klicken Sie auf „Startseite“ > „Bedingte Formatierung“ > ‚Neue Regel‘. Wählen Sie „Zellen formatieren, die enthalten“, wählen Sie unter der Regel „Auftretende Daten“ aus, wählen Sie die spezifischen Datumskriterien aus und legen Sie dann Ihr bevorzugtes Format fest, indem Sie auf „Format“ klicken. Bewerben Sie sich, indem Sie auf „OK“ klicken. Dadurch werden wichtige Daten in Ihren Daten schnell hervorgehoben.

Wie formatiere ich Fälligkeitstermine in Excel bedingt?

Wählen Sie zunächst Ihre Zellen mit Datumsangaben aus, um Fälligkeitstermine in Excel bedingt zu formatieren. Klicken Sie dann auf der Registerkarte „Startseite“ auf „Bedingte Formatierung“. Wählen Sie „Neue Regel“, wählen Sie „Nur Zellen formatieren, die enthalten“, stellen Sie die Regel auf „Zellenwert“ und „Kleiner als“ ein und geben Sie Ihr Fälligkeitsdatum oder eine Formel ein. Klicken Sie auf „Formatieren“, wählen Sie Ihren Formatierungsstil aus und drücken Sie zum Übernehmen auf „OK“.