Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Bedingte Formatierung
#1
Hallo. Als Neuling in diesem Forum muss ich erst ein Kompliment aussprechen. Wie hier geholfen wird, ist allererste Sahne.
Ich habe eine Tabelle ( unten angehängt), in der habe ich in der Spalte I unterschiedliche Einträge.
In dieser Tabelle hätte ich gerne die Zellen in Spalte I bei denen das Datum in der Vergangenheit liegt, rot eingefärbt.
In meiner Beispieltabelle also die Zelle I12 und I16. Kann man das mit einer bedingten Formatierung machen?? Wenn ja, wie muss denn die Formel lauten ????
Oder gibt es einen anderen Ansatz den ein Excellaie wie ich hin bekomme ??
Danke für eure Hilfe

Hamburg

ABCDEFGHI
92TegerMaikAushilfemöchte ab 01.01.15 in Filiale A arbeiten
10
11
123HauerMichaelHalbtagUrlaub von 01.12.14 - 10.12.14
13
14
154BaumFlorianGanztagArbeitet von 01.01.14  - 31.12.2014 in Fil. A
16Urlaub 01.07.14 -14.07.14
17
185MeierJensAushilfe
19Urlaub am 15.12.14
20
216MüllerUweGanztag
22In Filiale B 01.01.15 - 01.01.16
23

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
to top
#2
Hi,

ich habe dir mal eine Lösung mit einer Hilfsspalte (kann ausgeblendet werden) gebastelt. Aus der Spalte "I" musst du zuerst das letzte Datum extrahieren, um es mit HEUTE() vergleichen zu können. Wichtig ist aber, dass deine Daten immmer im selben Format, z.B. TT.MM.JJ, geschrieben werden. In deinem Beispiel hast du in Zelle I15 das zweite Datum im Format TT.MM.JJJJ geschrieben.

Tabelle2

BCDEFGHIJ
9TegerMaikAushilfemöchte ab 01.01.15 in Filiale A arbeiten01.01.2015
10
11
12HauerMichaelHalbtagUrlaub von 01.12.14 - 10.12.1410.12.2014
13
14
15BaumFlorianGanztagArbeitet von 01.01.14  - 31.12.14 in Fil. A31.12.2014
16Urlaub 01.07.14 - 14.07.1414.07.2014
17
18MeierJensAushilfe
19Urlaub am 15.12.1415.12.2014
20
21MüllerUweGanztag
22In Filiale B 01.01.15 - 01.01.1601.01.2016
23
Formeln der Tabelle
ZelleFormel
J9{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;"")}
J10{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;"")}
J11{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;"")}
J12{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;"")}
J13{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;"")}
J14{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;"")}
J15{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;"")}
J16{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;"")}
J17{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I17;MAX(ISTZAHL(TEIL(I17;SPALTE(17:17);1)*1)*SPALTE(17:17)));VERGLEICH(1;ISTZAHL(TEIL(I17&0;SPALTE(17:17);1)*1)*1;0);LÄNGE(I17));8))*1;"")}
J18{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I18;MAX(ISTZAHL(TEIL(I18;SPALTE(18:18);1)*1)*SPALTE(18:18)));VERGLEICH(1;ISTZAHL(TEIL(I18&0;SPALTE(18:18);1)*1)*1;0);LÄNGE(I18));8))*1;"")}
J19{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I19;MAX(ISTZAHL(TEIL(I19;SPALTE(19:19);1)*1)*SPALTE(19:19)));VERGLEICH(1;ISTZAHL(TEIL(I19&0;SPALTE(19:19);1)*1)*1;0);LÄNGE(I19));8))*1;"")}
J20{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I20;MAX(ISTZAHL(TEIL(I20;SPALTE(20:20);1)*1)*SPALTE(20:20)));VERGLEICH(1;ISTZAHL(TEIL(I20&0;SPALTE(20:20);1)*1)*1;0);LÄNGE(I20));8))*1;"")}
J21{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I21;MAX(ISTZAHL(TEIL(I21;SPALTE(21:21);1)*1)*SPALTE(21:21)));VERGLEICH(1;ISTZAHL(TEIL(I21&0;SPALTE(21:21);1)*1)*1;0);LÄNGE(I21));8))*1;"")}
J22{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I22;MAX(ISTZAHL(TEIL(I22;SPALTE(22:22);1)*1)*SPALTE(22:22)));VERGLEICH(1;ISTZAHL(TEIL(I22&0;SPALTE(22:22);1)*1)*1;0);LÄNGE(I22));8))*1;"")}
J23{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I23;MAX(ISTZAHL(TEIL(I23;SPALTE(23:23);1)*1)*SPALTE(23:23)));VERGLEICH(1;ISTZAHL(TEIL(I23&0;SPALTE(23:23);1)*1)*1;0);LÄNGE(I23));8))*1;"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I91. / Formel ist =$J9<HEUTE()Abc
I101. / Formel ist =$J9<HEUTE()Abc
I111. / Formel ist =$J9<HEUTE()Abc
I121. / Formel ist =$J9<HEUTE()Abc
I131. / Formel ist =$J9<HEUTE()Abc
I141. / Formel ist =$J9<HEUTE()Abc
I151. / Formel ist =$J9<HEUTE()Abc
I161. / Formel ist =$J9<HEUTE()Abc
I171. / Formel ist =$J9<HEUTE()Abc
I181. / Formel ist =$J9<HEUTE()Abc
I191. / Formel ist =$J9<HEUTE()Abc
I201. / Formel ist =$J9<HEUTE()Abc
I211. / Formel ist =$J9<HEUTE()Abc
I221. / Formel ist =$J9<HEUTE()Abc
I231. / Formel ist =$J9<HEUTE()Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • schadre
to top
#3
Hallo,

oder diese Formel in die bedingte Formatierung:

Code:
=ISTZAHL(SUCHEN("Urlaub";I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#4
Hi Edgar,

mit der Formel direkt in der bF spart man sich zwar (m)eine Hilfsspalte. Aber...

Dadurch, dass du ein bestimmtes Suchkriterium (hier: "Urlaub") eingebaut hast, wirkt die bF nicht auf die anderen Einträge.

Das wollte der Fragesteller:

Zitat:Ich habe eine Tabelle ( unten angehängt), in der habe ich in der Spalte I unterschiedliche Einträge.
In dieser Tabelle hätte ich gerne die Zellen in Spalte I bei denen das Datum in der Vergangenheit liegt, rot eingefärbt.

Es können ja auch Daten dabei sein, die nicht vom Urlaub abhängen. Wink
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#5
Erst einmal danke für eure schnelle Antworten. Ihr seid der Hammer 28

WillWissen: Deine Lösung funktioniert einwandfrei. Supi

BoskoBiati : Den Einwand von WillWissen hätte ich auch gemacht. Es befinden sich nicht nur "Urlaubseinträge" in der Spalte, sondern eine Vielzahl von unterschiedlichen Angaben mit einem Datum. Kann man deinen Ansatz also noch abändern, oder funktioniert das nur mit einem Suchwort?

Beim ausprobieren hatte ich noch ein Problem. Habe die Bedingte Formatierung in die Zelle I9 eingetragen und dann mit "Kopieren----Einfügen---Inhalte einfügen... Einfügen Formate" in die anderen Zellen übertragen.
Das funktionierte auch. Leider übernimmt er dabei auch die Rahmenlinien, Schriftfarbe etc. aus der Zelle I9 für alle anderen Zellen.
Ist ja vom Grundsatz nachvollziehbar, da ich ja das Format übertragen. Aber ich hätte Rahmenlinien, Schriftgröße etc. gerne so wie bereits für die jeweilige Zelle eingestellt. Ist das der Preis den ich zahlen muss, oder gibt es einen anderen Trick um die bedingte Formatierung zu übertragen???
to top
#6
Hi,

markiere den gesamten Bereich, der die bedingte Formatierung aufnehmen soll. Dann erst gibst du die Formel im Fenster der bF ein. So bleiben deine ursprünglichen Formatierungen der Tabelle erhalten.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#7
Zitat:Es befinden sich nicht nur "Urlaubseinträge" in der Spalte, sondern eine Vielzahl von unterschiedlichen Angaben mit einem Datum. Kann man deinen Ansatz also noch abändern, oder funktioniert das nur mit einem Suchwort?

Zu diesem Teil deiner Frage könnte ich dir, Edgars Vorschlag adaptiert, diese flexible Lösung vorschlagen:

Tabelle2 (2)

IJ
9möchte ab 01.01.15 in Filiale A arbeitenfiliale
10
11
12Urlaub von 01.12.14 - 10.12.14
13
14
15Arbeitet von 01.01.14  - 31.12.14 in Fil. A
16Urlaub 01.07.14 - 14.07.14
17
18
19Urlaub am 15.12.14
20
21
22In Filiale B 01.01.13 - 01.01.14
23
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I91. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I101. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I111. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I121. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I131. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I141. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I151. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I161. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I171. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I181. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I191. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I201. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I211. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I221. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc
I231. / Formel ist =ISTZAHL(SUCHEN($J$9;I9))*(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1))*1<HEUTE())Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Anstelle des Wortes "Urlaub" in der Formel gibst du einen absoluten Zellbezug - in diesem Fall J9 - ein. Hier kannst du dann deinen Suchbegriff eintragen. Im obigen Beispiel habe ich mal das Wort Filiale gewählt. Es wird nur die Zelle markiert, die das vergangene Datum und den Suchbegriff enthält.

Wäre das eine Option für dich?

Nachtrag:

Bei dieser Lösung werden natürlich die anderen relevanten Vergangenheiten nicht berücksichtigt. Daneben habe ich noch einen erheblichen Performanceverlust gegenüber der Hilfsspaltenlösung festgestellt. Ob das nur bei mir so ist oder alle betrifft, kann ich natürlich nicht sagen.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#8
WillWissen vielen vielen Dank.

Erst einmal danke für den super Tipp bezüglich des übertragen der bedingten Formatierung. Klasse.

Bei der Frage mit dem Suchbegriff habe ich mich wohl missverständlich ausgedrückt. Ich möchte keinen Suchbegriff, sondern ich hatte es bei dem Lösungsansatz von BoskoBiati so verstanden, das man hierbei einen Suchbegriff unbedingt benötigt.
to top
#9
Hallo,

1. wurden nur die Zellen I12 und I16 als formatierbar genannt, was den Schluß nahelegt, dass es nur um Urlaub geht.
2. ist die dann natürlich die Frage, was passiert, wenn es zwei Datumswerte gibt, von denen einer in der Zukunft liegt (I15)
3. Werfen die Daten ein Problem auf, weil sie das Zeichen 160 enthalten, was Auswertungen stört (ggf. aber auch hilfreich sein könnte, Frage 2)

Hier mal eine Formel, die das berücksichtigt:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHI
992   TegerMaikAushilfemöchte ab 01.01.15 in Filiale A arbeiten
1010       
1111      
12123   HauerMichaelHalbtagUrlaub von 01.12.14 - 10.12.14
1313       
1414      
15154   BaumFlorianGanztagArbeitet von 01.01.14  - 31.12.2014 in Fil. A
1616      Urlaub 01.07.14 -14.07.14
1717      
18185   MeierJensAushilfe 
1919      Urlaub am 15.12.14
2020      
21216   MüllerUweGanztag 
2222      In Filiale B 01.01.15 - 01.01.16
2323      
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Code:
=WECHSELN(GLÄTTEN(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));SUCHEN("-";TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9))&"-")-1));ZEICHEN(160);"")*1<HEUTE()
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Benutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • schadre
to top
#10
Hi Edgar,

zuerst einmal: deine neue Formel für die bF löst bei mir keine Reaktion aus. Ich vermute, du hast sie bei dir ausprobiert? An was kann's dann liegen?

Zitat:1. wurden nur die Zellen I12 und I16 als formatierbar genannt, was den Schluß nahelegt, dass es nur um Urlaub geht.

War eigentlich deutlich Wink

Zitat:In dieser Tabelle hätte ich gerne die Zellen in Spalte I bei denen das Datum in der Vergangenheit liegt, rot eingefärbt.

Zitat:2. ist die dann natürlich die Frage, was passiert, wenn es zwei Datumswerte gibt, von denen einer in der Zukunft liegt (I15)

Bei den vom Fragesteller vorgetragenen Beispielen kann es sich im Prinzip, falls ein Datum zukünftig ist, nur um das zweite Datum handeln. Sollte für diesen Fall ebenfalls eine bF gewünscht sein, schlage ich wieder die Hilfsspaltenlösung vor - dieses Mal mit zwei HSp (die natürlich wieder ausgeblendet werden können). Bei meiner Lösung wird die Zelle eingefärbt, bei der mindestens eines der Daten in der Vergangenheit liegt.

Zitat:3. Werfen die Daten ein Problem auf, weil sie das Zeichen 160 enthalten, was Auswertungen stört (ggf. aber auch hilfreich sein könnte, Frage 2)

Das Problem taucht nach meinem Dafürhalten mit der HSp-Lösung nicht auf (lasse mich natürlich gerne vom Gegenteil überzeugen Wink )

Tabelle2

IJKL
9möchte ab 01.01.15 in Filiale A arbeiten01.01.201501.01.2015
10
11
12Urlaub von 15.12.14 - 10.01.1515.12.201410.01.2015beide Daten zukünftig
13
14
15Arbeitet von 12.12.14  - 31.12.14 in Fil. A12.12.201431.12.2014erstes Datum Vergangenheit
16Urlaub 01.07.14 - 14.07.1401.07.201414.07.2014
17
18
19Urlaub am 13.12.1413.12.201413.12.2014Vergangenheit
20
21
22In Filiale B 01.01.13- 01.01.1401.01.201301.01.2014beide Daten Vergangenheit
23
Formeln der Tabelle
ZelleFormel
J9{=WENNFEHLER(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8)*1;"")}
K9{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;"")}
J10{=WENNFEHLER(LINKS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8)*1;"")}
K10{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;"")}
J11{=WENNFEHLER(LINKS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8)*1;"")}
K11{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;"")}
J12{=WENNFEHLER(LINKS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8)*1;"")}
K12{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;"")}
J13{=WENNFEHLER(LINKS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8)*1;"")}
K13{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;"")}
J14{=WENNFEHLER(LINKS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8)*1;"")}
K14{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;"")}
J15{=WENNFEHLER(LINKS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8)*1;"")}
K15{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;"")}
J16{=WENNFEHLER(LINKS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8)*1;"")}
K16{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;"")}
J17{=WENNFEHLER(LINKS(TEIL(LINKS(I17;MAX(ISTZAHL(TEIL(I17;SPALTE(17:17);1)*1)*SPALTE(17:17)));VERGLEICH(1;ISTZAHL(TEIL(I17&0;SPALTE(17:17);1)*1)*1;0);LÄNGE(I17));8)*1;"")}
K17{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I17;MAX(ISTZAHL(TEIL(I17;SPALTE(17:17);1)*1)*SPALTE(17:17)));VERGLEICH(1;ISTZAHL(TEIL(I17&0;SPALTE(17:17);1)*1)*1;0);LÄNGE(I17));8))*1;"")}
J18{=WENNFEHLER(LINKS(TEIL(LINKS(I18;MAX(ISTZAHL(TEIL(I18;SPALTE(18:18);1)*1)*SPALTE(18:18)));VERGLEICH(1;ISTZAHL(TEIL(I18&0;SPALTE(18:18);1)*1)*1;0);LÄNGE(I18));8)*1;"")}
K18{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I18;MAX(ISTZAHL(TEIL(I18;SPALTE(18:18);1)*1)*SPALTE(18:18)));VERGLEICH(1;ISTZAHL(TEIL(I18&0;SPALTE(18:18);1)*1)*1;0);LÄNGE(I18));8))*1;"")}
J19{=WENNFEHLER(LINKS(TEIL(LINKS(I19;MAX(ISTZAHL(TEIL(I19;SPALTE(19:19);1)*1)*SPALTE(19:19)));VERGLEICH(1;ISTZAHL(TEIL(I19&0;SPALTE(19:19);1)*1)*1;0);LÄNGE(I19));8)*1;"")}
K19{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I19;MAX(ISTZAHL(TEIL(I19;SPALTE(19:19);1)*1)*SPALTE(19:19)));VERGLEICH(1;ISTZAHL(TEIL(I19&0;SPALTE(19:19);1)*1)*1;0);LÄNGE(I19));8))*1;"")}
J20{=WENNFEHLER(LINKS(TEIL(LINKS(I20;MAX(ISTZAHL(TEIL(I20;SPALTE(20:20);1)*1)*SPALTE(20:20)));VERGLEICH(1;ISTZAHL(TEIL(I20&0;SPALTE(20:20);1)*1)*1;0);LÄNGE(I20));8)*1;"")}
K20{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I20;MAX(ISTZAHL(TEIL(I20;SPALTE(20:20);1)*1)*SPALTE(20:20)));VERGLEICH(1;ISTZAHL(TEIL(I20&0;SPALTE(20:20);1)*1)*1;0);LÄNGE(I20));8))*1;"")}
J21{=WENNFEHLER(LINKS(TEIL(LINKS(I21;MAX(ISTZAHL(TEIL(I21;SPALTE(21:21);1)*1)*SPALTE(21:21)));VERGLEICH(1;ISTZAHL(TEIL(I21&0;SPALTE(21:21);1)*1)*1;0);LÄNGE(I21));8)*1;"")}
K21{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I21;MAX(ISTZAHL(TEIL(I21;SPALTE(21:21);1)*1)*SPALTE(21:21)));VERGLEICH(1;ISTZAHL(TEIL(I21&0;SPALTE(21:21);1)*1)*1;0);LÄNGE(I21));8))*1;"")}
J22{=WENNFEHLER(LINKS(TEIL(LINKS(I22;MAX(ISTZAHL(TEIL(I22;SPALTE(22:22);1)*1)*SPALTE(22:22)));VERGLEICH(1;ISTZAHL(TEIL(I22&0;SPALTE(22:22);1)*1)*1;0);LÄNGE(I22));8)*1;"")}
K22{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I22;MAX(ISTZAHL(TEIL(I22;SPALTE(22:22);1)*1)*SPALTE(22:22)));VERGLEICH(1;ISTZAHL(TEIL(I22&0;SPALTE(22:22);1)*1)*1;0);LÄNGE(I22));8))*1;"")}
J23{=WENNFEHLER(LINKS(TEIL(LINKS(I23;MAX(ISTZAHL(TEIL(I23;SPALTE(23:23);1)*1)*SPALTE(23:23)));VERGLEICH(1;ISTZAHL(TEIL(I23&0;SPALTE(23:23);1)*1)*1;0);LÄNGE(I23));8)*1;"")}
K23{=WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I23;MAX(ISTZAHL(TEIL(I23;SPALTE(23:23);1)*1)*SPALTE(23:23)));VERGLEICH(1;ISTZAHL(TEIL(I23&0;SPALTE(23:23);1)*1)*1;0);LÄNGE(I23));8))*1;"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I91. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I101. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I111. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I121. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I131. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I141. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I151. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I161. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I171. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I181. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I191. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I201. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I211. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I221. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc
I231. / Formel ist =ODER($J9<HEUTE();$K9<HEUTE())Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste