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.
Wäre doch mit Nettoarbeitstage() viel leichter. Wenn die Woche vom Lieferdatum weniger als 5 Tage hat, dann enthält sie einen Feiertag. Alles mit einer Formel lösbar.
stimmt, das ist ein guter Denkansatz.
Aber auch hier:
ich muss von dem Lieferdatum den davorliegenden Montag ermitteln und davon ausgehend die nächsten 5 Tage (also: Montag + 4). Und dann kann ich überprüfen, ob NETTOARBEITSTAGE = 5.
Wahrscheinlich wäre die Formel etwas kürzer als meine – aber trivial und einfach ist sie auch nicht.
Dank fürs Mitdenken und Mitknobeln
Rene Martin
Doch, eigentlich relativ trivial:
=(NETTOARBEITSTAGE(H20-WOCHENTAG(H20;3); H20-WOCHENTAG(H20;3) + 4; B2:P13)<5)*25%
Die Formel ist nicht sehr kurz, aber man spart sich 5500 Datensätze ob ein Datum jetzt Feiertag ist oder nicht.
stimmt – DAS ist richtig – die vielen Hilfsberechnungen kann ich mir hierbei sparen …