[Excel] Bedingte Formatierung Datum

[Bischoff.Florian]

Hallo *,

leider bin ich bzgl. meines konkreten Problems leider nicht fündig geworden. Daher hier meine Frage:
Ich habe eine Mitgliederliste. In dieser steht in Spalte I ein Eintrittsdatum im Format TT.MM.JJJJ.
Ich möchte nun, dass die Zelle rot markiert wird, wenn das Mitglied 10, 20, 30, 40 oder 50 Jahre Mitglied ist.
Da reicht es mir völlig, wenn die Jahreszahlen passen. Ich habe für die bedingte Formatierung folgende Formeln geschrieben:
=WENN((JAHR(HEUTE())-JAHR(I1))=50;1;0)
=WENN((JAHR(HEUTE())-JAHR(I1))=40;1;0)
=WENN((JAHR(HEUTE())-JAHR(I1))=30;1;0)
=WENN((JAHR(HEUTE())-JAHR(I1))=20;1;0)
=WENN((JAHR(HEUTE())-JAHR(I1))=10;1;0)

Das funktioniert soweit auch super. Aber wie bekomme ich es hin, dass diese Formeln auch für die nächste Zele Anwendung findet, ohne für jede Zeile eine neue bedingte Formatierung zu erstellen und den Wert JAHR() zu ändern?
Also ich würde jetzt jede Zelle einzeln auswählen müssen, auf bedingte Formatierung gehen, die oben genannten Formeln eintragen mit dem nächsten JAHR() Wert... also für Zeile I2 dann JAHR(I2) und für Zeile I3 JAHR(I3).... etc.

Das geht doch bestimmt auch einfacher, oder?

Ich hoffe man versteht mich überhaupt

Vielen Dank schonmal für euer Wissen

Gruß,
Florian

 

[Keef]

Du hast nicht geschrieben, mit welchem Programm du arbeitest.

Mit Excel für Mac lässt sich die Formatierung oder die Formel doch kopieren. Dies müsste m.E. auch mit Numbers möglich sein.

 

[Bischoff.Florian]

Hallo.

Sorry, ich nutze Excel 2016.

Ja, ich kann sie kopieren, aber dann bleibt der Bezug von Jahr() ja immer bei I1. Der Bezug muss sich immer auf die aktuelle Zelle anpassen.

Gruß

 

[double_d]

Nach meinem Kenntnisstand verfügt Numbers nicht über die Möglichkeit bedingte Formatierungen mit Formeln zu setzen.
Daher kann es nur Excel sein.
/edit
Sorry, hab Deine Antwort erst nach meinem Beitrag gesehen. Aber Du sagst es ja. Konnte nur Excel sein.

In Excel kannst Du die formelgestützte bedingte Formatierung für einen Zellbereich (Matrix) anlegen, und brauchst nur einmal die Formel eingeben.

Dafür markierst Du die gesamte Spalte I in Deinem Excelblatt und legst mehrere Regeln mit jeweils einer Deiner oben genannten Formel an. Du kannst exakt die Syntax mit I1 nehmen. Hast Du die ganze Spalte vorher markiert, ergänzt Excel die Formel in den Zeilen jeweils zu I2, I3 usw., ohne, dass Du etwas machen musst.

Ganz simpel.

 

[Bischoff.Florian]

Hallo.

Vielen Dank. Das wäre ja wirklich simpel.
Ich werde es testen.

Gruß

 

[double_d]

Vielen Dank. Das wäre ja wirklich simpel.

So ist es.

Ich würde aber auch tatsächlich 5 separate Regeln anlegen, die jeweils 10 - 50 Jahre abprüfen und dann unterschiedliche Farben benutzen. Für eine Verschachtelung der Wenn-Formel sind das zu viele Kriterien und Du könntest dann auch nur eine Formatierung für alle Jahrzehnte der Mitgliedschaft wählen.

Also einmal ein wenig Arbeit, die 5 Regeln, bzw. die 5 Formeln zu schreiben, aber dann klappt es.

Btw. Mit der Formel greifst Du allerdings auch nur das Jahr ab. Ganz konkret heißt dass, wenn ein Mitglied am 23.10.1969 Mitglied geworden ist, wird ab dem 01.01.2019 die 50jährige Mitgliedschaft angezeigt, obwohl da noch fast 11 Monate fehlen.
Wenn das so gewollt ist, dann ok, ansonsten würde ich mindestens den Monat und das Jahr vergleichen.

Sofern notwendig, hier die abgewandelten Formeln für Jahr und Monat:
=WENN(UND(MONAT(HEUTE())-MONAT(I1)<=0;JAHR(HEUTE())-JAHR(I1)=50);1;0)
=WENN(UND(MONAT(HEUTE())-MONAT(I1)<=0;JAHR(HEUTE())-JAHR(I1)=40);1;0)
=WENN(UND(MONAT(HEUTE())-MONAT(I1)<=0;JAHR(HEUTE())-JAHR(I1)=30);1;0)
=WENN(UND(MONAT(HEUTE())-MONAT(I1)<=0;JAHR(HEUTE())-JAHR(I1)=20);1;0)
=WENN(UND(MONAT(HEUTE())-MONAT(I1)<=0;JAHR(HEUTE())-JAHR(I1)=10);1;0)

<=0 ist hier notwendig, damit ab dem Eintrittsmonat im Vergleichsjahr, der Rest des Jahres als Jubiläumsjahr stehen bleibt.

Ist also Jemand am 19.04.2008 eingetreten, würde in Deiner Liste ab dem 01.04.2018 bis zum 31.12.2018 das 10jährige Jubiläum angezeigt. Ab dem 01.01.2019 wartet dieser Eintrag dann wieder auf das Vergleichsjahr 2028 und würde ab dem 01.04.2028 das 20jährige Jubiläum anzeigen.

Also nur, wenn Du das so willst.

 

[Bischoff.Florian]

Vielen Dank! Es hat funktioniert. Das ist ja jetzt wirklich simpel gewesen
Mir reicht das Jahr, das ist nur, damit ich mir vor der Jahreshauptversammlung die Liste angucke und die markierten Einträge sofort sehe und überprüfen kann, ob das Mitglied bei der Versammlung geehrt werden muss oder nicht. So genau muss es nicht sein.