Prüferampel -bedingte Formatierung

[horo]

Hallo zusammen,
ich komme nicht weiter. Habe bestimmt einiges auch zu umständlich gemacht. Doch ich brauche eure Hilfe.
Habe eine Tabelle erstellt und ich komme mit der bedinten Formatierung nicht so richtig weiter.
In den Spalten I3 bis K3 soll der Prüfungsstatus angezeigt werden, d. h. in Sp I noch nicht begonne "rot", in Sp J begonnen, soll es gelb werden, und in Sp K erledigt, soll es grün werden. In Spalte L soll die Schrift rot werden, wenn man mehr Zeit als die in Spalte F und Q (identisch) vorgegeben ist benötigt hat. Spalten M bis  R will ich dann später ausblenden, sollen als Hilfsrechnung dienen. Ob notwendig ????? Alles was Grau ist soll später schreibgeschütz werden und die blau markierten Zellen werden nur vom Prüfkoordinator verwendet. Der Prüfer soll nur in Spalte D und E ein Datum eintragen.
Ampel

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			Prüfungs-														Pflicharbeitstage bzw. Richtwerte
2	Objekt	Form	Vorbereitung	Beginn der Prüfung	Ende	Zeit	Abgabetermin	Beginn der Prüfung		Status		Ende der Prüfung				ab hier beginnt die Vorbereitung zur Prüfung	ab dann muss die Prüfung beginnen	bis hier ist die Prüfung abzuschließen
3	Fahrkosten	Istkostenabrechnung	05.01.	03.01.	07.01.	3	09.01.	vor 6 Tage	n	n	n	nach 4 Tage	4	6	2	5	3	1
4	Monatsabschluss	Istkostenabrechnung	08.02.	12.02.	14.02.	5	15.02.	vor 3 Tage	n	n	n	nach 2 Tage	3	3	1	8	5	1
5	Jahresabschluss	Jahresabschlussprüfung	27.01.	28.02.	19.03.	40	20.03.	vor 20 Tage	n	n	n	nach 19 Tage	20	20	1	53	40	1
6									n	n	n
7									n	n	n

Formeln der Tabelle

Zelle Formel C3 =G3-(F3+(F3*0,3)) H3 ="vor "&(DATEDIF(D3;G3;"yd")& " Tage") L3 ="nach "&(DATEDIF(D3;E3;"yd")&" Tage") M3 =G3-C3 N3 =G3-D3 O3 =G3-E3 P3 =VRUNDEN(G3-C3;2,5) Q3 =F3 R3 =Q3-F3+1 C4 =G4-(F4+(F4*0,3)) H4 ="vor "&(DATEDIF(D4;G4;"yd")& " Tage") L4 ="nach "&(DATEDIF(D4;E4;"yd")&" Tage") M4 =G4-D4 N4 =G4-D4 O4 =G4-E4 P4 =VRUNDEN(G4-C4;2,5) Q4 =F4 R4 =Q4-F4+1 C5 =G5-(F5+(F5*0,3)) H5 ="vor "&(DATEDIF(D5;G5;"yd")& " Tage") L5 ="nach "&(DATEDIF(D5;E5;"yd")&" Tage") M5 =G5-D5 N5 =G5-D5 O5 =G5-E5 P5 =VRUNDEN(G5-C5;2,5) Q5 =F5 R5 =Q5-F5+1

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I3 1. / Formel ist =UND(WENN($M$3<$P$3;ISTZAHL($M$3))) Abc I3 2. / Formel ist =UND(WENN($N$3<$P$3;$N$3>$Q$3;ISTZAHL($N$3))) Abc I3 3. / Formel ist =UND(WENN($O$3>=$R$3;$O$3<$Q$3;ISTZAHL($O$3))) Abc L3 1. / Formel ist =UND($L$3>$F$3;) Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Sicherlich hab ihr noch Fragen dazu. Ich hoffe habe alles verständlich geschrieben und ihr könnt mir helfen, oder es einfacher und besser darstellen.
Beste Grüße Roland

[steve1da]

Hola,

was genau klappt denn und was genau klappt nicht?

Eine Beispieltabelle im xlsx Format wäre sicherlich hilfreicher.

Wann die Ampelfarben erscheinen sollen hast du nicht beschrieben.

Gruß,
steve1da

[schauan]

Hallo Horo,

Für die Ampel würde ich nur eine Spalte nutzen. Ob Du für Berechnungen eine Hilfsspalte nutzen willst, ist Deine Entscheidung.
Hier mal eine Prinzipdarstellung.
In Abhängigkeit des Verhältnisses A zu B hab ich -1, 0 und 1 als Ergebnis definiert.
Bei der Bedingten Formatierung gibt es auch eine Ampelfunktion. Hier habe ich die Werte 1 und 0 als Grenzwerte für grün und gelb genommen, was kleiner 0 ist, bleibt dann für Rot übrig.
Die Zahlen kannst Du mit einer benutzerdefinierten Formatierung unsichtbar schalten, gib z.B. nur ein Semikolon ein.

   

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
2	1	0	-1

Zelle	Formel
C2	=WENN(B2>A2;1;WENN(B2=A2;0;-1))

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

[horo]

Hallo,
was ich nicht hinbekomme ist, in Spalte L die Schrift "rot" darzustellen, wenn der Prüfer länger als die vorgegebene Zeit, wie in Spalte F festgelegt ist, benötigt. Ich habe die Sp P,Q u. R geändert. Vielleicht hilft es. Wie gesagt die "blau" markierten Zellen, bedient der Prüfkoordinator. Neu soll laut Koordinator, soll auch die Spalte c vom Prüfer ausgefüllt werden.
Die Spalte I bzw. I3 (die Ampel) soll "rot" zeigen, wenn die Vorbereitung nach dem festgelegten Datum, wie in Zelle P3 errechnet, begonnen wurde. Grün soll die Ampel in I3 werden, wenn vor dem Termin, wie aus Zelle P3 errechnet begonnen wurde.
Des Weiteren soll die Ampel in Zelle I3 bis K3 "rot" zeigen, wenn kein Datum in C3, D3 und E3 eingetragen ist.
Ich stelle die neue Tabelle rein.
Ampel

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Prüfungs-

Pflicharbeitstage bzw. Richtwerte

2

Objekt

Form

Vorbereitung

Beginn der Prüfung

Ende

Zeit

Abgabetermin

Beginn der Prüfung

Status

Ende der Prüfung

ab hier beginnt die Vorbereitung zur Prüfung

ab dann muss die Prüfung beginnen

bis hier ist die Prüfung abzuschließen

3

Fahrkosten

Istkostenabrechnung

06.01.

10.01.

3

09.01.

vor 3 Tage

n

n

n

nach 4 Tage

3

3

-1

05.01.2015

06.01.2015

08.01.2015

4

Monatsabschluss

Istkostenabrechnung

12.02.

14.02.

5

15.02.

vor 3 Tage

n

n

n

nach 2 Tage

3

3

1

09.02.2015

10.02.2015

14.02.2015

5

Jahresabschluss

Jahresabschlussprüfung

28.02.

19.03.

40

20.03.

vor 20 Tage

n

n

n

nach 19 Tage

20

20

1

02.02.2015

08.02.2015

19.03.2015

6

n

n

n

7

n

n

n

Formeln der Tabelle

Zelle Formel H3 ="vor "&(DATEDIF(D3;G3;"yd")& " Tage") L3 ="nach "&(DATEDIF(D3;E3;"yd")&" Tage") M3 =G3-D3 N3 =G3-D3 O3 =G3-E3 P3 =G3-(F3+(F3*0,15)) Q3 =G3-F3 R3 =G3-1 H4 ="vor "&(DATEDIF(D4;G4;"yd")& " Tage") L4 ="nach "&(DATEDIF(D4;E4;"yd")&" Tage") M4 =G4-D4 N4 =G4-D4 O4 =G4-E4 P4 =G4-(F4+(F4*0,15)) Q4 =G4-F4 R4 =G4-1 H5 ="vor "&(DATEDIF(D5;G5;"yd")& " Tage") L5 ="nach "&(DATEDIF(D5;E5;"yd")&" Tage") M5 =G5-D5 N5 =G5-D5 O5 =G5-E5 P5 =G5-(F5+(F5*0,15)) Q5 =G5-F5 R5 =G5-1

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format L3 1. / Formel ist =UND(WENN($L$3>$F$3;)) Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Da es noch mehr bedingte Formatierungen geben soll, so in J3 und K3, würde ich diese extra nennen.
Zu hause verwende ich Excel 2007 und im Betreib Excel 2010.
Da ich die Tabelle zu Hause erstelle, wird einiges aus Excel 2010 sicherlich nicht funktionieren.
Beste Grüße
Roland

[schauan]

Hallöchen,

in Deiner Bedingung für die Farbe vergleichst Du einen Text mit einer Zahl. Das geht nicht auf.
Die Zahl in L ergibt sich ja aus dem Datedif... Du müsstest also mit dem Datedif... vergleichen.

[horo]

Hallo,
ja ok, doch da bin ich unsicher und komme nicht auf die Formel.
Kannst du helfen?
Gruß

[steve1da]

Hola,

so:

Code:

=DATEDIF(D3;E3;"yd")>$F3

Gruß,
steve1da

[horo]

Hallo,
ja danke, es hat prima geklappt. Hast du auch eine Lösung für die anderen bF, di eich oben beschrieben habe bzw. kann ich das noch anders machen?
Gruß

[steve1da]

Hola,

ungetestet:

Code:

=oder(und($C3="";$D3="";$E3="");$D3>$P3)

für rot

Code:

=$D3<$P3

für grün.

Gruß,
steve1da

[horo]

Hi,
so wollte ich das formatieren, doch hab keine Lösung. Sehe den Fehler nicht.
Die Zellen C3 bis E3 sind leer. In Zelle I3 muß die Ampel auf "rot" stehen. Sobald ein Datum in C3 eingetragen wird, soll die Ampel in Zelle I3 auf Grün umschalten, wenn vor oder am Datum in P3 mit der Prüfung begonnen wurde, sonst rot bleiben. In Zelle J3 soll die Ampel "weiß" bleiben und erst "rot oder grün" werden, wenn ein Datum in Zelle D3 eingetragen wird. Das Datum sollte größer oder gleich als wie in Q3 sein, sonnst in Zelle J3 rot bleiben und in Zelle K3 soll es grün werden, wenn in Zelle E3 ein Datum eingetragen wird, das kleiner oder gleich R3 ist. Ist die Prüfung aber erst nach dem Datum in R3 beendet steht die Ampel in Zelle K3 auf gelb oder helldrün. Geht das zu machen?
Hab ein Teil angefangen und komme nicht weiter. Es funktioniert nicht. Sicherlich nicht richtig. Komme nicht weiter. Das ist sicherlich zu viel?

Ampel

	I
3	n

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I3 1. / Formel ist =WENN(ISTLEER($C$3);"";DATUM(JAHR($C$3)+1;MONAT($C$3);TAG($C$3))) Abc I3 2. / Formel ist =WENN($C$3>$P$3;"";) Abc I3 3. / Formel ist =WENN($C$3<$P$3;"";) Abc I3 4. / Formel ist =UND(WENN($C$3=0;"";)) Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Beste Grüße