Hallo,
in meiner Formel steckte auch noch ein Fehler und sie kann noch etwas gekürzt werden. Hier die Korrektur:
Code: Alles auswählen
=WENN(UND(ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24));ZÄHLENWENN($G$2:$H$10;A2));D2/24;WENN(ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24));C2;WENN(ZÄHLENWENN($G$2:$H$10;A2);1-B2;0)))*24
Am besten ist, sie mal auseinander zu nehmen (auf das /24 und *24 gehe ich mal nicht ein, da es nur der Formatierung gilt):
Wenn
UND(ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24));ZÄHLENWENN($G$2:$H$10;A2))
dann
D2
ansonsten wenn
ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24))
dann
C2
ansonsten wenn
ZÄHLENWENN($G$2:$H$10;A2)
dann
1-B2
ansonsten
0
Was macht diese Wenn-Dann-Monster? Übersetzt heißt es für das Beispiel (Datum 30.04.14, Beginn 23:00, Ende 07:00, Stunden
Wenn der Beginn- und der Ende-Tag Feiertag(e) sind, dann sind es die (Gesamt-) Stunden 8. Wenn (nur) der Endtag-Tag ein Feiertag ist, dann sind es die 7 Stunden, die der End-Zeit entsprechen. Wenn nur der Start-Tag ein Feiertag ist, dann sind es die Stunden bis Mitternacht (1-23:00). Ansonsten 0, da ja kein Feiertag dabei ist.
In dieses Teil möchtest du nun noch die Frage nach dem Sonntag integrieren. Also wenn (neben den obigen Bedingungen) noch Sonntag wäre, dann ...
Konkreter: wenn der betreffende Tag/die betreffenden Tage ein Feiertag oder ein Sonntag ist, dann ...
Damit wird die Formel zu:
Code: Alles auswählen
=WENN(UND(ODER(WOCHENTAG(KÜRZEN(A2+B2+D2/24);2)=7;ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24)));ODER(WOCHENTAG(A2;2)=7;ZÄHLENWENN($G$2:$H$10;A2)));D2/24;WENN(ODER(WOCHENTAG(KÜRZEN(A2+B2+D2/24);2)=7;ZÄHLENWENN($G$2:$H$10;KÜRZEN(A2+B2+D2/24)));C2;WENN(ODER(WOCHENTAG(A2;2)=7;ZÄHLENWENN($G$2:$H$10;A2));1-B2;0)))*24
In dieses Monster möchtest du nun auch noch einbauen, dann an ganz bestimmten Tagen (24.12. und 27.12.) nur bestimmte Uhrzeiten als Feiertag zählen.
Ich würde die Berechnung gern so einfach wie nur möglich halten
Genau dafür sind Hilfszellen sinnvoll. Sie vereinfachen die Rechnungen (durch Zerlegen in einzelne Fälle). Diese Zellen können ausgeblendet werden und nur die finale Zelle mit dem Ergebnis bleibt sichtbar. Die Gesamtmonsterformel sieht sich keiner an, um sie nachzurechnen.