Danke!
Formel: Bevor es LET gab, konnte man die benannten Formeln auch schon verschachtelt verwenden (und so nicht zuletzt die enge Grenze von 8 Funktionsschachtelungen und 31 Klammerungen in Excel4 erweitern).
Der Nachteil dabei war (in Bezug auf die Dateigröße), dass die Namen in voller Größe und auch mehrfach, als wären sie nie geschachtelt worden, die Hauptspeichergröße belasteten.
Bei LET sollte es ja eigentlich anders sein. Aber: Da jederzeit auch Zwischenschritte im LET als Endergebnis ausgegeben werden können, wird als Konsequenz auch jeder Zwischenschritt "zur Formellaufzeit" (was auch immer das tatsächlich heißen mag) aufbewahrt. Nicht ganz mit dem Größeneffekt verschachtelter Namen, aber immerhin.
Die benannten Formeln (
hier zu der physischen Position Schnittpunkt L375: zur Orientierung,
Tabelle1! in folgenden Formeln übersichtshalber gelöscht, da Excel sie automatisch ergänzt):
(1) Dauer: =SUMME((1-2*(SPALTE($B375:$J375)>3))*(REST(SPALTE($B375:$J375);2)=0)*(XX>0)*XX)
(2) XX: =WENN(next-$C375:$K375<0;next;$C375:$K375)-WENN(this-$B375:$J375>0;this;$B375:$J375)
(3) next: =INDEX(M:M;$A375):INDEX(M:M;$A375+1)
(4) this: =INDEX(L:L;$A375):INDEX(L:L;$A375+1)
(5) A375: =VERGLEICH($B375;$L$1:$L$371)
(6) AA6: =MIN(3;WAHL(REST(L6;7)+1;2;2;1;1;1;1;1)+ISTZAHL(VERGLEICH(L6;AB$6:AB$31;))*3)
(7) L1:Z5: Diagonale Eingabe der Zeitbegrenzungen pro "Tage-unterschiedlich"-Klasse (WE, WT, FT)
(..) weitere
(1) enthält (2), und (2) enthält (3) und (4).
(3),(4): Wir müssen also bei (3) und (4) anfangen: Beide ergeben einen 1x2-Vektor vom Such- und Folgedatum. (4) ist die Anfangszeit der betrachteten Zeitschachtel "mit eigener Aussage", (3) ist deren Ende (bzw. Anfang der nächsten).
(2) schneidet die vertikalen (über 2 Folgedatümer (3),(4)) und die horizontalen Zeiteinteilungen (Anfangs- und Endzeiten sowie Pausenbeginne und -enden einer Arbeitszeit = -Zeile) im Sinne von =MAX(;
MIN(d;b)-MAX(a;c)). Ich habe mich insgesamt auf WENN dabei zurückgezogen, da WENNFEHLER(EXP(LN(...));) erst ab XL2007 funktioniert. Dies hier klappt auch vorher.
(1) vervollständigt nun die Arrayform der =
MAX(;MIN(d;b)-MAX(a;c)
)-Formel.
(5) sorgt für die korrekte Zeile des Beginns von (3) und (4). Ich hätte auch variable Tagesanzahlen statt fix Tag und Folgetag verwenden können (wie in
sbtimediff_en), aber dann wäre es keine Lohnzeitenverwaltung, sondern eine Auftragszeiten-Ermittlung geworden.
(6) sortiert die Kalenderdatümer in WT WE FT, ...
(7) ... die manuell in 1:5 spezifiziert sind (davon 4:5 momentan nicht verwendet). Bitte dort die Zellinhalte, nicht etwa die Zellanzeige, checken (00:00 ist nämlich 0 oder 1, da der Tag von 0:00 bis 24:00 spezifiziert sein muss).
(..) D:K enthalten 4 mögliche Pausen in 8 Spalten (Anfang und Ende, relativ zu B375: Arbeitsanfang, in Zeile 6 spezifiziert). ENTF man D6:K6, wird ohne Pausen gerechnet. Die Pausen, sofern nicht ENTFt, werden arbeitnehmerfreundlich abgezogen, nicht etwa wie in manchen Zeiterfassungssystemen blockweise. Außerdem werden sie in die Zeitschachteln hinein anteilig abgezogen. Die Zeitschachtel für die besprochene Zelle L375 ist Werktag 0:-5:.
Die FT (Feiertage) werden ab AB6 bundeslandspezifisch gelistet. Der ISTZAHL-Teil in (6) sorgt für die Zuordnung. Man könnte nun z.B. eine eigene Schachtel z.B. für die oft halbierten FT OsSa, PfSa, HlgA und Silv bauen. Dafür würde (6) als Formel halt etwas länger. Genauso könnte man FT noch unterschiedlich stark bonifizieren mit Zuschlägen (373:374); erfordert nur weitere Spalten innerhalb von L:Z.
(1) hier noch mal eingesetzt als Monster (muss ja nicht wirklich sein, Achtung:{}-Abschluss):
L375:
=SUMME((1-2*(SPALTE($B375:$J375)>3))*(REST(SPALTE($B375:$J375);2)=0)*(WENN(
INDEX(M:M;$A375):INDEX(M:M;$A375+1)-$C375:$K375<0;
INDEX(M:M;$A375):INDEX(M:M;$A375+1);$C375:$K375)-WENN(
INDEX(L:L;$A375):INDEX(L:L;$A375+1)-$B375:$J375>0;
INDEX(L:L;$A375):INDEX(L:L;$A375+1);$B375:$J375)>0)*(WENN(
INDEX(M:M;$A375):INDEX(M:M;$A375+1)-$C375:$K375<0;
INDEX(M:M;$A375):INDEX(M:M;$A375+1);$C375:$K375)-WENN(
INDEX(L:L;$A375):INDEX(L:L;$A375+1)-$B375:$J375>0;
INDEX(L:L;$A375):INDEX(L:L;$A375+1);$B375:$J375)))
Heute würde ich das (für XL2000) etwas kürzer schreiben können:
L375:
=SUMME((1-2*(SPALTE($B375:$J375)>3))*(REST(SPALTE($B375:$J375);2)=0)*(WENN(
INDEX(M:M;$A375+{0;1})-$C375:$K375<0;
INDEX(M:M;$A375+{0;1});$C375:$K375)-WENN(
INDEX(L:L;$A375+{0;1})-$B375:$J375>0;
INDEX(L:L;$A375+{0;1});$B375:$J375)>0)*(WENN(
INDEX(M:M;$A375+{0;1})-$C375:$K375<0;
INDEX(M:M;$A375+{0;1});$C375:$K375)-WENN(
INDEX(L:L;$A375+{0;1})-$B375:$J375>0;
INDEX(L:L;$A375+{0;1});$B375:$J375)))
Excel Zeitkonto Nachtstunden/ Sonntagsstunden/ Feiertagsstunden berechnen lassen
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
