Generische Formel
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Zusammenfassung
Um die Arbeitszeit zwischen zwei Daten mit einem benutzerdefinierten Zeitplan zu berechnen, können Sie eine Formel verwenden, die auf den Funktionen WEEKDAY und SUMPRODUCT basiert, und zwar mithilfe von ROW, INDIRECT und MID. In dem gezeigten Beispiel lautet die Formel in F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Das ergibt 36 Stunden, basierend auf einem benutzerdefinierten Zeitplan, in dem 8 Stunden von Montag bis Freitag, 4 Stunden am Samstag und Montag, der 3. September, ein Feiertag sind. Feiertage werden als benannter Bereich G6: G8 geliefert. Der Arbeitsplan wird als Textzeichenfolge in Spalte D eingegeben und kann nach Bedarf geändert werden.
Hinweis: Dies ist eine Array-Formel, die mit Strg + Umschalt + Eingabetaste eingegeben werden muss. Wenn Sie einen normalen 8-Stunden-Arbeitstag haben, ist diese Formel einfacher.
Erläuterung
Im Kern verwendet diese Formel die Funktion WOCHENTAG, um den Wochentag (dh Montag, Dienstag usw.) für jeden Tag zwischen den beiden angegebenen Daten zu ermitteln. WOCHENTAG gibt eine Zahl zwischen 1 und 7 zurück. Bei den Standardeinstellungen ist Sonntag = 1 und Samstag = 7.
Der Trick bei dieser Formel besteht darin, ein Array von Daten zusammenzustellen, die Sie in die WEEKDAY-Funktion eingeben können. Dies geschieht mit ROW mit INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interpretiert die verketteten Daten als Zeilennummern und gibt ein Array wie folgt zurück:
(43346;43347;43348;43349;43350;43351;43352)
Jede Zahl im Array steht für ein Datum. Die WEEKDAY-Funktion wertet dann das Array aus und gibt ein Array von Wochentagswerten zurück:
(2;3;4;5;6;7;1)
Diese Zahlen entsprechen dem Wochentag jedes Datums. Sie werden der MID-Funktion als Startnummernargument zusammen mit dem Wert in D6 "0888884" für Text bereitgestellt:
MID("0888884",(2;3;4;5;6;7;1),1)
Da wir MID ein Array von Startnummern geben, wird ein Array von Ergebnissen wie folgt zurückgegeben:
("8";"8";"8";"8";"8";"4";"0")
Diese Werte entsprechen den Arbeitsstunden an jedem Tag vom Startdatum bis zum Enddatum. Beachten Sie, dass die Werte in diesem Array Text und keine Zahlen sind. Um in tatsächliche Zahlen umzuwandeln, multiplizieren wir mit einem zweiten Array, das zum Verwalten von Feiertagen erstellt wurde, wie unten erläutert. Die mathematische Operation zwingt den Text zu numerischen Werten.
Ferien
Um Feiertage zu behandeln, verwenden wir ISNA, MATCH und den genannten Bereich "Feiertage" wie folgt:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Dieser Ausdruck verwendet MATCH, um Datumsangaben zu finden, die im benannten Bereich Feiertage liegen, und zwar mit demselben Array von Datumsangaben, die oben mit INDIRECT und ROW generiert wurden. MATCH gibt eine Nummer zurück, wenn Feiertage gefunden werden, und den Fehler # N / A, wenn dies nicht der Fall ist. Die ISNA-Funktion "kippt" die Ergebnisse so, dass TRUE Feiertage und FALSE Nicht-Feiertage darstellt. ISNA gibt ein Array oder folgende Ergebnisse zurück:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Schließlich werden beide Arrays innerhalb von SUMPRODUCT miteinander multipliziert. Die mathematische Operation zwingt TRUE und FALSE zu 1 und Null und die Textwerte im ersten Array zu numerischen Werten (wie oben erläutert). Am Ende haben wir also:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Nach der Multiplikation haben wir ein einzelnes Array in SUMPRODUCT, das alle Arbeitsstunden im Datumsbereich enthält:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT summiert dann alle Elemente im Array und gibt ein Ergebnis von 36 zurück.
