Office: (Office 2016) Zeiterfassung: freie Tage automatisch ausfüllen lassen

Hallo zusammen.

Ich bin neu hier in diesem Forum. Ich habe schon gesucht, aber soweit ich sehen kann, ist mein Problem noch nicht aufgetaucht.

Folgendes ist los:
Ich habe eine Excel-Tabelle mit einer Registerkarte für jeden Monat. Und ich habe eine Registerkarte Einstellungen.
In den Registerkarten mit den Monaten beginnt der erste des Monats immer in Zelle C7, diese Zellen sind wie folgt formatiert:
- Kategorie: "Datum"
- Typ: *15.03.2016
In den Registerkarten der Monate gibt es in der Spalte S die Möglichkeit, eine Notiz zu schreiben.

In der Registerkarte Einstellungen habe ich eine Liste der freien Tage:
- in den Zellen B21 bis B31 stehen die Namen der freien Tage
- die Zellen C21 bis C31 enthalten die Daten der freien Tage
- die Formatierung der Daten in den Einstellungen entspricht den Daten der Monate

Ein Beispiel:
Am 20.05.2024 ist Pfingstmontag.
Im Registerkarte Einstellungen steht in Zelle B28 das Wort Pfingstmontag, in Zelle C28 steht das Datum 20.05.2024
Ich möchte, dass das Wort Pfingstmontag in der Registerkarte Mai in Zelle S26 erscheint.

Und das dann natürlich auch für alle anderen Monate, in denen freie Tage vorkommen.

Ich habe diese Formel:
=WENNFEHLER(INDEX(Einstellungen!$B$21:$B$26; VERGLEICH(C7; Einstellungen!$C$21:$C$26; 0)); "")

Und jetzt kommt das Verrückte!
In den Monaten Januar, März und April sind die freien Tage an der richtigen Stelle vermerkt.
Im Monat Mai wird Christi Himmelfahrt auch ordentlich in Spalte S vermerkt.
Danach wird nichts mehr vermerkt, d.h. im Monat Mai wird Pfingsten nicht mehr vermerkt und auch in den folgenden Monaten werden die freien Tage nicht mehr vermerkt.

Ich verstehe wirklich nicht, wie das passieren kann. Vielleicht hat ein Experte in diesem Forum eine Idee?

Ich schicke Dir die Excel-Datei mit, damit Du sie Dir ansehen kannst.

Vielen Dank im Voraus für Deine Hilfe!

LG
Jan Willem

 

Hola,
Ich verstehe wirklich nicht, wie das passieren kann.
na dann schau dir doch einfach mal an wo "Pfingstmontag" steht und wo der Bereich deiner INDEX Formel endet.
Gruß,
steve1da

 

Wow... Wie dumm ich mich jetzt fühle. Vielen Dank für deine schnelle Antwort. Du hast absolut Recht. Danke, jetzt kann mein Wochenende beginnen. Halleluja!

 

Hallo,

solche Zellbereichsfehler kannst du leicht vermeiden, wenn du die Tabelle der Feiertage in Einstellungen!B20:C31 in eine strukturierte (=intelligente) Tabelle umwandelst.
Dazu einfach den vorgenannten Zellbereich markieren und die Tastenkombination Strg+T drücken.
Diese Tabelle mit einem sinnvollen Namen versehen, zB. "tbFeiertage" und die Spaltenköpfe mit "Bezeichnung" und "Datum" versehen (in B20:C20).

Damit kannst du in den Monatsblättern die Feiertagsformel von
=WENNFEHLER(INDEX(Einstellungen!$B$21:$B$26; VERGLEICH(C7; Einstellungen!$C$21:$C$26; 0)); "")
umstellen auf:
=WENNFEHLER(INDEX(tbFeiertage[Bezeichnung]; VERGLEICH(C7; tbFeiertage[Datum]; 0)); "")
Du kannst an deine "tbFeiertage"-Tabelle beliebig viele Feiertage anfügen (oder auch löschen), ohne Gefahr zu laufen, dass in deine Blattformeln Bereichsfehler einfließen. Ferner ist die Formel leichter verständlich.
Außerdem würde sich die Formel vereinfachen, wenn du die Spalten in der Reihenfolge "Datum" und "Bezeichnung" platzieren würdest. Die Formel lautet dann einfach:
=WENNFEHLER(SVERWEIS(C7;tbFeiertage;2;0);"")

Ferner gibt es für den Ostersonntag eine Formel, die in deinem Fall (deine Jahreszelle ist in D8) lautet:
=7*RUNDEN((4&-D8)/7+REST(19*REST(D8;19)-7;30)*0,14;)-6
Sie ist für die Jahre 1900-2203 gültig: Ermittlung des Ostersonntags und aller beweglichen Feiertage (herber.de)