Generische Formel
=(date2-date1)>n
Zusammenfassung
Um "überfällige" Daten hervorzuheben, können Sie eine bedingte Formatierungsregel verwenden, die prüft, ob die Abweichung zwischen zwei Daten größer als eine bestimmte Anzahl von Tagen ist. In dem gezeigten Beispiel wurden drei bedingte Formatierungsregeln auf den Bereich D5: C12 mit diesen Formeln angewendet:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Hinweis: Bedingte Formatierungsregeln werden relativ zur oberen linken Zelle in der Auswahl zum Zeitpunkt der Erstellung der Regel ausgewertet, in diesem Fall D5.
Berechnung der Varianz in Tagen
Die Abweichungen in Spalte E werden berechnet, indem das ursprüngliche Datum mit dieser Formel vom aktuellen Datum abgezogen wird:
=D5-C5
Das Ergebnis ist die Differenz in Tagen zwischen dem ursprünglichen und dem aktuellen Datum. Ein positiver Unterschied stellt ein spätes Datum dar (dh einen "Ausrutscher" im Zeitplan). Eine negative Differenz zeigt an, dass das aktuelle Datum vor dem Zeitplan liegt. Dies funktioniert, da Excel-Daten Seriennummern sind.
Die in Spalte E gezeigte Varianz dient nur als Referenz in diesem Beispiel und wird von den Regeln für die bedingte Formatierung nicht verwendet. Wenn Sie jedoch Spalte E als Hilfsspalte behandeln, können Sie einfachere Regeln für die bedingte Formatierung schreiben, die die Varianz direkt verwenden.
Erläuterung
In diesem Beispiel möchten wir drei verschiedene Farben anwenden, je nachdem, wie stark das ursprüngliche Datum vom aktuellen Datum abweicht:
- Grün, wenn die Varianz weniger als 3 Tage beträgt
- Gelb, wenn die Varianz zwischen 3 und 10 Tagen liegt
- Rot, wenn die Varianz größer als 10 Tage ist
Für jede Regel berechnen wir eine Varianz, indem wir das ursprüngliche Datum vom "aktuellen" Datum abziehen (wie oben erläutert). Dann überprüfen wir das Ergebnis mit einem logischen Ausdruck. Wenn ein Ausdruck TRUE zurückgibt, wird die bedingte Formatierung ausgelöst.
Da wir drei separate Farben mit jeweils einem logischen Test wünschen, benötigen wir drei separate Regeln für die bedingte Formatierung. Der folgende Bildschirm zeigt, wie die Regeln für die Anwendung der Formatierung Grün, Gelb und Rot konfiguriert wurden. Beachten Sie, dass in den ersten beiden Regeln "Stop if true" aktiviert ist:
Regeln werden in der angegebenen Reihenfolge ausgewertet. Regel 1 prüft, ob die Varianz weniger als 3 Tage beträgt. Regel 2 prüft, ob die Abweichung weniger als 10 Tage beträgt. Regel 3 prüft, ob die Abweichung größer oder gleich 10 Tagen ist. Sowohl in Regel 1 als auch in Regel 2 ist "Stop if true" aktiviert. Wenn eine der Regeln TRUE zurückgibt, beendet Excel die Überprüfung zusätzlicher Regeln.
Ab heute n Tage überfällig
Möglicherweise möchten Sie ein Fälligkeitsdatum mit dem heutigen Datum vergleichen. Um zu testen, ob Daten ab heute mindestens n Tage überfällig sind, können Sie eine Formel wie die folgende verwenden:
=(TODAY()-date)>=n
Diese Formel gibt nur dann TRUE zurück, wenn ein Datum mindestens n Tage in der Vergangenheit liegt. Wenn ein Datum in der Zukunft liegt, ist die Differenz eine negative Zahl, sodass die Regel niemals ausgelöst wird.
Weitere Informationen zum Erstellen von Formelkriterien finden Sie unter 50+ Beispiele für Formelkriterien.
