Všeobecný 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)
Zhrnutie
Na výpočet celkového počtu pracovných hodín medzi dvoma dátumami a časmi môžete použiť vzorec založený na funkcii NETWORKDAYS. V zobrazenom príklade 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 „spodný“ je pomenovaný rozsah H5 a „horný“ je pomenovaný rozsah H6.
Poznámka: tento príklad bol inšpirovaný výzvou formuly na Chandoo a úplnejším riešením, ktoré poskytol majster receptov Barry Houdini na fóre MrExcel.
Vysvetlenie
Tento vzorec počíta celkový pracovný čas medzi dvoma dátumami a časmi, ktoré sa vyskytujú medzi „nižším“ a „horným“ časom. V zobrazenom príklade je dolný čas 9:00 a horný čas 17:00. Vo vzorci sa zobrazujú ako pomenované rozsahy „spodný“ a „horný“.
Logika vzorca je vypočítať všetky možné pracovné hodiny medzi začiatočným a konečným dátumom vrátane a potom vyradiť všetky hodiny v deň začatia, ktoré sa vyskytnú medzi časom začatia a nižším časom, a všetky hodiny v deň ukončenia, ktoré nastanú medzi konečný čas a horný čas.
Funkcia NETWORKDAYS spracováva vylúčenie víkendov a sviatkov (ak sú poskytované ako rozsah dátumov). Ak má váš plán neštandardné pracovné dni, môžete prepnúť na NETWORKDAYS.INTL.
Výstup formátovania
Výsledkom je číslo, ktoré predstavuje celkový počet hodín. Rovnako ako všetky časy programu Excel, budete musieť výstup naformátovať vhodným číselným formátom. V zobrazenom príklade používame:
(h):mm
Hranaté zátvorky zabraňujú programu Excel prevrátiť sa, keď sú hodiny väčšie ako 24. Inými slovami, umožňujú zobraziť hodiny väčšie ako 24. Ak potrebujete desatinnú hodnotu pre hodiny, môžete výsledok vynásobiť číslom 24 a formátovať ako regulárne číslo.
Jednoduchá verzia
Ak sa počiatočný a konečný čas budú vždy vyskytovať medzi dolným a horným časom, môžete použiť jednoduchšiu verziu tohto vzorca:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Žiadny čas začiatku a konca
Na výpočet celkového počtu pracovných hodín medzi dvoma dátumami, za predpokladu, že všetky dni sú plné pracovné dni, môžete použiť ešte jednoduchší vzorec:
=NETWORKDAYS(start,end,holidays)*hours
Podrobnosti nájdete tu vo vysvetlení.
