Obecný vzorec
=(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)
souhrn
Chcete-li vypočítat celkovou pracovní dobu mezi dvěma daty a časy, můžete použít vzorec založený na funkci NETWORKDAYS. V zobrazeném příkladu obsahuje E5 tento vzorec:
=(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)
kde „dolní“ je pojmenovaný rozsah H5 a „horní“ je pojmenovaný rozsah H6.
Poznámka: tento příklad byl inspirován vzorcovou výzvou na Chandoo a úplnějším řešením, které poskytl mistr vzorců Barry Houdini na fóru MrExcel.
Vysvětlení
Tento vzorec počítá celkovou pracovní dobu mezi dvěma daty a časy, které se vyskytují mezi „nižším“ a „horním“ časem. V zobrazeném příkladu je dolní čas 9:00 a horní čas 17:00. Ty se ve vzorci zobrazují jako pojmenované rozsahy „dolní“ a „horní“.
Logikou vzorce je výpočet všech možných pracovních hodin mezi počátečním a koncovým datem, včetně, a potom zpět všechny hodiny v počáteční datum, které se vyskytnou mezi počátečním a nižším časem, a jakékoli hodiny v koncový den, které nastanou mezi čas ukončení a horní čas.
Funkce NETWORKDAYS zpracovává vyloučení víkendů a svátků (pokud jsou poskytovány jako rozsah dat). Pokud má váš plán nestandardní pracovní dny, můžete přepnout na NETWORKDAYS.INTL.
Výstup formátování
Výsledkem je číslo, které představuje celkový počet hodin. Stejně jako všechny časy aplikace Excel budete muset výstup naformátovat vhodným formátem čísla. V ukázaném příkladu používáme:
(h):mm
Hranaté závorky zabraňují převrácení aplikace Excel, když jsou hodiny větší než 24. Jinými slovy, umožňují zobrazit hodiny větší než 24. Pokud potřebujete desítkovou hodnotu pro hodiny, můžete výsledek vynásobit 24 a formátovat jako běžné číslo.
Jednoduchá verze
Pokud počáteční a koncový čas nastane vždy mezi spodním a horním časem, můžete použít jednodušší verzi tohoto vzorce:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Žádný čas začátku a konce
Chcete-li vypočítat celkovou pracovní dobu mezi dvěma daty, za předpokladu, že všechny dny jsou plné pracovní dny, můžete použít ještě jednodušší vzorec:
=NETWORKDAYS(start,end,holidays)*hours
Podrobnosti viz vysvětlení zde.
