Excel/Sheets - Warnung wenn bestimmtes Datum naht.

[Ryne]

Moin,

bin leider nicht allzu fit in Excel und stehe gerade vor einem Problem, vielleicht hat ja jemand einen Tipp.
Es geht um Folgendes: Verschiedene Kunden haben verschiedene Vertragslaufzeiten (Enddatum Spalte B). Ich möchte nun eine Warnung ausgeben, wenn der Vertrag dem Ende naht. (Also die jeweilige Zelle mit dem Datum rot färben) Dies soll, je nach Größe des Kunden (Spalte F = Umsatz) früher oder später geschehen. Beispielsweise ab 50 Euro Umsatz - 12 Monate vorher, zwischen 25 und 50 Euro - 6 Monate und weniger als 25 Euro - 3 Monate.

Wie genau stelle ich das an? Wahrscheinlich muss ich da eine Custom Formula verwenden richtig? Zumindest finde ich unter "Conditional Formatting" keine passende Lösung, um die Condition auf eine andere Spalte (F in meinem Fall) zu beziehen.

 

[floq0r]

Ich würde das Clustering des Umsatzes z.B. in Gruppen 1, 2 und 3 in einer Extra Spalte berechnen und dann unter Bezugnahme auf diese Spalte eine bedingte Formatierung vornehmen.

 

[DeusoftheWired]

https://support.microsoft.com/de-de...mationen-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

Angenommen, der Termin steht in Zelle B2. Dann legst du für diese Zelle drei neue bedingte Formatierungen an. Neue Regel → Formel zur Ermittlung der zu formatierenden Zellen verwenden.

=(B2-HEUTE())<7

→ rot

=(B2-HEUTE())<14

→ orange

=(B2-HEUTE())<21

→ gelb

Das färbt dir die Zelle B2 in rot ein, wenn weniger als 7 Tage bis zum Termin verbleiben. Mit weniger als 14 Tagen ist es orange, weniger 21 Tage gelb. Mehr als 21 Tage macht keine Farbe.

Das ganze nach deinem eigenen Geschmack anpassen.

 

[floq0r]

@DeusoftheWired Das berücksichtigt aber nicht den Umsatz

 

[Ryne]

https://support.microsoft.com/de-de...mationen-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

Angenommen, der Termin steht in Zelle B2. Dann legst du für diese Zelle drei neue bedingte Formatierungen an. Neue Regel → Formel zur Ermittlung der zu formatierenden Zellen verwenden.

=(B2-HEUTE())<7

→ rot

=(B2-HEUTE())<14

→ orange

=(B2-HEUTE())<21

→ gelb

Das färbt dir die Zelle B2 in rot ein, wenn weniger als 7 Tage bis zum Termin verbleiben. Mit weniger als 14 Tagen ist es orange, weniger 21 Tage gelb. Mehr als 21 Tage macht keine Farbe.

Das ganze nach deinem eigenen Geschmack anpassen.

So weit war ich auch schon, aber das Problem liegt darin, dass Spalte F (Umsatz) auch einbezogen werden muss, nicht nur das Datum.

 

[steve1da]

Hola,

da es ja immer rot werden soll, reicht eine Formel in der bed. Formatierung:

=((B2>=EDATUM(HEUTE();-12))*(B2<=EDATUM(HEUTE();-6)*(F2>50)))+((B2=EDATUM(HEUTE();-6))*(B2<=EDATUM(HEUTE();-3)*(F2>25)*(F2<50))+((B2>EDATUM(HEUTE();-3))*(F2<25)))

Gruß,
steve1da

 

[DeusoftheWired]

Ups, da war ja noch was. Einfach beide Bedingungen mit UND kombinieren.

=UND((B2-HEUTE())<365;F2>50)

→ rot

=UND((B2-HEUTE())<183;F2>25;F2<50)

→ rot

=UND((B2-HEUTE())<92;F2<25)

→ rot

 

[Ryne]

Ups, da war ja noch was. Einfach beide Bedingungen mit UND kombinieren.

=UND((B2-HEUTE())<365;F2>50)

→ rot

=UND((B2-HEUTE())<183;F2>25;F2<50)

→ rot

=UND((B2-HEUTE())<92;F2<25)

→ rot

Danke. Eigentlich brauche ich aber nur rot.

Also im Endeffekt in jede Zelle der Spalte B folgende 3 Formatierungsregeln

=(B2-HEUTE())<365=UND((B2-HEUTE())<365;F2>50)
=(B2-HEUTE())<180=UND((B2-HEUTE())<365;F2>25)
=(B2-HEUTE())<90=UND((B2-HEUTE())<365;F2<25)

Soweit richtig?

@steve1da

Das scheint nicht zu funktionieren bzw. wahrscheinlich mache ich es einfach falsch
Hatte versucht diese Regel für die Gesamte Spalte B anzuwenden.

 

[steve1da]

Hola,
du markierst vorher den Bereich für den die Formel gelten soll, dann setzt du die Formel in die bed. Formatierung ein. Wenn du das für die ganze Spalte B machen willst, muss aus Zeile 2 in der Formel Zeile 1 werden, ansonsten hast du einen Versatz drin.
Ansonsten weiß ich leider nicht was "funktioniert nicht" bedeuten soll.

 

[DeusoftheWired]

Soweit richtig?

Das dürfte nicht klappen. Eine bedingte Formatierung greift dann, wenn die Formel für die Ermittlung wahr wird. Weil du mehr als eine Bedingung hast, mußt du mit UND arbeiten. Das funktioniert nicht ganz so, wie wir es als Menschen sprachlich umsetzen, sondern muß am Anfang der Formel zur Ermittlung stehen. Die einzelnen Bedingungen trennst du mit einem Semikolon. Deshalb die Schreibweise wie in #7.

Und ja, jede Zelle braucht die drei Regeln.

 

[Ryne]

Das dürfte nicht klappen. Eine bedingte Formatierung greift dann, wenn die Formel für die Ermittlung wahr wird. Weil du mehr als eine Bedingung hast, mußt du mit UND arbeiten. Das funktioniert nicht ganz so, wie wir es als Menschen sprachlich umsetzen, sondern muß am Anfang der Formel zur Ermittlung stehen. Die einzelnen Bedingungen trennst du mit einem Semikolon. Deshalb die Schreibweise wie in #7.

Und ja, jede Zelle braucht die drei Regeln.

Ich fühle mich als wäre ich gerade komplett auf den Kopf gefallen, haha. Irgendwas hab ich noch falsch gemacht.

Ich habe für die Zelle B2 folgende 3 Formatierungen gesetzt:

=AND((B2-TODAY())<365,F2>1000000)
=AND((B2-TODAY())<183,F2>500000,F2<1000000)
=AND((B2-TODAY())<92,F2<500000)

Das Datum ist weniger als 1 Jahr weg und das Auftragsvolumen ist größer als 1 Mio. Mit diesen 3 Regeln müsste es sich doch jetzt rot färben oder nicht? Oder kann es noch zu Fehlern kommen wegen falscher Formatierung bei den Auftragsvolumenbeträgen?

 

[steve1da]

Jetzt wäre eine Beispieldatei hilfreich ;-)

 

[DeusoftheWired]

Den Antrag unterstütze ich! Macht sich mit einer anonymisierten Beispieldatei tausendmal einfacher und schneller als mit drölfzig Beiträgen hier im Forum.

 

[Ryne]

Okay, werde gleich ein erstellen. Passt Google Sheets oder besser in Excel fix konvertieren?

Edit: Habe gerade eine Beispieldatei auf meinem Privatrechner erstellt und das Ganze funktioniert wie zu erwarten. Muss ich wohl nochmal schauen was denn bei der Originaldatei falsch ist, eigentlich die gleiche Formel und die gleiche Spaltenbelegung genutzt. Kann es sein, dass manche Formate des Datums beispielsweise nicht erkannt werden? Das war der einzige Unterschied der mir spontan auffällt. (Original mit / und Testdatei mit Punkten getrennt.)

 

[steve1da]

Hola,

sowas kann man mit Istzahl() überprüfen. Istzahl(B2) ergibt was?
Gruß,
steve1da

 

[Ryne]

Hola,

sowas kann man mit Istzahl() überprüfen. Istzahl(B2) ergibt was?
Gruß,
steve1da

Wird als "False" angezeigt. Dann liegt es wohl tatsächlich daran, dass ich das / Datum Format verwendet habe, werde es mal mit einem . versuchen.

 

[DeusoftheWired]

Wenn ISTZAHL falsch zurückgibt, ist es kein Datum wie es Excel haben möchte. Wenn du nacheinander 1 8 . 1 1 . 2 0 2 0 Enter eingibst, speichert Excel das nicht als String dieser zehn Zeichen, sondern speichert es intern als Zahl, die am 01.01.1970 01.01.1900 mit der Zahl 1 beginnt und von da an pro Tag um eins hochgezählt wird. Der dritte Januar 1970 1900 ist also intern für Excel eine 3. Je nach Formatierung der Zelle versteckt Excel das aber vor dir und zeigt dir stattdessen etwas an, das ein Mensch als Datum wahrnimmt. Möchtest du mit einem Datum rechnen (wie wir in der Formel für die bedingte Formatierung), muß es eine Zahl sein. Schau zur Not mit F2, was als Zellinhalt angegeben wird.

 

[floq0r]

@DeusoftheWired Tag 1 ist der 1.1.1900

 

[DeusoftheWired]

Verdammt, du hast recht! Hab’s mit dem Unix-Zeitstempel verwechselt.

Wird oben editiert.