Nur noch zwei Mal joggen, dann ist wieder Weihnachten.
Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:
„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.
Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.
Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.
Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:
Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.
Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.
Die Formel
=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):
Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:
Dazu verwende ich die Formel:
=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:
=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)
Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.
Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.
Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.