Hilfe bei bedingter Formatierung

[galaxisspatz]

Moin,
schon mal im vorraus vielen Dank für die Hilfe.
Ich bin dabei einen Jahreskalender zu erstellen der möglichst wenig Anpassungen beim Jahreswechsel benötigt. Im Anhang die Datei. Ich komme leider bei zwei Punkten bei der bedingten Formatierung nicht weiter.
1) Feiertage
im Reiter Feiertag werden alle nötigen Feiertage errechnet und diese sollen im Reiter calendar die Zellen entsprechend färben (F10 - CR36, F46 - CR72, F82 - CR108, F118 - CR144). Dafür habe ich in Zeile 37, 73, 109 und 145 (Spalte F - CR) (ich werde jetzt immer nur noch von Zeile 37 sprechen)  folgende Formel die die bedingte Formatierung steuert (Änderung bezüglich der Spalten natürlich enthalten). Leider erkennt die Formel in Zeile 37 den Tag nach Karfreitag aber auch als "Feiertag" an ich weiß nur nicht warum.

Code:

=SUMME(ZÄHLENWENN(INDIREKT({"Feiertage!B2";"Feiertage!B5:B9";"Feiertage!B11";"Feiertage!B16:B17";"Feiertage!B20:B21"});F$41))

Code:

=F$73=1

1) Ferien
ich möchte im Reiter calendar die Ferien darstellen (Zeile 6-9, 42-45, 78-81 und 114-117, Spalte F - CR), diese sind in den Reitern HH, NS, SH, MV dargestellt.
Dafür habe ich mir folgende Formel für die bedingten Formatierung zusammengebaut (als Beispiel für HH, Januar - März), leider werden auch wieder nicht alle Ferientage dargestellt.

Code:

=UND(ODER(UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;1;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;1;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;2;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;2;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;3;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;3;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;4;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;4;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;5;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;5;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;6;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;6;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;7;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;7;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;8;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;8;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;9;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;9;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);""));UND(F5>=WENNFEHLER(INDEX(HH!$B$2:$G$11;10;VERGLEICH($BV$1;HH!$B$1:$G$1;0));"");F5<=WENNFEHLER(INDEX(HH!$B$2:$G$11;10;VERGLEICH($BV$1;HH!$B$1:$G$1;0)+1);"")));ISTZAHL(F5))

Wenn jemand eine Idee hat vielen Dank

Gruß
Olaf

[steve1da]

Hola,
die Beschreibung passt nicht zur Datei.
In den genannten Zeilen steht nichts drin, die Formeln sind nicht in der bedingten Formatierung.
Die Ferien der Bundesländer in einzelne Blätter zu verteilen macht null Sinn, nur Aufwand.
Gruß,
steve1da

[galaxisspatz]

Moin steve1da,
ich hbae den Text angepasst und die Spalten hinzugefügt, sowohl bei Problem 1 als auch 2.

[steve1da]

Warum bei den Feiertagen nicht einfach:

Code:

=zählenwenn(Feiertage!$B$2:$B$21;F$5)

Für die Feiertage für Hamburg reicht auch:

Code:

=SUMMENPRODUKT((HH!$B$1:$F$1=$BV$1)*(G$5>=HH!$B$2:$F$10)*(G$5<=HH!$C$2:$G$10))

[HKindler]

Hi,

deine Wochende-Formel geht auch einfacher:

=WOCHENTAG(F$5;2)>5

Und den Feiertag bekommst du - allerdings umgedreht (also Feiertag=0, kein Feiertag=1) - mit

=NETTOARBEITSTAGE.INTL(F5;F5;"0000000";Feiertage!$B$2:$B$21)

Mit =--NICHT(obigeFormel) könnte man die 0en und 1en umdrehen.

Wenn es egal ist, dass die Wochenenden bei den Feiertagen mit ausgegeben werden, könnte man "0000000" auch durch 1 ersetzen. Die sieben Nullen bewirken nur, dass kein Tag als "frei" deklariert wird. Für nähere Infos siehe die Hilfe zu Nettoarbeitstage.intl.

[galaxisspatz]

Hallo steve1da 
danke Die Feiertagsproblematik hat sich damit erledigt, ich lösche einfach die Feiertage die nicht frei sind in den bundesländern raus und gut.

belibt noch Problem zwei. Ich habe deine Formel, für die bedingte Formatierung, etwas angepasst

Code:

=Wenn(ISTZAHL(F$5)=Wahr;SUMMENPRODUKT((HH!$B$1:$R$1=$BV$1)*(F$5>=HH!$B$2:$R$10)*(F$5<=HH!$C$2:$S$10));"")

Leider werden auch hier nicht alle Tage markiert. Im Anhang deie Datei mit den neuen Formeln.

Gruß
Olaf

---

Hi,

deine Wochende-Formel geht auch einfacher:

=WOCHENTAG(F$5;2)>5

Und den Feiertag bekommst du - allerdings umgedreht (also Feiertag=0, kein Feiertag=1) - mit

=NETTOARBEITSTAGE.INTL(F5;F5;"0000000";Feiertage!$B$2:$B$21)

Mit =--NICHT(obigeFormel) könnte man die 0en und 1en umdrehen.

Wenn es egal ist, dass die Wochenenden bei den Feiertagen mit ausgegeben werden, könnte man "0000000" auch durch 1 ersetzen. Die sieben Nullen bewirken nur, dass kein Tag als "frei" deklariert wird. Für nähere Infos siehe die Hilfe zu Nettoarbeitstage.intl.

Hallo HKindler,
danke für deine Hinweise, ich werde sie wahrscheinlich in der finalen  Version einbauen.

Gruß
Olaf

[steve1da]

Die können auch nicht alle markiert werden weil deine Datumswerte im Blatt HH teilweise als TEXT vorliegen, zB in B4 oder in B6.

[galaxisspatz]

Danke das war es.

[galaxisspatz]

Hi,
ich noch mal. So hier die "finale" Version. Es muss Indien noch einpflegen aber ich glaube das interessiert hier niemanden.
Alle Daten werden berechnet inkl. Ostern und Orthodoxes Ostern.

Danke nochmal für eure Hilfe.

Gruß
Olaf

[steve1da]

Noch ein Tipp: in der bedingten Formatierung braucht man kein Wenn(). Die Prüfung auf Istzahl() kannst du dir schenken