Splošna formula
=(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)
Povzetek
Za izračun skupnega števila delovnih ur med dvema datumoma in časoma lahko uporabite formulo, ki temelji na funkciji NETWORKDAYS. V prikazanem primeru E5 vsebuje to formulo:
=(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)
kjer je "spodnji" imenovani obseg H5, "zgornji" pa imenovani obseg H6.
Opomba: ta primer je navdihnil izziv formule na Chandooju in popolnejšo rešitev, ki jo je na forumu MrExcel zagotovil mojster formule Barry Houdini.
Pojasnilo
Ta formula izračuna skupni delovni čas med dvema datumoma in časoma, ki nastopijo med "spodnjim" in "zgornjim" časom. V prikazanem primeru je spodnji čas 9:00, zgornji pa 17:00. Ti so v formuli prikazani kot imenovani obsegi "spodnji" in "zgornji".
Logika formule je izračunati vse možne delovne ure med začetnim in končnim datumom, vključno s tem, nato umakniti vse ure na začetni datum, ki se pojavijo med začetnim časom in nižjim časom, ter vse ure na končnem datumu, ki se pojavijo med končni in zgornji čas.
Funkcija NETWORKDAYS obravnava izključitev vikendov in praznikov (če je na voljo kot obseg datumov). Če imate v urniku nestandardne delovne dni, lahko preklopite na NETWORKDAYS.INTL.
Oblikovanje izhoda
Rezultat je število, ki predstavlja skupno število ur. Kot vsi Excel-ovi časi boste morali tudi izhod oblikovati s primerno številčno obliko. V prikazanem primeru uporabljamo:
(h):mm
V oglatih oklepajih se Excel ne prevrne, ko so ure večje od 24. Z drugimi besedami, omogočajo prikaz ur, večjih od 24. Če potrebujete decimalno vrednost za ure, lahko rezultat pomnožite s 24 in oblikujete kot redna številka.
Preprosta različica
Če se začetni in končni čas vedno pojavita med spodnjim in zgornjim časom, lahko uporabite preprostejšo različico te formule:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Brez začetnega in končnega časa
Za izračun skupnega števila delovnih ur med dvema datumoma, ob predpostavki, da so vsi dnevi polni delavniki, lahko uporabite še preprostejšo formulo:
=NETWORKDAYS(start,end,holidays)*hours
Za podrobnosti glejte razlago tukaj.
