Excel dyn. Kalender, Kalenderwochenstunden in extra Blatt übernehmen

Question

Hallo ihr fleißigen Helfer!

Ich habe einen dynamischen Kalender mit Excel erstellt https://supportnet.de/forum/?qa=blob&qa_blobid=12670125974162283331.

Er besteht aus 12 Monatsblättern, einem Blatt Überstunden, einem Blatt Feiertage und einem Blatt Sollstunden.

Bei Änderung der Jahreszahl im Januar, werden im gesamten Kalender die Wochentage, die Kalenderwochen, die Feiertage und die Überstunden, auch wochenweise entsprechend übernommen.

In dem Blatt Überstunden werden die Überstunden pro Monat übernommen.

Das ist ja kein Problem.

Problematisch wird es für mich, in dem Blatt Überstunden die Überstunden pro Kalenderwoche zu übernehmen, da die ja auch dynamisch sind, und bei jeder neuen Jahreszahl die Zelle in der Spalte „Überstunden pro Woche“ ändern.

Ich kann die Überstunden in den Kalenderwochen mit Hand in jedem Monat verknüpfen, aber das müsste ich ja dann in jedem Jahr neu machen.

Gibt es die Möglichkeit, auch die Übernahme der Kalenderwochenstunden in dem Blatt Überstunden mit einer Formel zu automatisieren?

Mit freundlichen Grüßen

Hans-Jörg

Answer 1

Hallo Hans-Jörg,

Füge folgende Formel in die Zelle B23 des Tabellenblatts "Überstunden" ein:

=SUMMENPRODUKT((--(KALENDERWOCHE(Januar!$B$3:$B$33+0;1)=Überstunden!A23))*(WOCHENTAG(Januar!$B$3:$B$33;3)<5); Januar!$O$3:$O$33)

Damit werden die Überstunden, die in einer Kalenderwoche zwischen Montag und Freitag entstanden sind, aufsummiert. So kannst du die Addition der Stunden an Sonntagen in der Spalte Überstunden beibehalten. Falls du diese Addition löschst, geht die Formel auch einfacher:

=SUMMENPRODUKT(--(KALENDERWOCHE(Januar!$B$3:$B$33+0;1)=Überstunden!A23); Januar!$O$3:$O$33)

Diese Formel kannst du nach unten ziehen. Für die Wochen, die sich über 2 Monate ziehen können, sieht die Formel dann so aus (Beispiel für Woche 5 für die Monate Januar und Februar):

=SUMMENPRODUKT((--(KALENDERWOCHE(Januar!$B$3:$B$33+0;1)=Überstunden!A27))*(WOCHENTAG(Januar!$B$3:$B$33;3)<5); Januar!$O$3:$O$33)+SUMMENPRODUKT((--(KALENDERWOCHE(Februar!$B$3:$B$33+0;1)=Überstunden!A27))*(WOCHENTAG(Februar!$B$3:$B$33;3)<5); Februar!$O$3:$O$33)

Die Formeln musst du natürlich für die einzelnen Monate anpassen.

Erklärt wird der erste Teil der Formel hier: Klick mich!

Gruß

M.O.

Answer 2

Hallo M.O!
Vielen Dank für Deine Antwort, genau so hab ich mir das vorgestellt! Unglaublich, was mit Excel alles funktioniert. Nur, ohne solche Profis wie ihr, wären viele aufgeschmissen. Auf so eine Formel muss man als sehr kleiner Excel Versteher erst mal kommen.

Ich möchte mich nochmal herzlich bedanken.

Mit freundlichen Grüßen

Hans-Jörg