Falls kein Werktag: nächster Werktag

@ > Home > Inhalt > Falls kein Werktag: Nächster Werktag Excel 4+

Aufgabe:

Stufe 1: Ein wie auch immer vorliegendes Datum soll auf einen Werktag fallen (für Ablauf von Fristen bei Ämtern). Wochenenden und Feiertage sind dafür also zu überspringen, wenn dies nicht der Fall ist, und der folgende Werktag ermittelt werden.

Stufe 2: Bei Zahlungsfälligkeiten bei Banken (Target-Tage nach Euro-Zinsmethode) soll, wenn das unter 1. ermittelte Datum in den Folgemonat fällt, stattdessen der letzte vorherige Werktag ermittelt werden.

Voraussetzungen:

- Ein 1spaltiger Bereich mit aufsteigenden Feiertagen wird ftg genannt.
- A1: das eingegebene oder ermittelte Datum
- Die Funktion =ARBEITSTAG() aus dem Analyse-Add-In soll nicht verwendet werden.

Überlegungen:

Ein Wochenende hat 2 Tage. Es gibt maximal 3 aufeinanderfolgende Werk-Feiertage, nämlich Weihnachten, wenn man den Heiligabend dazu zählt (Banken, aber auch manche Arbeitgeber). So gelangt man zu einer maximalen werktaglosen Dauer von 5 Tagen. Also muss maximal 5 zu einem Nicht-Werktag addiert (für Stufe 2: evtl. abgezogen) werden, um einen Werktag zu erhalten.

Stufe 1:

Feiertage (vor dem *) und Wochenendtage (nach dem *) müssen für jeden dieser 5 Tage gesondert berechnet werden, da die Reihenfolge von Werk-, Feier- und Wochenendtagen beliebig ist. Die folgende geschachtelte WENN-Formel tut dies, wobei das DANN bei jeweiligem Ausschluss von Feier- und Wochenendtag erreicht ist:

=WENN((A1+0>SVERWEIS(A1+0;ftg;1))*(REST(A1+0;7)>1);0;
WENN((A1+1>SVERWEIS(A1+1;ftg;1))*(REST(A1+1;7)>1);1;
WENN((A1+2>SVERWEIS(A1+2;ftg;1))*(REST(A1+2;7)>1);2;
WENN((A1+3>SVERWEIS(A1+3;ftg;1))*(REST(A1+3;7)>1);3;
WENN((A1+4>SVERWEIS(A1+4;ftg;1))*(REST(A1+4;7)>1);4;5)))))+A1

Für Matrixformel-Anhänger ist dies ein Graus, aber sie ist schnell (da ca. 71% aller zufälligen Tage Werktage sind und somit die Formel mit dem ersten DANN endet) und auch auf Pocket-Excel einsatzfähig (mit ca. 270 Zeichen allerdings dafür etwas zu lang).

Es naht Linderung:

{=VERGLEICH(1;(A1+ZEILE($1:$6)-1>INDEX(ftg;VERGLEICH(A1+ZEILE($1:$6)-1;ftg)))*(REST(A1+ZEILE($1:$6)-1;7)>1);0)+A1-1}

ist die elegante Kurzform. Sie muss aber nicht nur als Matrixformel eingegeben werden, sondern sogar als Arrayformel in zwei beliebige benachbarte Zellen gemeinsam markiert abgeschlossen werden. An der Ursache wird noch geforscht; vielleicht können Sie ja helfen!

Stufe 2:

Hier gilt das zu Stufe 1 Gesagte analog, mit dem Unterschied, dass die dortige Formel zunächst vergleichen muss, ob sie gegenüber dem Ausgangsdatum noch im gleichen Monat ist. Wenn ja, kann dieser Wert genommen werden. Leider muss Excel ihn noch einmal berechnen, da es sich keine Zwischenschritte merkt (Abhilfe hier, bezogen auf die markierten Teile, die sich wiederholen). Falls nein, werden rückwärts die letzten 5 Tage vor dem Ausgangsdatum entsprechend überprüft:

=A1+WENN(MONAT(
WENN((A1+0>SVERWEIS(A1+0;ftg;1))*(REST(A1+0;7)>1);0;
WENN((A1+1>SVERWEIS(A1+1;ftg;1))*(REST(A1+1;7)>1);1;
WENN((A1+2>SVERWEIS(A1+2;ftg;1))*(REST(A1+2;7)>1);2;
WENN((A1+3>SVERWEIS(A1+3;ftg;1))*(REST(A1+3;7)>1);3;
5-(A1+4>SVERWEIS(A1+4;ftg;1))*(REST(A1+4;7)>1)))))
+A1)=MONAT(A1);
WENN((A1+0>SVERWEIS(A1+0;ftg;1))*(REST(A1+0;7)>1);0;
WENN((A1+1>SVERWEIS(A1+1;ftg;1))*(REST(A1+1;7)>1);1;
WENN((A1+2>SVERWEIS(A1+2;ftg;1))*(REST(A1+2;7)>1);2;
WENN((A1+3>SVERWEIS(A1+3;ftg;1))*(REST(A1+3;7)>1);3;
5-(A1+4>SVERWEIS(A1+4;ftg;1))*(REST(A1+4;7)>1)))))
;
-WENN((A1-1>SVERWEIS(A1-1;ftg;1))*(REST(A1-1;7)>1);1;
WENN((A1-2>SVERWEIS(A1-2;ftg;1))*(REST(A1-2;7)>1);2;
WENN((A1-3>SVERWEIS(A1-3;ftg;1))*(REST(A1-3;7)>1);3;
WENN((A1-4>SVERWEIS(A1-4;ftg;1))*(REST(A1-4;7)>1);4;
5)))))

Haben Sie etwas gemerkt? Der hervorgehobene Bereich der Stufe 2 unterscheidet sich von Stufe 1 in der tiefsten Schachtelung. Das tiefste WENN weicht einer booleschen Operation. Grund ist, Platz für eine 8. Funktionsebene für SETV() zu schaffen, da Excel vor Version 2007 nur 8 Funktionen schachteln kann und dies in Stufe 1 schon ausgereizt ist - mit 6x WENN, 1x MONAT und 1x SVERWEIS bzw. REST. Ein anderer Weg zur Ebenenreduzierung könnte die Benennung tieferer Ebenen sein.

Auch hier gibt es eine Matrixformel-Variante, genauso wie oben mit dem beschriebenen Eingabemanko einer Zweizellen-Arrayformel:

{=A1+WENN(MONAT(
VERGLEICH(1;(A1+ZEILE($1:$6)-1>INDEX(ftg;
VERGLEICH(A1+ZEILE($1:$6)-1;ftg)))*(REST(A1+ZEILE($1:$6)-1;7)>1);0)
+A1-1)=MONAT(A1);
VERGLEICH(1;(A1+ZEILE($1:$6)-1>INDEX(ftg;
VERGLEICH(A1+ZEILE($1:$6)-1;ftg)))*(REST(A1+ZEILE($1:$6)-1;7)>1);0) -1;
-VERGLEICH(1;(A1-ZEILE($1:$5)>INDEX(ftg;
VERGLEICH(A1-ZEILE($1:$5);ftg)))*(REST(A1-ZEILE($1:$5);7)>1);0))}

Sie belegt nur 6 Funktionsebenen. Somit kann SETV() sofort eingesetzt werden:

{=A1+WENN(MONAT(setv(
VERGLEICH(1;(dat>INDEX(ftg;VERGLEICH(dat;ftg)))*(REST(dat;7)>1);0))
+A1-1)=MONAT(A1);getv()-1;-
VERGLEICH(1;(das>INDEX(ftg;VERGLEICH(das;ftg)))*(REST(das;7)>1);0))}

Zusätzlich verkürzt wurden die Matrix-Laufvariablen durch Benennung mit "dat" und "das".

Fazit: Der Einsatz von Matrixformel, SETV() und benannten Formelteilen gemeinsam kürzt die Formel der Stufe 2 von knapp 800 Zeichen auf 181. Das entspricht einem Faktor 4,4 bei der Kürzung und einem Faktor 1-2 bei der Beschleunigung.

Anmerkungen

http://excelformeln.de/formeln.html?welcher=13 =ARBEITSTAG()-Ersatz des Analyse-Add-Ins
http://excelformeln.de/formeln.html?welcher=428 als Ideengeber für die Target-Datum-Ermittlung

standen Pate für diese Seite.

Dieses Beispiel ist ein Spiegel für die verschiedenen Philosophien im Excel-Lager, da hier VBA, Makros oder das Add-In mit der Funktion =ARBEITSTAG() zu erheblich effizienteren Lösungen führen, als die obengenannten Formeln. Allerdings bedarf es - wie immer - dazu erst bei merklichen Leistungseinbrüchen einer Anwendung.