sorry, ....
Brauchst dich doch nicht zu Entschuldigen. Selbst wenn Du 2 Bundesländer verwalten müsstest, so würde man dies hinbekommen, man muss dann nur im Vorfeld alles abklären.
die Ferienzeilen dienen nur als Hinweis für die Mitarbeiter
Gut zu wissen.
da werde ich die Ferien einfach händisch einfärben, damit sie bei der Urlaubspalnung parat sind.
Brauchst Du doch nicht. Es müssen lediglich 2 seperate Datenbereiche für die Ferien angelegt werden damit eine Formel darauf zugreifen kann. Der Rest geschiet automatisch.
Es geht ausschließlich um die Berücksichtigung der Feiertage in Niedersachsen!!!
Auch gut zu wissen.
Da wird für die Feiertage auch eine seperate Tabelle angelegt, worauf eine Formel zugreift. Rest geht dann auch automatisch.
So, und nun zu der angehängten Datei von mir.
Eins vorweg, ich habe unterhalb der "Kalenderzeile" noch ein paar weitere Zeilen eingefügt damit meine kommenden Formeln dort untergebracht werden können. Diese zusätzlichen Zeilen kannst Du bei bedarf Ausblenden.
Da ja selbst bei nur einem Bundesland die Bedingung vorgegeben ist: *Urlaubstage am Wochenende und an Feiertagen nicht mit aufsummieren*, habe ich folgende Formel eingesetzt.
Und zwar in der Zeile 4
Code: Alles auswählen
=WENN(ZÄHLENWENN(Feiertage.$B$5:$B$9;B$2);1;WENN(WOCHENTAG(B$2;2)>5;1;""))
Mit ZÄHLENWENN wird überprüft ob das zu vergleichende Datum in der Feiertagsliste vorhanden ist, siehe Tabellenblatt *Feiertage*. Wenn ja, dann wird eine 1 ausgegeben.
Sollte das zu vergleichende Datum kein Feiertag sein, dann wird überprüft ob es sich um ein Wochenende handelt, WOCHENTAG(B$2;2). Die 2 in WOCHENTAG ztählt die Tage von Montag mit 1 bis Sonntag mit 7. Dadurch ist es einfacher ein Wochenende zu bestimmen, >5. Und wenn es ein Wochende ist, wird auch eine 1 ausgegeben.
Jetzt müssen nur noch die zutreffenden Bedingungen summiert werden, und das geschieht mit dieser Formel.
SUMMENPRODUKT zählt jetzt nur die Zellen zusammen die;
1.
ein "u" beinhalten.
und 2.
in der Zeile 4 Leer sind "".
ACHTUNG! SEHR WICHTIG!
In den Zellen dürfen nur kleine u eingetragen werden, bei großen U zählt SUMMENPRODUKT Falsch.
Die farbliche Markierung für Wochende und Feiertag habe ich nicht geändert.
Damit wäre das Thema Urlaub erledigt.
Kommen wir zu den Ferien.
In der Zeile 5 habe ich folgende Formel.
Code: Alles auswählen
=SUMMENPRODUKT(B$2>=$Ferien.$B$5:$B$10;B$2<=$Ferien.$C$5:$C$10)
Im Tabellenblatt *Ferien* gibt es 2 Bereiche für die Ferien in Niedersachsen und in NRW. Beide sind aber vom Aufbau identisch. Das heißt, das der Ferienbeginn in der Spalte mit der Überschrift VON rein kommt, und das Ferienende in die Spalte BIS.
Anders ausgedrückt.
In Spalte VON kommt der erste Ferientag, und in Spalte BIS der letzte Ferientag.
Und wenn es sich nur um einen einzigen Ferientag handelt, sie NRW, dann wird dieser einzelne Ferientag bei VON als auch bei BIS eingetragen. Das musst du dir unbedingt merken!
SUMMENPRODUKT durchsucht also in der Ferienliste ob das zu vergleichende Datum zwischen VON und BIS liegt, und wenn ja, dann wird eine 1 ausgegeben.
Und diese Zeile 5 fragt die Bedingte Formatierung ab, und wenn dort zu dem vergleichendem Datum eine 1 steht, dann wird das Datum als Feriendatum markiert.
Das war im groben die Beschreibung für die Ferien in Niedersachsen. Und genau so verhällt es sich mit den Ferien für NRW, einzig der Unterschied ein anderer Feriendatenbereich und die Formel dafür steht in der Zeile 6.
Du kannst natürlich die Farben für die Ferien nach Lust und Laune ändern, gar so wie es dir gefällt. Ich habe da mal nur 2 farben willkürich eingestellt.
Die Daten in den Tabellenblättern *Ferien* und *Feiertage* sind momentan für das Jahr 2016 eingestellt. Für das Jahr 2017 musst Du dort die Daten ändern.
Und ich habe dort die Bereiche farblich markiert wo Du die Daten eingeben kannst ohne die Formeln ändern zu müssen.
Jetzt noch etwas allgemeines.
In der Zelle A1, hattest Du von Hand ja eingegeben: *Urlaub April 2017*. Und im Anschluß musstest Du das Startdatum in der Zelle B4 von Hand eintragen. Das habe ich jetzt geändert, da einfacher.
In A1 gibst Du das gewünschte Datum ein, z.B. 1.8.2016, und schon wird dein Titeltext dort angezeigt und die Kalenderzeilen passen sich automatisch diesem Datu an. Und Fäädddiscccch
Sollte aus einem mir unerklärlichen Grunde die Datei bei dir nicht korrekt funktionieren, so sag bescheid, aber nenne dann auch deine LO Version und zwar die ganz genaue Versionsnummer.
Gruß
balu