Office: (Office 2016) Kalenderblatt - Bereich auslesen und Datum(sbereich) generieren

Moin moin zusammen,

ich habe mal wieder eine etwas komplexere Thematik und komme leider nicht weiter.

Mein Ziel ist es, eine als Kalender aufgebaute Tabelle in eine Übersichtlichere Darstellung umzuwandeln. Leider bin ich des VBA nicht mächtig, weshalb ich mich auf die "klassischen" Formeln konzentrieren muss.

Meine aktuelle Datei sieht aus wie im Anhang. Den Anhang Fehlzeitenuebersicht_2019.xlsm betrachten

Um es nicht zu komplex zu machen, möchte ich nur die Eintragungen für "Urlaub" sprich die "1" auswerten.

Meine Formel soll von der Logik her wie folgt aussehen (s. Zelle AO9 im Reiter "Mustermann, Max): =WENN(L9=1;DATUM(JAHR(HEUTE());MONAT(A9);TAG(L6));"")

Nun soll das ganze aber nicht nur für einzelne Zellen, sondern den jew. gesamten Monat funktionieren. Idealerweise also:

=WENN(B9:AF9=1;DATUM(JAHR(HEUTE());MONAT(A9);TAG(B6:AF6));""). Leider funktioniert das mit dieser Formel jedoch nicht.

Damit nicht genug, sollen auch noch zusammenhängende Zeiträume als Datumsbereich ausgegeben werden, also z.B.: 11.03. - 15.03.2020

Ich stehe momentan einfach auf dem Schlauch, wie ich das am besten hinbekomme. Kann mir hier jemand helfen?

edit: ach ja, zu allem Überfluss muss das ganze dann noch auf den Reiter "Übersicht" übertragen werden.

 

Hallo,

ich habe deine Excel-Datei modifiziert/ergänzt und zwar im Arbeitsblatt "Mustermann, Max":

1. Im Zellbereich AN7:AN18 habe ich 12 DropDowns eingefügt, sodass dort die Fehlzeitenarten (1/K/F/…/A) ausgewählt werden können.
2. Im Zellbereich AO7:BK18 habe ich 23x12 Matrixformeln eingefügt. Diese verwenden die DropDown-Einstellung aus Punkt 1) und listen die Tagesdaten auf, in denen diese Fehlzeitart im Bereich B7:AF18 eingetragen ist.
3. Im Zellbereich BL7:BL18 habe ich 12 benutzerdefinierte VBA-Funktionen eingefügt. Z.B. ist in Zelle BL12 die Funktion
   "=DatumsListe(AN12;A12:AF12)" enthalten. Der erste Parameter AN12 greift wieder auf die Fehlzeiten-DropDown aus Punkt 1) zu, während im 2. Parameter A12:AF12 die 32 Monats-Zellen der Jahresübersicht enthalten sind. Die Funktion liefert für die Fehlzeitart "1" den Ausdruck "11.-14., 17.-19., 21.6.2019". Bei Auswahl der Fehlzeitart "k" in AN12 erscheint in BL12 der Ausdruck "26.-27.6.2019".
4. In Zelle A25 habe ich noch eine weitere benutzerdefinierte VBA-Funktion eingefügt "=Ostern(A24)". Sie errechnet zur Jahreseingabe in A24 das Osterdatum (für den Zeitraum 1900-9999). In VBA würde sie sogar für den Zeitraum 100-9999 funktionieren.

Das stimmt nicht. Sie funktioniert. Du musst sie nur als Matrixfunktion eingeben, d.h. die Formel ist in die Formeleditierzeile von Excel einzugeben und die Formeleingabe ist dann nicht bloß mit der EINGABE-Taste abzuschließen, sondern mit der Tastenkombination Strg+Umschalt+EINGABE. Daraufhin erscheint die Formel in geschweiften Klammern eingeschlossen:
{=WENN(B9:AF9=1;DATUM(JAHR(HEUTE());MONAT(A9); TAG(B6:AF6));"")}

 

Hi Anton,

vielen, vielen Dank. Das hat mir extrem weitergeholfen.

Viele Grüße

 

Ich habe noch eine ergänzende Frage zu meinem obigen Thema:

Ich habe nun noch eine Auswahl der Feiertage pro Bundesland in meine Tabelle eingebaut und möchte, dass sich die angezeigte Feiertagsliste entsprechend dem ausgewählten Bundesland aktualisiert. Das ist besonders für die Berechnung der Nettoarbeitstage je nach Bundesland wichtig. Ergo habe ich -am Beispiel des Feiertags Hlg. 3 Könige in Bayern und Ba-Wü- folgende Formel verwendet:

{=WENN(UND(ODER(wertAuswahlBundesland=$F$3:$U$3);ODER(F5:U5="X"));DATUM(JAHR(HEUTE());1;6);"")}

- "wertAuswahlBundesland" verweist auf mein Dropdownfeld
- der Bereich F3:U3 umfasst die Bundesländer in der Übersichtstabelle
- der Bereich F5:U5 ist die Zeile in welcher ein X steht, falls das Bundesland an diesem Tag einen Feiertag hat.

Leider komme ich da nicht mehr weiter. Entweder Excel zeigt mir tatsächlich das Formelergebnis (06.01.2020) an oder es macht das Feld leer. Durch die Änderung der Auswahl im Dropdown ändert sich aber nichts.

Habt ihr eine Idee?

Vielen Dank vorab und Grüße

der_Bo

 

Hallo,

leider kann man mit den UND(..) bzw. ODER(…)-Funktionen keine Matrixfunktionen bauen.

Aus diesem Grund habe ich dir in der beiliegenden Excel-Datei ein Arbeitsblatt "Feiertage" eingefügt, in dem sich

• 2 ListTabellen "TabFeiertage" und "TabBdLdFeiertage" befinden.
• Über die Zelle B4 (die das Jahr enthält) werden die Kalenderdaten der möglichen Feiertage der Spalte "TabFeiertage[Datum]" ermittelt.
• In Zelle B8 befindet sich ein Bundesländer-DropDown, das seine Daten aus der Kopfzeile "TabBdLdFeiertage[#Kopfzeilen]" bezieht. Damit kann in Zelle C8 die Tabellenspalte des ausgewählten Bundeslandes ermittelt werden.
• Weiters ist in Zelle B12 ein Feiertage-DropDown, das seine Daten aus der Spalte "TabFeiertage[Feiertag]" erhält, womit in Zelle C12 die Tabellenzeile des ausgewählten Feiertages bezogen werden kann.
• Damit kann in Zelle B13 der Feiertagsstatus aus der ListTabelle "TabBdLdFeiertage" ermittelt werden:
  "x" der Tag ist gesetzlicher Feiertag, "?" der Tag ist nur in einem Teilgebiet gesetzlicher Feiertag, "0" der Tag ist kein (gesetzlicher) Feiertag in diesem Bundesland.
• In Zelle B14 wird das Datum des Feiertags angezeigt (falls in B13 ein "x" oder "?" enthalten ist) oder es wird "---" angezeigt (falls in B13 ein "0" enthalten ist).

Ob die "x" bzw. "?" in den einzelnen Bundesländern wirklich exakt so zutreffen, das müsstest du im Einzelfall noch überprüfen. Ich habe die Daten aus dem Internet bezogen, da ich als Ösi die deutschen Details nicht genau kenne. Die Berechnung der Feiertagsdatumsangaben dürfte zutreffen.

Im Namensmanager habe ich die beiden Namen "ListeFeiertage" und "ListeBundesländer" eingefügt.

 

Hi Anton,

vielen Dank für die Lösung. Mal wieder hochgradig. Nur leider ein kleines Problem hierbei: Mittlerweile soll die Datei nicht mehr mit VBA angelegt sein. Daher habe ich mir eine Office-Vorlage geschnappt und diese ergänzt bzw. überarbeitet. Hintergrund ist auch, dass die Liste bundesweit eingesetzt werden soll und daher unterschiedliche Feiertage bei der Berechnung der verbrauchten Urlaubstage angesetzt werden müssen. Da dies verformelt ist, benötige ich also eine entsprechende Vorbereitungsformel, die beim Anlegen des Mitarbeiters und der damit verbundenen Auswahl des Bundeslandes die Feiertage, die nicht zutreffen ausblendet (daher meine obige Formel).

Zum besseren Verständnis habe ich jetzt mal meine Datei angehängt. Den Anhang Kopie von Fehlzeitenplaner neu_2020.xlsx betrachten


Vielleicht kannst du dir das mal anschauen?

Für Rückfragen einfach melden :-)
VG
der_Bo

 

Hallo,

ich habe deine Excel-Datei modifiziert und unten angefügt - wie du siehst, diesmal ohne VBA-Einsatz.

1. Dafür habe ich im Arbeitsblatt "gesetzl. Feiertage" die Bundesländerfeiertagsauflistung im Bereich F3:U23 umgewandelt in eine ListTabelle namens "BundesländerFeiertage". Die ListTabelle "Betriebsferien" in B3:C23 habe ich unverändert belassen.
2. Im Namensmanager habe ich den Namen "ListeBundesländer" mit der Formel =BundesländerFeiertage[#Kopfzeilen] eingefügt.
   Mit diesem Namen habe ich auch im Arbeitsblatt "Kalenderansicht" das Bundesländer-DropDown in Zelle T2 (wertAuswahlBundesland) formelmäßig angepasst.
3. Im Namensmanager habe ich ferner den Namen "NrAuswahlBundesland" mit der Formel =VERGLEICH(wertAuswahlBundesland;ListeBundesländer;0) eingefügt.
   Dieser Name ermöglicht es, mittels des vorhin erwähnten Bundesländer-DropDown (=wertAuswahlBundesland) die zutreffende Spalte in der Tabelle "BundesländerFeiertage" zu ermitteln.
4. Zu guter Letzt habe ich dann den entscheidenden Namen "BetriebsferienAuswahlBundesland" in den Namensmanager mit der Formel:
   =(Betriebsferien[Feiertage]*(INDEX(BundesländerFeiertage;0;NrAuswahlBundesland)="X""))
   eingefügt, die sich wie eine Matrixformel verhält. Wie sie sich verhält, habe ich nur zu Demonstrationszwecken im schräg durchstrichenen Bereich B30:B49 dargestellt. Durch diese Formel werden die für ein Bundesland (=wertAuswahlBundesland) nicht zutreffenden Feiertagsdaten als 0-Werte dargestellt.
   Den Effekt habe ich ferner im Bereich F27:G30 mit den beiden Funktionen NETTOARBEITSTAGE(…) bzw. NETTOARBEITSTAGE.INTL(…) gezeigt, wo beidesmal der Name "BetriebsferienAuswahlBundesland" zum Einsatz kommt.

Meine Ergänzungen im Arbeitsblatt "gesetzl. Feiertage" im Bereich B27:L50 können alle gelöscht werden.

 

Hello again,

nachdem ich die Datei nun in Verwendung habe (was ich ohne eure Hilfe niemals vollständig hinbekommen hätte), habe ich nun nochmal eine Luxus-Frage:

Ich habe im Reiter "Abwesenheitsliste" die automatische Berechnung der Fehltage eingebaut:
=WENN(E4="";NETTOARBEITSTAGE([@Anfangsdatum](C4);[@Enddatum](D4);lstFreieTage);(WERT(F4)*24-WERT(E4)*24)/8)

Da die Datei über mehrere Jahre hinweg genutzt werden soll, ist diese Abwesenheitsliste natürlich sehr lang. Leider ist es nun so, dass meine NETTOARBEITSTAGE-Formel den Bereich "IstFreieTage" immer nur vom aktuell ausgewählten Kalenderjahr berechnen kann (da sich die Formeln in genanntem Bereich natürlich immer nur auf das aktuelle/ausgewählte Jahr beziehen). Soweit so gut und so korrekt. Allerdings verfälscht es die Gesamtzahl der Netto-Tage in der Abwesenheitsliste, wenn ich mir die Fehltage eines Mitarbeiters über mehrere Jahre hinweg ansehen möchte.

Besteht die Möglichkeit der Formel beizubringen, dass sich der Bereich "IstFreieTage" immer auf das Jahr der Zelle C4 bzw. D4 beziehen kann? Ich habe es bereits mit Verketten und Indirekt versucht, jedoch komme ich auf keinen grünen Zweig.

Meine Idee war, dass die Formel grundsätzlich so aussehen könnte:
=WENN(E4="";NETTOARBEITSTAGE([@Anfangsdatum](C4);[@Enddatum](D4);KALENDERJAHR=JAHR(D4)lstFreieTage);(WERT(F4)*24-WERT(E4)*24)/8)

Macht das Sinn?

Danke schonmal vorab und viele Grüße,

der_Bo

 

Hola,

eine Beispieldatei wäre schön.

Gruß,
steve1da

 

Hi steve1da,

aber sicher...

 

Hola,

da IstFreieTage ja ein definierter Bereich ist wo nur die Feiertage eines bestimmten Jahres stehen sehe ich aktuell keine Möglichkeit das variabel zu gestalten. Excel müsste ja für jedes Jahr intern irgendwo die Feiertage ermitteln und die sind ja immer anders (außer den fixen natürlich).

Gruß,
steve1da

 

Hi steve1da,

genau das ist der Knackpunkt. Ich hatte gehofft, dass ich irgendwie in der Formel NETTOARBEITSTAGE den dritten Syntaxwert mit einer verschachtelten Formel so hinkriege, dass Excel sich die freien Tage für das Kalenderjahr aus Zelle D4 zieht. Wäre halt eine Luxuslösung... :-)

 

Hola,

das einzige was mir grad einfällt ist, die freien Tage für jedes Jahr nebeneinander zu erstellen und diesen Bereich dann für die Formel heranzieht.

Gruß,
steve1da

 

Hallo Christian,

du hast im Arbeitsblatt "gesetzl. Feiertage" die strukt.Tabelle "Betriebsferien" mit der Spalte/Feld "Feiertage", wobei diese Spalte identisch ist mit dem benannten Zellbereich "IstFreieTage". Darüber hinaus hast du unterhalb davon eine 2. Tabelle "Betriebsferien7" mit den Feiertagen des Vorjahres.
Ich weiß natürlich nicht, für was du diese separate Tabelle fürs Vorjahr benötigst - aber mein Gedanke ist: Wenn du die 2. Tabelle zu einem Bestandteil der ersten Tabelle machst, so erstreckt sich der benannte Zellbereich "IstFreieTage" automatisch über die Feiertage zweier Jahre. Ich habe dir zusätzlich noch die Feiertage des Vorvorjahres unten dran gehängt, sodass jetzt in diesen Demodaten 3 Jahre mit ihren Feiertagen im Zellbereich "IstFeiertage" enthalten sind. Das ließe sich so ganz leicht auf noch weitere Vorjahre erweitern. -- Das alles unter der Voraussetzung, dass du die separate Vorjahrestabelle "Betriebsferien7" nicht benötigst.

 

Hi Anton,

erste Sahne. Auf die Idee bin ich noch gar nicht gekommen.

Das löst mein Problem. Vielen Dank.

Gruß
der_Bo