Excel bedingte Formatierung?
Hallo,
leider komme ich bei meiner Excel-Tabelle nicht weiter und hoffe daher auf eure Hilfe.
In Zelle E2 soll manuell ein Datum eingetragen werden - dieses ist variabel.
In Zelle F2 soll das Datum ein Jahr später automatisch eingegeben werden, wenn E2 befüllt wird. Hier habe ich "=E2+365" als Formel eingegeben.
Das funktioniert soweit auch, nur dass Excel in der Zelle F2 automatisch 30.12.1900 einträgt, obwohl in E2 nichts steht.
Jetzt soll F2 aber rot befüllt werden, wenn das Datum in E2 ein Jahr überschreitet. Meines Verständnisses nach also =E2+366, aber das funktioniert leider nicht.
Deshalb bin ich hier und hoffe auf eure Hilfe.
3 Antworten
1.) Dass bei der Formel "=E2+365" der 30.12.1900 erscheint, wenn E2 leer ist, das ist logisch, denn EXCEL interpretiert eine leere Zelle als 0. Eine 0 beim Datum entspricht bei EXCEL 1 Tag vor dem Beginn der "EXCEL-Zeitrechnung" 1.1.1900.
Das kannst du vermeiden, indem du in der Formel eine Bedingung einbaust für den Fall, dass E2 leer ist: =wenn(E2="";"";E2+365")
2.) "...wenn das Datum in E2 ein Jahr überschreitet."
Da verstehe ich nicht, wie du das meinst.
Deine Beschreibung ist immer noch unklar.
Meinst du, F2 soll rot sein, wenn das aktuelle Tagesdatum nach dem Datum in F2 ist?
F2 soll rot sein, wenn E2 mehr als 365 Tage überschritten wird
Wenn in F2 die Formel =wenn(E2="";"";E2+365") steht, dann ist die Differenz zwischen E2 und F2 IMMER GENAU 365 Tage!
Dann ist es doch gar nicht möglich, dass der Unterschied zwischen E2 und F2 mehr als 365 Tage beträgt.
okay, ich versuche es anders zu beschreiben.
Wenn ich in E2 als Datum 01.01.2020 eintrage, steht in F2 das Datum 01.01.2021.
Eine bedingte Formatierung soll jetzt bewirken, dass die Zeile 2 (nicht nur Zelle F2, sorry für die Umstände) ab dem 02.01.2021 rot gefärbt wird - also nach Ablauf von E2+1Jahr.
Ich weiß nicht, wie ich es anders beschreiben soll :-)
Die ganze Liste soll mehrere Daten beinhalten, also steht in E3 ein weiteres Datum, in E4 noch eins usw. Die Replikation ist aber kein Problem.
Ich versteh's nicht wirklich und rate jetzt einfach mal:
Wenn die Zellen in Zeile 2 rot sein sollen, falls ihr Inhalt größer ist als das Datum in Zelle F2, dann:
- Zeile 2 markieren
- Bedingte Formatierung "Regeln zum Hervorheben von Zellen"
- Größer als
- =$F$2 eingeben in das Feld (oder in die Zelle F2 klicken)
- Ok drücken
Genau so meine ich das. Aber das funktioniert nicht wirklich.
Wenn ich es so eintrage, wie Du es beschrieben hast, passiert leider nichts - auch nicht, wenn das Datum in F2 bereits abgelaufen ist.
Was meinst du denn mit "abgelaufen"?
Wenn du die Bedingte Formatierung in Zeile 2 genau so machst, wie ich es geschrieben hab, und wenn in F2 z.B. das Datum 31.12.2020 steht und du z.B. in Zelle A2 das Datum 03.08.2021 eingibst, dann muss die Zelle A2 rot werden, weil der Inhalt von A2 größer ist als der Inhalt von F2.
Und das funktioniert wirklich nicht?
Ich bedanke mich für Deine Mühe und Deine Hilfe :-)
Eine kurze Beschreibung zur Datei:
Es ist eine Datei, in der über das Datum gepflegt wird, wann etwas geliefert wird und wann die Garantie abläuft. Garantiezeit 1 Jahr.
Lieferdatum ist Zelle E2, Garantie-Ende ist Zelle F2 (daher die Formel von Dir vorher).
Wenn F2 das gestrige Datum (02.08.2021) überschreitet (E2 dann entsprechend 02.08.2020), soll Zeile 2 rot gefärbt werden.
Wenn zb in Zelle F8 das heutige Datum steht, soll Zeile 8 entsprechend ab morgen rot markiert werden.
Ach, es geht also DOCH ums aktuelle Tagesdatum!
Danach hatte ich doch extra oben in einem Kommentar gefragt, aber darauf bist du nicht eingegangen, so dass ich annehmen musste, dass das aktuelle Tagesdatum keine Rolle spielt und nur die Daten in den Zellen miteinander verglichen werden sollen.
Wenn du das Datum in F2 mit dem aktuellen Tagesdatum abgleichen willst, dann gib bei der Bedingten Formatierung als Vergleichswert nicht =$F$2, sondern =HEUTE() ein.
Nein, tut es nicht, das war ein blödes Beispiel. Sorry für die Umständlichkeiten
Denn wenn in E2 zb der 01.06.2020, in F2 entsprechend der 01.06.2021 steht, spielt das heutige Datum keine Rolle, denn dann ist der erste Tag ohne Garantie der 02.06.2021 und dann soll in dem Fall auch ab dem 02.06.2021 die Zeile 2 rot markiert werden.
Hier können verschiedene Daten stehen, also auch welche, bei denen die Garantie bereits abgelaufen ist. Und natürlich auch noch laufende Garantie-Zeiten, bei denen die Zeile erst dann rot werden soll, wenn deren Garntie abgelaufen ist.
Ich hoffe das war jetzt so verständlich :-)
Das wird ja immer konfuser!
Ich bin jetzt raus.
Im Großen und Ganzen soll die Zeile 2 ab dann rot werden, wenn das Datum, welches in F2 steht, um mindestens einen Tag überschritten wird :-)
Moin,
ich fange mal ganz von vorn an.
- Allgemein
Excel interpretiert Zahlen als Datum. Die ganzen Zahlen stellen den Tag des Jahres dar und das hinterm Komma ist die Zeit. Eine Leere Zelle ist 0. Das "0-Datum" ist 00.01.1900. Sozusagen ist Tag 10 der 10.01.1900. Tag -7 ist theoretisch 24.12.1899. Das funktioniert in meinem Excel 2016 nicht ... Wenn man mit Zeit rechnen möchte kann ich nur Raten bei Einstellungen vor beginn der Erstellung der Tabelle auf "1904 Datumswerte" zu stellen. Dann kann man auch mit negativen Zeiten rechnen...
Kleiner Gag am Rande:
=Datum(Jahr;29;2)<>Datum(Jahr;03;01)
liefert dir ob das Jahr ein Schaltjahr ist ...
_2. 365
Datum plus 365 ist nicht genau ein Jahr später. Es gibt Schaltjahre mit 366 Tagen. Wenn man das über mehrere Jahrzehnte rechnet wird der Fehler recht groß!
Ich würde immer mit:
=Datum(Jahr(F2+1);Monat(F2);Tag(F2))
rechnen. Dann hat man genau ein Jahr. Unabhängig vom Jahr! (Bei Geburtstagen ist das Interessant!)
_3. 0 wegmachen
wenn man eine 0-Unterdrückung haben möchte gibt es unterschiedliche Optionen. Entweder mit Wenn oder in Bedingten Formatierungen die Zelle einfach auf "Weiße Schrift" Stellen, wenn es 0 ist. Der 00.01.1900 ist auch 0.
_4. Rot machen.
Das habe ich nicht verstanden. Wenn du automatisch berechnen lässt, das genau 1 Jahr später angezeigt wird, kann Excel den Wert nicht überschreiten.
Wenn jemand etwas anderes (außer die Formel) von Hand eingibt könnte man mit Bedingten Formatierungen das Feld entsprechend Rot färben.
Besser wäre es aber das Feld entsprechend mit Blattschutz zu schützen!
_5. Garantie
Aus deinen Ausführungen bei Rübezahl entnehme ich, das du dir gern Garantiesachen anzeigen lassen würdest.
Dazu brauchst du einen Bezug! Der Bezug kann eigentlich immer nur =Heute() sein.
Bei Bedingte Formatierungen müsstest du dann eintragen (Formel)
=Heute()=> F2
Dann könnte es Rot werden, wenn deine Garantier abläuft/abgelaufen ist.
Grüße
Hallo, vielen lieben Dank für die ausführliche Antwort. Ich konnte inzwischen Hilfe bei unseren Controllern finden, die auch sehr viel mit Excel arbeiten.
Die Thematik mit dem Schaltjahr und die damit verbundene Abweichung über mehrere Jahrzehnte hinweg, sehe ich als nicht so großes Problem an, da die Datei vermutlich nur einige Jahre relevant bleibt. Das ist nicht genau abzusehen, aber nicht weiter schlimm, wenn es um einen Tag pro Schaltjahr verschoben wird. Das mit dem heutigen Datum bzgl. der Garantie leuchtet mir nicht ein. Dafür ist mein Excel-Verständnis zu klein :-) Aber die Datei funktioniert jetzt wie sie soll, was die Hauptsache ist :-)
Danke dennoch für Deine Unterstützung hier und die ausführliche Beschreibung
Hallo Rubezahl,
vielen Dank für deine schnelle Antwort.
Bei 2. meine ich, dass wenn in E2. zb der 01.10.2020 eingetragen wird, soll in F2 entsprechend 01.10.2021 stehen (das funktioniert)
Ab dem 02.10.2021 soll die Zelle aber dann rot gefärbt werden.
Vielen Dank für Deine Hilfe bei 1.) das hat super funktioniert :-)