Dieses Forum nutzt Cookies

Nitro · 08.03.2016, 14:33

Hallo,

ich hoffe die Profis können mir weiter helfen.
Zuerst: Wenn zu meiner Frage schon eine Antwort geben sollte, löscht meinen Beitrag und zeigt mir einen Link. Die Suche hat mir aber nicht weiter geholfen.

Nun zu meinem Anliegen:

Es geht um Messmittel. Diese müssen regelmäßig kalibriert werden.

Spalte H Datum der letzten Kalibrierung
Spalte J das Datum ab/seit wann das Messmittel eingesetzt wird und
in Spalte K das Datum der nächsten Prüfung

Und das für hunderte Messmittel untereinander.

Jetzt wünscht mein Kollege das die Zellen in der Spalte K farblich formatiert werden. Und zwar nach der Vorgabe:

Wenn das Datum in der Spalte K um 2 Jahre kleiner ist als das Datum in Spalte J, dann die Zellen in Spalte J bitte rot einfärben.
Wenn das Datum in der Spalte K um 23 Monate kleiner ist als das Datum im Spalte J, dann bitte die Zellen in Spalte J gelb.

Und wenn in Spalte K kein Datum steht, kommt das Datum aus Spalte H zum Einsatz.

	H	I	J	K
1	Dat.letzte Prüfung	Interv.	ggf. einges.	nächste Prüfung
2	03.07.2012	24	13.01.2014	Jan. 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan. 17
5	11.03.2014	24	26.01.2015	Jan. 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz. 17

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Ich habe es schon mit einer Formel in "Bedingter Formatierung" versucht, bekomme aber immer "Bezug"

Bestimmt ganz einfach, aber ich komme nicht weiter Huh

:s

steve1da · 08.03.2016, 14:40

Hola,

Code:
löscht meinen Beitrag und zeigt mir einen Link.

das wäre von der Reihenfolge eher schlecht :D

Wie genau sahen denn deine Versuche aus?

Eine Beispieldatei mit Wunschergebnis wäre von Vorteil.

Gruß,
steve1da

***Rabe*** · Registriert seit: 10.04.2014 Version(en): 2016 + 365

Hi,

(08.03.2016, 14:33)Nitro schrieb: Wenn das Datum in der Spalte K um 2 Jahre kleiner ist als das Datum in Spalte J, dann die Zellen in Spalte J bitte rot einfärben.
Wenn das Datum in der Spalte K um 23 Monate kleiner ist als das Datum im Spalte J, dann bitte die Zellen in Spalte J gelb.

Und wenn in Spalte K kein Datum steht, kommt das Datum aus Spalte H zum Einsatz.

ich habe jetzt "größer" statt "kleiner" angenommen, denn K ist hier nie kleiner als J, Bedingung 1 und 2 ist rot, Bedingung 3 und 4 gelb:

Tabelle2

	H	I	J	K
1	Dat.letzte Prüfung	Interv.	ggf.	nächste Prüfung
2	03.07.2012	24	13.01.2014	Jan 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan 17
5	11.03.2014	24	26.01.2015	Jan 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz 17

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed. Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$J$2:$J$9	01.Bed.: Formel ist		=K2>DATUM(JAHR($J2)+2;MONAT($J2);TAG($J2))		255					2	J2:J9
$J$2:$J$8	02.Bed.: Formel ist		=H2>DATUM(JAHR($J2)+2;MONAT($J2);TAG($J2))		Test					2	J2:J8
$J$2:$J$8	03.Bed.: Formel ist		=K2>DATUM(JAHR($J2);MONAT($J2)+23;TAG($J2))		Test					2	J2:J8
$J$2:$J$8	04.Bed.: Formel ist		=H2>DATUM(JAHR($J2);MONAT($J2)+23;TAG($J2))		Test					2	J2:J8

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$J$2:$J$9	01.Bed.		Falsch					2	J2:J9
$J$2:$J$8	02.Bed.		Falsch					2	J2:J8
$J$2:$J$8	03.Bed.		Falsch					2	J2:J8
$J$2:$J$8	04.Bed.		Falsch					2	J2:J8

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

oder ohne fixe Festschreibung des Intervalls, dafür mit Bezug auf die Spalte I:

Tabelle2

	H	I	J	K
1	Dat.letzte Prüfung	Interv.	ggf.	nächste Prüfung
2	03.07.2012	24	13.01.2014	Jan 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan 17
5	11.03.2014	24	26.01.2015	Jan 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz 17

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed. Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$J$2:$J$9	01.Bed.: Formel ist		=K2>DATUM(JAHR($J2);MONAT($J2)+$I2;TAG($J2))		255					2	J2:J9
$J$2:$J$8	02.Bed.: Formel ist		=H2>DATUM(JAHR($J2);MONAT($J2)+$I2;TAG($J2))		Test					2	J2:J8
$J$2:$J$8	03.Bed.: Formel ist		=K2>DATUM(JAHR($J2);MONAT($J2)+$I2-1;TAG($J2))		Test					2	J2:J8
$J$2:$J$8	04.Bed.: Formel ist		=H2>DATUM(JAHR($J2);MONAT($J2)+$I2-1;TAG($J2))		Test					2	J2:J8

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$J$2:$J$9	01.Bed.		Falsch					2	J2:J9
$J$2:$J$8	02.Bed.		Falsch					2	J2:J8
$J$2:$J$8	03.Bed.		Falsch					2	J2:J8
$J$2:$J$8	04.Bed.		Falsch					2	J2:J8

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

Datei (Klick):

.xlsb

bedingte Formatierung zur Kalibrierung.xlsb (Größe: 10,86 KB / Downloads: 3)

Nitro · 08.03.2016, 15:37

Hallo Steve,

so soll es am Ende aussehen.

"K4" soll rot sein weil es "J4" + 2Jahre ist.
"K10" das gleiche, bezieht sich aber auf "H10" da bei J10 nichts eingetragen ist.
"K11" soll gelb sein, weil Differenz zu "J11" nur 23 Monate.

Nehme ich die "bedingte Formatierung" und ändere das Datum zb. in J4, so das es auch nur 23 Monate wären ändert sich die Farbe aber nicht von rot auf gelb.

Gebe ich

J4 < DATUM (JAHR(HEUTE());MONAT(HEUTE());TAG(HEUTE()))

ein, habe ich als Ergebnis #Bezug.

Und mit WENN DANN stehe ich ja erstrecht auf Kriegsfuß

Nitro · 08.03.2016, 15:43

(08.03.2016, 15:34)Rabe schrieb: Hi,

(08.03.2016, 14:33)Nitro schrieb: Wenn das Datum in der Spalte K um 2 Jahre kleiner ist als das Datum in Spalte J, dann die Zellen in Spalte J bitte rot einfärben.
Wenn das Datum in der Spalte K um 23 Monate kleiner ist als das Datum im Spalte J, dann bitte die Zellen in Spalte J gelb.

Und wenn in Spalte K kein Datum steht, kommt das Datum aus Spalte H zum Einsatz.

ich habe jetzt "größer" statt "kleiner" angenommen, denn K ist hier nie kleiner als J

Mein Fehler.Spalten verstausch beim Schreiben.
J muss kleiner sein als K

***Rabe*** · Registriert seit: 10.04.2014 Version(en): 2016 + 365

Hi,

(08.03.2016, 15:37)Nitro schrieb: J4 < DATUM (JAHR(HEUTE());MONAT(HEUTE());TAG(HEUTE()))

vor dem J4 sollte auch ein = stehen.

Ich komme auf andere Einfärbungen:

Meßmittelverwaltung

	H	I	J	K
1	Dat.letzte Prüfung	Interv.	ggf. einges.	nächste Prüfung
2	03.07.2012	24	13.01.2014	Jan. 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan. 17
5	11.03.2014	24	26.01.2015	Jan. 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz. 17
9	03.05.2013	24	19.03.2015	Mrz. 17
10	23.02.2015	24		Feb. 17
11	23.02.2015	24	19.03.2015	Feb. 17
12	02.04.2012	24	05.02.2015	Feb. 17

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed. Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$K$2:$K$12	01.Bed.: Formel ist		=$K2>DATUM(JAHR($J2);MONAT($J2)+$I2;TAG($J2))		255					2	K2:K12
$K$2:$K$12	02.Bed.: Formel ist		=$H2>DATUM(JAHR($J2);MONAT($J2)+$I2;TAG($J2))		255					2	K2:K12
$K$2:$K$12	03.Bed.: Formel ist		=$K2>DATUM(JAHR($J2);MONAT($J2)+$I2-1;TAG($J2))		Test					2	K2:K12
$K$2:$K$12	04.Bed.: Formel ist		=$H2>DATUM(JAHR($J2);MONAT($J2)+$I2-1;TAG($J2))		Test					2	K2:K12

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$K$2:$K$12	01.Bed.		Falsch					2	K2:K12
$K$2:$K$12	02.Bed.		Falsch					2	K2:K12
$K$2:$K$12	03.Bed.		Falsch					2	K2:K12
$K$2:$K$12	04.Bed.		Falsch					2	K2:K12

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

BoskoBiati · 08.03.2016, 15:55

Hallo,

Zitat:"K11" soll gelb sein, weil Differenz zu "J11" nur 23 Monate.

wieso das denn? Laut Deiner Tabelle sind das nur 13 Monate.

Auch die beiden anderen Zeilen ergeben für mich andere Werte!

Arbeitsblatt mit dem Namen 'Meßmittelverwaltung'
	H	I	J	K
1	Dat.letzte Prüfung	Interv.	ggf. einges.	nächste Prüfung
2	03.07.2012	24	13.01.2014	Jan. 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan. 17
5	11.03.2014	24	26.01.2015	Jan. 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz. 17
9	03.05.2013	24	19.03.2015	Mrz. 17
10	23.02.2015	24		Feb. 17
11	23.02.2015	24	19.03.2015	Feb. 17
12	02.04.2012	24	05.02.2015	Feb. 17

Zelle	bedingte Formatierung...	Format
K2	1: =DATEDIF(MAX($H2;$J2);HEUTE();"M")>=$I2	abc
K2	2: =DATEDIF(MAX($H2;$J2);HEUTE();"M")>=$I2-1	abc

Nitro · Registriert seit: 08.03.2016 Version(en): 2010

Hi,

sind es denn von 2015 bis 2017 keine 2 Jahre

März 2015-März 2017=2 Jahre=24 Monate

März 2015- Februar 2017=23 Monate

Vielleicht mache ich auch einen Denkfehler.

Außerdem sind die Spalten "K" und "J" als erstes auschlaggebend. Wenn in "J" keine Datum steht kommt"H" zu einsatz

Nitro · 08.03.2016, 16:56

(08.03.2016, 15:55)Rabe schrieb: Hi,

(08.03.2016, 15:37)Nitro schrieb: J4 < DATUM (JAHR(HEUTE());MONAT(HEUTE());TAG(HEUTE()))

vor dem J4 sollte auch ein = stehen.

das ist mir bewusst. Aber dann wurde nur J4 übernommen und der Rest der Formel fehlte :s

BoskoBiati · 08.03.2016, 17:52

Hallo,

erläutere mir doch bitte an Deiner Tabelle, anhand welcher Kriterien gefärbt werden soll:

Arbeitsblatt mit dem Namen 'Meßmittelverwaltung'
	H	I	J	K
2	03.07.2012	24	13.01.2014	Jan. 16
3	03.07.2012	24
4	11.03.2014	24	26.01.2015	Jan. 17
5	11.03.2014	24	26.01.2015	Jan. 17
6	28.09.2011	24
7	11.01.2012	24
8	03.05.2013	24	19.03.2015	Mrz. 17
9	03.05.2013	24	19.03.2015	Mrz. 17
10	23.02.2015	24		Feb. 17
11	23.02.2015	24	19.03.2015	Feb. 17
12	02.04.2012	24	05.02.2015	Feb. 17

Die Zeilen 2, 4, 5, 8 und 9 haben identische Abstände, werden aber überwiegend nicht gefärbt. Einzig die Zeilen 10 und 11 entsprechen Deinen Forderungen, 12 ist wieder nicht korrekt!

Login
Benutzername:
Passwort:	Passwort vergessen?
	Merken