nur Kalenderwochen auflisten die keine Feiertage enthalten

[Kalkulon]

Hallo liebe Excelfans ;)

Seit einiger Zeit beschäftige ich mich wieder öfters mit Ecxel und merke, dass der Spaßfaktor immer weiter steigt XD

Als bisheriger stiller Mitleser bin ich jetzt an einen Punkt gekommen, an dem ich einfach nicht weiterkomme, vielleicht hat jemand eine Idee dazu:

Im Endeffekt soll es um eine Auswertung von Stromverbrauchsdaten gehen - ist in dem Fall aber nicht relevant.


Ich habe es geschafft alle Feiertage zu ermitteln und die Arbeitswochen (Mo-Fr) eines Jahres darzustellen.
Jetzt würde ich gerne nur jene Kalenderwochen auflisten, welche keine Feiertage enthalten - ohne Pivot-Tabelle. Das Thema Makros werde ich wohl erst in ein par Wochen angehen - also würde ich eine klassische Lösung vorziehen ;)

Ich hoffe ich habe keine Info vergessen.

Vielen Dank mal im Vorhinein!

lG Michl

[shift-del]

Hallo

Code:

=ZÄHLENWENNS($B$3:$B$263;B3)=5

[lupo1]

AT!A3: {=WENN(ODER(D3='C:\Users\axxcl\Downloads\[reporting sheet 161230.xlsx]Fei'!$E$1:$E$13);"Feiertag";"Arbeitstag")}

AW!A4: =A3+7*(1+SUMMENPRODUKT(--(ZÄHLENWENN(AT!B:B;B3+{1.2.3})<5))) bitte genau prüfen, ob korrekt. EDIT: Nee, ist falsch, daher

AW!A4: =A3+7*(1+
(ZÄHLENWENN(AT!B:B;B3+1)<5)+
(ZÄHLENWENN(AT!B:B;B3+1)<5)*(ZÄHLENWENN(AT!B:B;B3+2)<5))

wird Ende Mai 2015 tatsächlich ausgereizt. Sollten mal 3 Wochen hintereinander FT haben, dann:

AW!A4: =A3+7*(1+
(ZÄHLENWENN(AT!B:B;B3+1)<5)+
(ZÄHLENWENN(AT!B:B;B3+1)<5)*(ZÄHLENWENN(AT!B:B;B3+2)<5))+
(ZÄHLENWENN(AT!B:B;B3+1)<5)*(ZÄHLENWENN(AT!B:B;B3+2)<5)*(ZÄHLENWENN(AT!B:B;B3+3)<5))

Wäre die Datei anders, könnte das einfacher ausfallen. Letzteres etwas kürzer:

AW!A4: =A3+7*(1+
(ZÄHLENWENN(AT!B:B;B3+1)<5)+
(SUMMENPRODUKT(--(ZÄHLENWENN(AT!B:B;B3+{1.2})<5))=2)+
(SUMMENPRODUKT(--(ZÄHLENWENN(AT!B:B;B3+{1.2.3})<5))=3))

Bei mehr als 3 Wochen mit FT hintereinander muss man schon tricksen (hier sind es 4):

AW!A4: =A3+7*(1+WENN(REST(
LOG(1+SUMMENPRODUKT({1.2.4.8}*(ZÄHLENWENN(AT!B:B;B3+{1.2.3.4})<5));2);1)=0;
LOG(1+SUMMENPRODUKT({1.2.4.8}*(ZÄHLENWENN(AT!B:B;B3+{1.2.3.4})<5));2))) stimmt noch nicht!

[neopa]

Hallo,

gesucht ist ja nur die Auflistung KW, die in den Werktagen (Mo-Fr) keine Freitage haben.
Ich würde dies dann einen anderen Formelansatz lösen:

In AW!C3:

Code:

=WENNFEHLER(AGGREGAT(15;6;ZEILE(A$1:INDEX(A:A;KALENDERWOCHE(("1-"&Frei!B$1+1)-1;21)))/ISTNV(VERGLEICH(ZEILE(A$1:INDEX(A:A;KALENDERWOCHE(("1-"&Frei!B$1+1)-1;21)));INDEX(KÜRZEN((Frei!E$1:E$19-DATUM(JAHR(Frei!E$1:E$19+3-REST(Frei!E$1:E$19-2;7));1;REST(Frei!E$1:E$19-2;7)-9))/7)*(REST(Frei!E$1:E$19;7)>1););));ZEILE(A1));"")

und Formel nach unten ziehen.

Diese Formel würde natürlich erheblich kürzer, wenn in Blatt: Frei eine Hilfsspalte eingeführt wird.

in Frei!F1:

Code:

=KALENDERWOCHE(E1;21)*(REST(E1;7)>1)

nach unten kopiert wird.

Dann reicht in AW!C3:

Code:

=WENNFEHLER(AGGREGAT(15;6;ZEILE(A$1:INDEX(A:A;KALENDERWOCHE(("1-"&Frei!B$1+1)-1;21)))/ISTNV(VERGLEICH(ZEILE(A$1:INDEX(A:A;KALENDERWOCHE(("1-"&Frei!B$1+1)-1;21)));Frei!F$1:F$15;));ZEILE(A1));"")

[lupo1]

(gegenstandslos)

[neopa]

Hallo lupo1,

Geschrieben von lupo1 - Heute, 17:25 (gegenstandslos)

was willst Du uns damit sagen?

[lupo1]

Nichts ;) Ich hatte mich zunächst über Deinen Beitrag geärgert, aber dann festgestellt, dass Du ihn ungefähr gleichzeitig zu meinem geschrieben haben musstest. Damit waren dann meine Vorwürfe eben - gegenstandslos, weil Du meinen Beitrag nicht berücksichtigt haben konntest ;)

[neopa]

Hallo lupo1,

also Deinen Beitrag von 16:10 hatte ich schon gelesen. Allerdings hast Du offensichtlich noch nachträgliche Korrekturen in Deinem Beitrag vorgenommen, denn ursprünglich stand da meiner Erinnerung nach noch eine abschließende Bemerkung, dass Du Deine Formeln noch auf Korrektheit prüfst oder so ähnlich. Auch deswegen hatte ich mir Gedanken gemacht, wie ich das Problem lösen würde, egal wie viele KW hintereinander zumindest einen Feiertag bzw. definierten freien Tag haben.

[lupo1]

Ich lasse das "gegenstandslos" jetzt trotzdem stehen und halte an meiner Lösung als der unmittelbar gewollten fest. Beschränkung auf 5 Werktage liegt längst vor, sowohl in der Problemstellung als auch in den Lösungen von shift-del und mir.

[neopa]

Hallo lupo1,

in der Ausgangsfragestellung von Michl steht:

Jetzt würde ich gerne nur jene Kalenderwochen auflisten, welche keine Feiertage enthalten

In seiner eingestellten Datei steht zusätzlich in AW!C2: 

Vollst. AW

in den Zellen darunter steht nichts, während in Spalte A und B schon etwas steht.

Beides zusammen betrachtet, habe ich die Zielstellung des Fragestellers anders als Du betrachtet. Dafür habe ich eine Formel für AW!C3 eingestellt, die nach unten zu kopieren ist. Die Beschränkung auf die 5 Werktage hat der Fragesteller vorgegeben, ich hab diese in meiner ersten Antwort nur nochmal wiederholt aber nicht angezweifelt.

Ob nun Deine Interpretation oder meine die von Michl angestrebte ist, kann mE nur der Fragesteller entscheiden.

@Detlef,

vielleicht kannst Du mir mal Deinen Vorschlag erläutern. Wo wolltest Du diesen einsetzen und was bewirken? Sorry, ich checke es momentan wirklich nicht.

@aMichl,

ich hab gerade noch einen Fehler im Ergebnis meiner Formel bemerkt. Wenn Du meinen Formelvorschlag nutzen willst, dann kannst Du diesen Fehler aber ganz einfach vermeiden. Dazu müsstest Du lediglich zusätzlich als letzten Feiertag in Deiner Liste in Frei!E1:E## noch den 1.1. des Folgejahres mit aufnehmen also z.B. so: =DATUM(B1+1;1;1)