[Excel] Datum in Zelle bedingt formatieren, wenn älter als zwei Jahre

[Isi1202]

Hallo,

ich habe in Zellen verschiedene Daten (Datum) eingetragen. Ich möchte die Zellen mittels der bedingten Formatierung so formatieren das mir die Zellen farbig angezeigt werden, wenn zwei Jahre überschritten sind. Leider konnte ich keine geeignete Formatierung finden.

Viele Grüße,
Isabel

 

[kelevra]

Du musst folgende Formel auf die erste Zelle der Spalte anwenden:

=A1<DATUM(JAHR(HEUTE())-2;MONAT(HEUTE());TAG(HEUTE()))

A1 ist dabei die erste Zelle, musst du bei dir entsprechend anpassen.
Die "-2" hinter ...JAHR(HEUTE()) gibt deine Bedingung "älter als 2 Jahre" wieder.

 

[Isi1202]

Gibt es auch die Möglichkeit es das Excel dies automatisch aktualisiert? Ich nehmen an, so müsste ich das Datum regelmäßig selbst aktualisieren um es aktuell zu halten.

 

[kelevra]

Es wird immer von "Heute" gezählt. Wenn du also die Tabelle in einem Monat öffnest, werden automatisch die Zellen markiert die älter als der 28.5.16 sind. "HEUTE" bezieht sich immer auf den Zeitpunkt, zudem du die Tabelle öffnest.

 

[TOM-OSx]

In der Formel steht "heute()" drinnen, das bedeutet das der Inhalt von "a1" immer beim öffnen mit heute verglichen wird!

 

[kelevra]

Heute ist der Wert für HEUTE() der 28.04.16
Morgen ist der Wert für HEUTE() der 29.04.16
Übermorgen ist der Wert für HEUTE() der 30.04.16
usw.

Und ab diesem Wert zählt Excel dann.

In der Formel wird HEUTE() immer mit dem Zeitpunkt gleichgesetzt, an dem du die Datei öffnest, nicht wann du die Formel eingetragen hast.

 

[Isi1202]

Ich habe nun folgende Formel eingegeben:

=N7<DATUM(2013*(HEUTE())-2;9*(HEUTE());1*(HEUTE()))

Excel sagt mir aber das hier ein Fehler ist bzw falsch sei.
Wo liegt mein Fehler?

 

[kelevra]

Du hast die Begriffe "JAHR", "MONAT" und "TAG" durch Zahlen ersetzt. Das ist falsch.

Übernimm die Formel wie oben.

Für deine Anforderung, Daten älter als 2 Jahre bspw. rot zu markieren, schreibst du die -2 hinter JAHR(HEUTE()).

 

[Isi1202]

In der Spalte sind allerdings mehrere Daten vorhanden, die es alle gilt nach der Regel zu formatieren. Das ist mein Problem.
Vielen Dank schon mal, in Excel bin ich noch eine Nullnummer

 

[kelevra]

Das macht nichts. Du kannst entweder die komplette Spalte markieren oder nur einen Teil. Anschließend öffnest du den Editor für die Bedingte Formatierung und nutzt in der Formel die erste Zelle in der Spalte.

Wenn du bspw. die Zellen A1 bis A20 markiert hast, reicht es in der Formel =A1<DATUM(JAHR(HEUTE())-2..... usw. anzugeben. Die Formel wirkt sich automatisch auf den gewählten Bereich aus.

 

[Isi1202]

Ich bin deinen Anweisungen gefolgt und komme nun komischerweise auf dieses Ergebnis:


Hierbei ist mir nicht ganz klar weshalb einige Daten, die eigentlich nicht in die Formel passen trotzdem rot markiert sind und einige die reinpassen dann nicht markiert.

Ich wollte ebenso in dieselbe Spalte die Formel mit nur einem Jahr Unterschied - also -1 - einfügen und es sind dieselben Zellen, wie im Bild rot, gelb markiert.

 

[kelevra]

Kannst du mal die Excel Datei hochladen?

 

[Isi1202]

Leider lässt sich die Datei nicht hochladen, da es ein fehlerhaftes Dateiformat ist.

 

[kelevra]

Lad sie einfach hier hoch und poste den Link:

http://www.file-upload.net

 

[Isi1202]

Ich musste die Spalte in eine neue Datei einfügen, da die originale Datei personenbezogene Daten enthält.

http://www.file-upload.net/download-11609678/Arbeitsmappe1.xlsx.html

 

[kelevra]

Setze in der Formel die erste Zelle auf A1, sodass die Formel so aussieht:

=A1<DATUM(JAHR(HEUTE())-2;MONAT(HEUTE());TAG(HEUTE()))

 

[Isi1202]

Das hat super geklappt. Vielen Dank.

Gibt es auch für den Bereich von bspw 3-6 Monaten eine Formatierung?
Für vor 6 Monaten müsste sie ja wie folgt aussehen:

=A1<DATUM(JAHR(HEUTE());MONAT(HEUTE())-6;TAG(HEUTE()))

 

[kelevra]

Du legst einfach für den Bereich zwei Regeln an:

 

[Isi1202]

Vielen Dank.

Ich merke, dass Excel manchmal doch nicht so leicht ist wie gedacht.

 

[kelevra]

Kein Problem, ich helfe wo ich kann. Bei Excel muss ich auch immer wieder nachschauen und ausprobieren.