Formuła ogólna
=(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)
Podsumowanie
Aby obliczyć łączną liczbę godzin pracy między dwiema datami i godzinami, można użyć formuły opartej na funkcji DNI.ROBOCZE. W pokazanym przykładzie E5 zawiera następującą formułę:
=(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)
gdzie „dolny” to nazwany zakres H5, a „górny” to nazwany zakres H6.
Uwaga: ten przykład został zainspirowany wyzwaniem z formułą na Chandoo i bardziej kompletnym rozwiązaniem dostarczonym przez mistrza formuł Barry'ego Houdiniego na forum MrExcel.
Wyjaśnienie
Ta formuła oblicza całkowitą liczbę godzin pracy między dwiema datami i godzinami, które występują między „dolnym” a „górnym” czasem. W przedstawionym przykładzie dolna godzina to 9:00, a górna godzina 17:00. Są one wyświetlane w formule jako nazwane zakresy „dolny” i „górny”.
Logika wzoru polega na obliczeniu wszystkich możliwych godzin pracy między datą początkową i końcową włącznie, a następnie wycofaniu wszystkich godzin w dniu rozpoczęcia, które występują między godziną rozpoczęcia a niższą godziną, oraz wszelkich godzin w dniu zakończenia, które przypadają między czas zakończenia i czas górny.
Funkcja DNI.ROBOCZE obsługuje wykluczanie weekendów i świąt (podanych jako zakres dat). Możesz przełączyć się na NETWORKDAYS.INTL, jeśli Twój harmonogram obejmuje niestandardowe dni robocze.
Formatowanie danych wyjściowych
Wynikiem jest liczba reprezentująca całkowitą liczbę godzin. Podobnie jak we wszystkich programach Excel, będziesz musiał sformatować dane wyjściowe za pomocą odpowiedniego formatu liczb. W przedstawionym przykładzie używamy:
(h):mm
Nawiasy kwadratowe uniemożliwiają przewijanie programu Excel, gdy liczba godzin jest większa niż 24. Innymi słowy, umożliwiają wyświetlanie godzin dłuższych niż 24. Jeśli potrzebujesz wartości dziesiętnej dla godzin, możesz pomnożyć wynik przez 24 i sformatować jako zwykły numer.
Prosta wersja
Jeśli czas rozpoczęcia i zakończenia zawsze będzie występował między godzinami dolnymi i górnymi, możesz użyć prostszej wersji tej formuły:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Brak czasu rozpoczęcia i zakończenia
Aby obliczyć łączną liczbę godzin pracy między dwiema datami, zakładając, że wszystkie dni są pełnymi dniami roboczymi, możesz użyć jeszcze prostszej formuły:
=NETWORKDAYS(start,end,holidays)*hours
Zobacz wyjaśnienie tutaj, aby uzyskać szczegółowe informacje.
