Generische Formel
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Zusammenfassung
Um die Gesamtarbeitszeit zwischen zwei Daten und Zeiten zu berechnen, können Sie eine Formel verwenden, die auf der Funktion NETWORKDAYS basiert. Im gezeigten Beispiel enthält E5 die folgende Formel:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
wobei "unter" der benannte Bereich H5 und "ober" der benannte Bereich H6 ist.
Hinweis: Dieses Beispiel wurde von einer Formelherausforderung für Chandoo und einer umfassenderen Lösung inspiriert, die von Formelmeister Barry Houdini im MrExcel-Forum bereitgestellt wurde.
Erläuterung
Diese Formel berechnet die Gesamtarbeitszeit zwischen zwei Daten und Zeiten, die zwischen einer "unteren" und einer "oberen" Zeit liegen. In dem gezeigten Beispiel ist die untere Zeit 9:00 Uhr und die obere Zeit 17:00 Uhr. Diese erscheinen in der Formel als die benannten Bereiche "unter" und "oben".
Die Logik der Formel besteht darin, alle möglichen Arbeitsstunden zwischen dem Start- und dem Enddatum einschließlich zu berechnen und dann alle Stunden am Startdatum, die zwischen der Startzeit und der unteren Zeit liegen, und alle Stunden am Enddatum, die zwischen dem Datum liegen, zurückzusetzen die Endzeit und die obere Zeit.
Die Funktion NETWORKDAYS behandelt den Ausschluss von Wochenenden und Feiertagen (sofern als Datumsbereich angegeben). Sie können zu NETWORKDAYS.INTL wechseln, wenn Ihr Zeitplan nicht standardmäßige Arbeitstage enthält.
Ausgabe formatieren
Das Ergebnis ist eine Zahl, die die Gesamtstunden darstellt. Wie in allen Excel-Zeiten müssen Sie die Ausgabe mit einem geeigneten Zahlenformat formatieren. In dem gezeigten Beispiel verwenden wir:
(h):mm
Die eckigen Klammern verhindern, dass Excel verschoben wird, wenn die Stunden größer als 24 sind. Mit anderen Worten, sie ermöglichen die Anzeige von Stunden größer als 24. Wenn Sie einen Dezimalwert für Stunden benötigen, können Sie das Ergebnis mit 24 multiplizieren und als formatieren reguläre Nummer.
Einfache Version
Wenn Start- und Endzeiten immer zwischen unteren und oberen Zeiten liegen, können Sie eine einfachere Version dieser Formel verwenden:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Keine Start- und Endzeit
Um die Gesamtarbeitszeit zwischen zwei Daten zu berechnen, können Sie eine noch einfachere Formel verwenden, vorausgesetzt, alle Tage sind volle Arbeitstage.
=NETWORKDAYS(start,end,holidays)*hours
Einzelheiten finden Sie hier.
