Office: (Office 365) Feiertage ohne Wochenende zählen

Hallo Zusammen

Während ich das Thema geschrieben habe, konnte ich eine Lösung finden.
Habe das Thema aber trotzdem gepostet, falls jemand auf dasselbe Problem stossen sollte bzw. jemand die Knacknuss trotzdem noch lösen möchte.

--

"Ich stehe aktuell vor einer Herausforderung die ich zur Zeit nicht alleine lösen kann."

Ich möchte in meiner Tabelle, gerne alle "Sondertage" ausgeben, damit ich anschliessend die zusätzliche Vergütung der geleisteten Piketttage berechnen kann. Ein Sondertag sind alle Feiertage und Wochenenden. Es wird immer 1 Woche Pikett am Stück geleistet und die Vergütung für Wochentage und Feiertage/Wochenende sind unterschiedlich.

Ausgangslage:


Tabellenblatt 1 -> "Daten"
Spalte A = 1. Tage der KW
Spalte B = Letzter Tag der KW
Spalte C = KW
Spalte D = Sondertage
Spalte E = Vergütung

Tabellenblatt 2 -> "Feiertage" (Spalte A) [Feiertage werden komplett automatisch berechnet]

Die beste Formel die ich hinbekommen habe, ist die Folgende:
=SUMME(ZÄHLENWENNS(Feiertage!$A:$A;">="&Daten!A2;Feiertage!$A:$A;"<="&Daten!B2);2)

Mit dieser Formel prüfe ich die Datenwerte zwischen Spalte A und B (Kalenderwoche), prüfe ob diese ein Feiertag sind und addiere "2" hinzu. Da immer eine Woche am Stück Pikett geleistet wird, gibt es immer 2 Sondertage, Samstag und Sonntag.
Mit dieser Formel, berücksichtige ich aber nicht, ob ein Feiertag auf ein Wochende fällt. Dadurch zählt die Formel zum Teil die Sondertage doppelt.

Zahlenbeispiel:
Anfang | Ende | KW | Sondertage
11.04.2022 | 17.04.2022 | 15 | ?
Zwischen dem 11.04.2022 und dem 17.04.2022 haben wir 2 Feiertage. Den Karfreitag und den Ostersonntag.
In dieser Woche würde ich eine Anzahl von 3 Sondertagen erwarten. Die Formel oben, gibt mir aber 4 Tage aus. Das ich 2 Tage für das Wochenende zähle sowie 2 Feiertage in der Liste vorkommen. Da der Ostersonntag an einem Sonntag ist, wird dieser doppelt gezählt.

Die Berechnung der Vergütung ist dann nur noch in einer nächsten Spalte mit:
=[Sondertage]*[Sondertagessatz]+(7-[Sondertage])*[Wochentagessatz]

Zahlenbeispiel:
=D2*40+(7-D2)*20

Habe diverse Formeln und Ansätze probiert, jedoch ohne Erfolg. Die Schwierigkeit hier ist, dass ich zwei Bereiche habe die geprüpft werden müssen. Einmal die Daten der KW selbst und diese dann wiederum gegen den Wertebereich der Feiertage. Als Formel ausgedrückt:
Wenn alle Daten der jeweiligen KW in der Feiertagesliste vorkommen und kein Wochenende ist, dann zählen.

Wie müsste ich die Formel umbauen damit es funktioniert? Oder jage ich hier einem Geist hinterher?

----

Wie oben erwähnt, habe ich während ich dieses Thema geschrieben habe, eine Lösung gefunden.
Und zwar habe ich in der Feiertagesliste zusätzlich eine Spalte eingebaut, die die Wochenenden mit einer Wenn-Formel "ausklammert":
=WENN(WOCHENTAG(B2;2)>6;"";B2)

Dadurch bekomme ich in der Hauptformel nur die Feiertage die auf einen Wochentag fallen und so keine doppelten Zählungen mehr.

Falls nun jemand aber noch eine Lösung für eine komplette Formel haben sollte, wäre ich dennoch dankbar, falls man diese an einem anderen Ort noch einsetzen wollen würde.

Manchmal sieht man den Wald vor lauter Bäumen gar nicht mehr

Gruss
Chrigi

 

Du hast die Feiertage in Deiner Datzei abgelegt. Benutze
=NETTOARBEITSTAGE()

 

Hallo,

verwende bitte NETTOARBEITSTAGE.INTL (und nicht NETTOARBEITSTAGE!):
=NETTOARBEITSTAGE.INTL(A2;B2;1;)-NETTOARBEITSTAGE.INTL(A2;B2;1;Tabelle2!A2:A100)

 

Hallo Zusammen

Perfekt! Die "=NETTOARBEITSTAGE.INTL(A2;B2;1;)-NETTOARBEITSTAGE.INTL(A2;B2;1;Tabelle2!A2:A100)" war die Lösung.
Ich habe diese nun noch mit der Summe Funktion zu:
=SUMME(NETTOARBEITSTAGE.INTL(B3;C3;1;)-NETTOARBEITSTAGE.INTL(B3;C3;1;Feiertage!A:A);2)

ungebaut und so bekomme ich das erwünschte Ergebnis und ich kann die Wenn-Funktion in der Feiertagesliste wieder rausnehmen.

Besten Dank und Gruss
Chrigi

 

Die SUMME ist überflüssig addiere einfach 2 dazu, das ist viel übersichtlicher:
NETTOARBEITSTAGE.INTL(B3;C3;1;)-NETTOARBEITSTAGE.INTL(B3;C3;1;Feiertage!A:A)+2