Excel - Färbung wenn Datum länger als... zurück liegt

[McDuc]

Hallo liebe Experten,

da ich auch nach längerer Recherche keine Lösung gefunden habe, hoffe ich mal wieder auf euer Know How
Ich bastle gerade eine Liste, in der ich gerne auf den erste Blick sehen möchte, mit wem ich schon länger keinen Kontakt mehr hatte.

Feld 1: letztes Kontaktdatum
Feld Name, Vorname soll:

• rot eingefärbt sein, wenn der letzte Kontakt länger als 3 Monate her ist
• gelb, wenn länger als 1 Monat
• grün, wenn in den letzten 4 Wochen ein Kontakt stattgefunden hat
• blau (sofern überhaupt 4 Farben möglich sind?!), wenn noch kein Kontakt stattgefunden hat (Feld 1 = leer)

Versucht habe ich schon die bedingte Formatierung mit den Formeln:
=>EDATUM("Feld";+3)
=>EDATUM("Feld";+1)
usw. was aber leider nicht korrekt zu sein scheint. Hat hier jemand eine Idee (möglichst ohne Makro, von denen ich keine Ahnung habe ).

Danke vielmals vorab!

 

[G-Red]

Versuch mal mit der Datedif Funktion
Hier ein paar Beispiele

 

[TrueAzrael]

Bin grad an keinem Rechner, aber müsstest du den Zellwert (also das letzte Kontaktdatum) nicht mit dem Heutigen Datum abzüglich der 3/1 Monate vergleichen?
Denn das Datum im Feld wird ja nie größer dem Datum im Feld plus ein oder drei Monaten sein.

 

[fuchen]

Machste mit einer bedingten Formatierung via Datedif

z.B.:
=datedif(X1;today();"M")>=3 --> länger als 3 Monate -> rot
=datedif(X1;today();"M")>=1 --> länger als 1 Monat -> gelb
=datedif(X1;today();"M")<1 --> weniger als 1 Monat -> grün

X1 ist hier die Vergleichszelle
"M" steht für den Monat, der verglichen wird

richtige Reihenfolge beachten, damit auch die richtige Farbe benutzt wird (weil >1 ja ebenfalls gegeben ist bei >3)

 

[McDuc]

Vielen Dank, das schaut schon mal gar nicht so verkehrt aus.
Wie müsste die Formel dann korrekt lauten:

=DATDIF(HEUTE;Feld1;"M") und wie bekomme ich die > und < in die Formel?

 

[G-Red]

Genau, du müsstest ein Feld haben, wo das Datum zum letzen Kontakt ist und das derzeitige dateum mit der Funktion Datum() abgleichen.

 

[fuchen]

Nimm am besten das Video hier, das zeigt genau den Ablauf, wie du die bedingte Formatierung festlegst.
(die richtige Zelle bei seinem "$C2" und natürlich M statt Y nehmen)

YouTube

An dieser Stelle steht ein externer Inhalt von YouTube, der den Forumbeitrag ergänzt. Er kann mit einem Klick geladen und auch wieder ausgeblendet werden.

YouTube-Embeds laden

Ich bin damit einverstanden, dass YouTube-Embeds geladen werden. Dabei können personen­bezogene Daten an YouTube übermittelt werden. Mehr dazu in der Datenschutzerklärung.

YouTube-Embeds laden

Datenschutzerklärung

 

[McDuc]

Klasse, vielen Dank. Damit werde ich mich mal spielen.

 

[cruse]

und wie bekomme ich die > und < in die Formel?

Tastatur?

@fuchen Anstatt Komma's Semikolon ?

 

[McDuc]

@cruse: ?

 

[cruse]

sorry etwas zu spät geantwortet^^

ich speicher mir btw. auch mal den thread, da ich die Funktion hin und wieder auch gesucht hab

 

[McDuc]

Also:

• größer als 3
• kleiner als 1 klappt

Habt ihr noch eine Idee, wie die Formel aussehen müsste, wenn ich zwischen 1 & 3 gelb haben möchte? Bzw. blau wenn leer?

Aktuell ist es so, wenn ich =datedif(Feld;heute();"m")>=1 eingebe, dann wird das Feld gelb, egal ob 2 oder 8 Monate dazwischen liegen (d.h. die "rote" Formel wird nicht berücksichtigt?!

 

[fuchen]

in Excel oben in der Zelle auf "Bedingte Formatierung" -> Regeln verwalten -> dann in der Übersicht mit den Pfeiltasten oben die Regeln an die richtige Position schieben, dass die Regel mit der roten bzw. blauen Färbung immer vorgeht

die Regeln werden nacheinander abgearbeitet, sodass sie dann immer überschrieben werden, wenn sie in der falschen Reihenfolge sind

Ergänzung (27. Mai 2020)

 

[McDuc]

Irre - wenn man es erst mal weiß, macht es durchaus Sinn, aber ansonsten steht man(n) echt wie der Ochs vorm Berg. Vielen Dank, jetzt klappt es!

btw, für diejenigen, die vielleicht Ähnliches suchen und brauchen: die Formatierung für leer lautet "=istleer(Feld)"

 

[steve1da]

Hola,

alternativ:

=(datedif(Feld;heute();"m")>=1)*(datedif(Feld;heute();"m")<3)

Gruß,
steve1da

 

[McDuc]

Jetzt muss ich leider doch noch mal fragen:

Das mit der Färbung etc. klappt hervorragend, ABER...

Mein Plan ist ein sehr einfache CRM. D.h ich habe eine Seite, auf der all die Daten von Person X stehen.
Auf einer allg. Übersichtsseite möchte ich nur vereinzelte Daten angezeigt bekommen.
Als Beispiel:

Übersichtsseite:
Name, Vorname, Geb.datum und Kundennummer
Kundenseite:
Name, Vorname, Geb.datum, Kundennummer, Datum des letzten Kontakts uvm.

Um das ganze zu vereinfachen bzw. auch zukünftig schnell ändern zu können, habe ich die Felder der Übersichtsseite auf die entsprechenden Felder auf der Kundenseite verknüpft (Name=Name usw.). Jetzt ist es aber so: auch wenn ich auf der Übersichtsseite formatiere, werden komischerweise nicht diese Felder farblich hinterlegt, sondern die im eigentlichen Kundenblatt?!

Eine schnelle und unkomplizierte Lösung wäre vmtl., wenn ich das letzte Kontaktdatum einfach auf der Übersichtsseite integrieren würde, was ich aus datenschutzrechtlichen Gründen aber nicht möchte.

Hat hier noch jemand eine Idee?

 

[fuchen]

Du kannst dir den Input für deine bedingte Formatierung aus jedem beliebigen Tab holen.
Ich würde an deiner Stelle einfach den Namen in der Übrsichtsseite formatieren und dir die Daten des Kontaktdatums vom anderen Tab holen.

bspw.

=datedif(Tabelle2!X1;today();"M")>=3
=datedif(Tabelle2!X1;today();"M")>=1
=datedif(Tabelle2!X1;today();"M")<1

"Tabelle2" ist hier der Name des zweiten Tabs

Ich habe kurz gegoogelt, es kann gut sein, dass Excel auch 2020 noch keine bedingte Formatierung aus anderen Tabs übernehmen kann (wie dämlich)

Alternative1:

Falls das nicht gehen sollte, musst du den Bereich auf dem zweiten Tab, den du vorn benutzen willst, einen Namen geben.

den Bereich in dem Tab markieren, "Namen definieren", dem Bereich einen Namen geben und diesen dann in der bedingten Formatierung einsetzen

wenn du dem Bereich dann den Namen "Datumsdingsbums" vergibst, sähe dein neuer Code in etwa so aus:

=datedif(Datumsdingsbums;today();"M")>=3
=datedif(Datumsdingsbums;today();"M")>=1
=datedif(Datumsdingsbums;today();"M")<1

Alternative 2:
Die Variante für Faule ohne viel Fummeln... also mein Lieblingsweg

Lege dir in deiner Übersichtsseite ganz hinten in Spalte AA (oder sonstwo) eine Hilfszelle an, in der genau die Datumsdifferenz drinsteht, und prüfe einfach auf diese Zahl.

z.b. schreibst du auf deine Übersichtsseite in Spalte AA

AA1 -> datedif(Tabelle2!$X1;today();"M")
(lässt das nach unten beliebig lang ausfüllen)

und dann vergleichst du die bedingte Formatierung auf der Übersichtsseite einfach mit dieser Zeile, dann musst du gar nichts aus deinem anderen Tab in der bedingten Formatierung vergleichen und er kann nicht meckern

Wenn AA1<1 -> grün
Wenn AA1>=1 -> gelb
Wenn AA1>=3 -> rot

 

[McDuc]

Top, das probiere ich - herzlichen Dank.