EXCEL: Termine Bedingte Formatierung

[DarkCava]

Hallo,

ich habe eine Terminliste unter Excel, welche einzelne Termine mithilfe von bedingten Formatierungen in verschiedenen Farben formatieren soll.

Folgende Bedingungen sollen gelten:

• Termin liegt zwischen 10 und 30 Tagen in der Zukunft (gelb)
• Termin liegt zwischen Null und 9 Tagen in der Zukunft (rot)
• Termin ist erreicht bzw. abgelaufen (grün)

Bei der Bedingung, dass der Termin grün eingefärbt werden soll, wenn er von HEUTE() erreicht bzw. abgelaufen ist besteht folgendes Problem: Nicht nur die Termine, sondern alle formatierten Spalten ohne irgendeinen Eintrag - färben sich grün. Die Spalten, wo nichts eingetragen ist, sollen aber weiß bleiben.

Wie muss die Formel für die bedingte Formatierung dann aussehen? Bisher habe für die grüne Markierung verwendet:


=($C5-HEUTE()<0)



Grüße

 

[Heretic Novalis]

abgesehen davon, dass outlook das recht gut kann, würde ich noch eine bedingung mittels "ISTLEER" einbauen (falls in der spalte immer neue daten unten angefügt werden) bzw. den bereich, auf den die formatierung angewendet werden soll begrenzen (falls er fix ist, also keine weiteren daten mehr unten dazukommen)

 

[dreamy_betty]

Die leeren Zellen werden deswegen mit der grüner Farbe hinterlegt, weil die Formel 'wahr' ist. Ich würde (ungetestet) etwas wie zum Beispiel die folgende Formel verwenden:

=UND($C4<>0;$C4-HEUTE()<0)

 

[DarkCava]

Ich habe es mit =ISTLEER(C1) versucht und bei dieser Bedingung die Farbe Weiß bestimmt. Leider funktioniert es nicht so wie es soll. Es werden auch die ersten drei Felder weiß dargestellt, obwohl Inhalt vorhanden ist.

Der Vorschlag von dreamy_betty funktioniert ebenfalls nicht.

Gibt es weitere Lösungsmöglichkeiten? ISTLEER müsste eigentlich klappen, ist mir ein Rätsel warum nicht.

 

[dreamy_betty]

Hallo DarkCava,

die Formel aus meinem Beitrag funktioniert eigentlich korrekt, wenn man sie richtig einsetzt. Ich habe das mal kurz selbst gebastelt, wie du an meinem angehängten Bild sehen kannst. Die drei Formeln - im Screenshot sind sie leider abgeschnitten, weil man dieses Fenster nicht vergrößern kann - lauten wie folgt. Sie wirken dabei auf den Zellbereich von C2:C22 und beinhalten daher auch leere Zellen.

grün (erreicht/abgelaufen) =UND(C2<>"";C2-HEUTE()<0)
rot (0 bis 9 Tage in der Zukunft) =UND(C2-HEUTE()>=0;C2-HEUTE()<=9)
gelb (10 bis 30 Tage i.d.Z.) =UND(C2-HEUTE()>=10;C2-HEUTE()<=30)

Damit man ISTLEER() nutzen kann, muss die Bedingung auch korrekt gewählt werden, man könnte beispielsweise auch folgende Formel verwenden:

grün =UND(NICHT(ISTLEER(C2));C2-HEUTE()<0)

Sollte das Datum in den Zellen allerdings nicht direkt eingegeben, sondern durch eine Formel (die ISTLEER-Variante fällt weg) eingefügt werden die einen ausgeblendeten Nullwert erzeugt (die originale Lösung fällt weg), dann müsste man die Prüfung C2<>"" anpassen. Im Prinzip kann man auch C2>0 verwenden, weil Excel das Datum intern als Ganzzahl betrachtet (der heutige 26.8.2013 ist intern die Zahl 41512, weil es der sovielte Tag seit dem 1.1.1900 ist).

grün =UND(C2<>0;C2-HEUTE()<0)
grün =UND(C2>0;C2-HEUTE()<0)

 

[DarkCava]

@ dreamy_betty

Ganz tolle Lösung. Herzlichen Dank, hast mir super weiter geholfen

 

[kuhtim]

Hallo,
ich habe ein Problem mit bedingter Formatierung.

Habe eine in der meherere Termine (Datum) stehen. Alle Vergangenen und heute in rot, die kommenden 2 monate gelb, und die weiter in der Zukunft grün.

Diese will ich automatisch einfärben:

Meine Vorgehensweise:

1. entsprechende Zellen markiert
2.Bedingte Formatierung
3. Neue Regel
4. alle zellen basierend auf ihren werten formatieren
5. 3 farben skala
6. alle felder auf formel eingestellt
7. in felder: =">=HEUTE()"

weiter komme ich nicht, bitte kann mir jmd helfen oder eine bessere methode sagen

Vielen Dank im Voraus!

 

[dreamy_betty]

Hallo kuhtim,

weil Excel in Farbskalen keine relativen Bezüge erlaubt, wird dir nur der etwas umständlichere Weg über drei einzelne bedingten Formatierungen bleiben. Wie das generell funktioniert steht ja schon in meiner Antwort weiter oben.

Für deinen Fall wären die folgenden Formeln passend. Dabei markierst du einen mit Datumswerten gefüllten Bereich - in diesem Beispiel ist es A2:A123, wobei A123 für das Ende steht und in keine tragende Rolle spielt - und verwendest für jede bedingte Formatierung eine der folgenden Formeln. Statt dem Zellbezug verwendest du die erste markierte Zelle.

=UND(A2<>0;A2<=HEUTE())
=UND(A2<>0;A2>HEUTE();A2<HEUTE()+60)
=UND(A2<>0;A2>=HEUTE()+60)


Als Anmerkung sollte ich hinzufügen, dass nicht genau mit 2 Monaten sondern mit 60 Tagen gerechnet wird. Unter der Haube ist für Excel ein Datum nur eine ganz normale Zahl, welche die Anzahl an Tagen seit dem 1.1.1900 darstellt. Man kann also vergleichsweise unkompliziert mit Tagen rechnen und erhält dadurch eine gut lesbare Formel. Wenn man aber unbedingt den selben Tag des Monats benötigt, müssen folgende Formeln verwendet werden.

=UND(A2<>0;A2>HEUTE();A2<DATUM(JAHR(HEUTE());MONAT(HEUTE())+2;TAG(HEUTE())))
=UND(A2<>0;A2>=DATUM(JAHR(HEUTE());MONAT(HEUTE())+2;TAG(HEUTE())))